derived table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

derived table

Author

Topic

casati74
Posting Yak Master

109 Posts

Posted - 2006-09-07 : 05:12:37

Hello i have a problem with this derived table

SELECT distinct @IdTag , @HeatNumber

from
(
SELECT ScDon10.dbo.RepTagConfig.TagTable
FROM ScDon10.dbo.RepTableConfig INNER JOIN ScDon10.dbo.RepTagConfig ON ScDon10.dbo.RepTableConfig.IdRepTable = ScDon10.dbo.RepTagConfig.IdRepTable
WHERE (ScDon10.dbo.RepTableConfig.TableName = @InvolvedTable)) d
where (@IdTag = @IdTag) and (@HeatNumber = @HeatNumber)

I must use this to check if exist or not but when i run this select i recive alway a true response.

I must check if a copy of IdTag and HeatNumber are present on Table returned on select of from cleusule.

How can i solve this problem????

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-07 : 05:20:58

quote:
Originally posted by casati74

Hello i have a problem with this derived table

SELECT distinct @IdTag , @HeatNumber

from
(
SELECT ScDon10.dbo.RepTagConfig.TagTable
FROM ScDon10.dbo.RepTableConfig INNER JOIN ScDon10.dbo.RepTagConfig ON ScDon10.dbo.RepTableConfig.IdRepTable = ScDon10.dbo.RepTagConfig.IdRepTable
WHERE (ScDon10.dbo.RepTableConfig.TableName = @InvolvedTable)) d
where (@IdTag = @IdTag) and (@HeatNumber = @HeatNumber)

You think there is a problem with the query? Me too!

This is how your query look like. Please explain to me what you are trying to acheive with that.

SELECT distinct	@IdTag,
		@HeatNumber
from		(
			SELECT		ScDon10.dbo.RepTagConfig.TagTable
			FROM		ScDon10.dbo.RepTableConfig
			INNER JOIN	ScDon10.dbo.RepTagConfig ON ScDon10.dbo.RepTableConfig.IdRepTable = ScDon10.dbo.RepTagConfig.IdRepTable
			WHERE		ScDon10.dbo.RepTableConfig.TableName = @InvolvedTable
		) d
where		@IdTag = @IdTag
		and @HeatNumber = @HeatNumber

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-07 : 05:21:43

Somthing like this?


If Exists ( SELECT ScDon10.dbo.RepTagConfig.TagTable
FROM ScDon10.dbo.RepTableConfig INNER JOIN ScDon10.dbo.RepTagConfig ON ScDon10.dbo.RepTableConfig.IdRepTable = ScDon10.dbo.RepTagConfig.IdRepTable
WHERE (ScDon10.dbo.RepTableConfig.TableName = @InvolvedTable)) d
where (@IdTag = @IdTag) and (@HeatNumber = @HeatNumber) )  
---do som processing

Else 
-- Do Somt processing

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-07 : 05:22:31

Casati and Chirag, look at the WHERE clause. Notice something strange?

...
where @IdTag = @IdTag
and @HeatNumber = @HeatNumber

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-07 : 05:25:30

Aww dam copy paste..

What does he wants then?? since this will always return true.. ???

I am Confused..

Chirag

casati74
Posting Yak Master

109 Posts

Posted - 2006-09-07 : 05:34:11

for my this is correct

select d.IdTag, d.Colata
from
(
SELECT distinct RepTagConfig.TagTable
FROM RepTableConfig INNER JOIN RepTagConfig
ON RepTableConfig.IdRepTable = RepTagConfig.IdRepTable
WHERE (RepTableConfig.TableName = N'Heats' and IdTag = '1')
) d
where (d.IdTag = @HeatIDField)
and (d.Colata = @HeatNumber)

but when i run it i recive the invalid column name for IdTag and colata why???

If i run separatly the from select and i insert manually the result on first select it work correctly why???

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-07 : 05:38:21

[code]
select d.IdTag, d.Colata
from
(
SELECT distinct RepTagConfig.TagTable,[IdTag],[Colata]
FROM RepTableConfig INNER JOIN RepTagConfig
ON RepTableConfig.IdRepTable = RepTagConfig.IdRepTable
WHERE (RepTableConfig.TableName = N'Heats' and IdTag = '1')
) d
where (d.IdTag = @HeatIDField)
and (d.Colata = @HeatNumber)
[/code]

You need to specify the column name in the derived table then only you can use them in the main query...

but why are you using derived table this can be achived without using derived table.. .???

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-07 : 05:38:38

You are STILL only selecting one column from the derived table, and that column is TagTable.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-07 : 05:42:29

And that's even weirder. IdTag is supposed to be BOTH 1 and @HeatIDField ?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-07 : 05:44:54

I will give you a free tip. Start smaller. Just make the select query work, THEN you can start checking for existance and so on...

SELECT		*
FROM		RepTableConfig a
INNER JOIN	RepTagConfig b ON b.IdRepTable = a.IdRepTable
WHERE		a.TableName = N'Heats'
		and ?.IdTag = '1'
		and ?.Colata = @HeatNumber
		and ?.IdTag = @HeatIDField

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-07 : 05:47:01

Peter, I guess he is trying to learn the concepts of Derived Table, since no way this looks like a professional Assignment..

Chirag

casati74
Posting Yak Master

109 Posts

Posted - 2006-09-07 : 05:49:16

i solve thank's

i wrote

if not exists (select @IdTag as Idtag , @colata as colata
from
(
SELECT distinct RepTagConfig.TagTable
FROM RepTableConfig INNER JOIN RepTagConfig
ON RepTableConfig.IdRepTable = RepTagConfig.IdRepTable
WHERE (RepTableConfig.TableName = N'Heats' and IdTag = '1')
) d

where @IdTag = @HeatIDField and @Colata = @HeatNumber
)
begin
print 'non esiste'
end
else
print 'esiste'

and work correctly

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-07 : 06:10:10

quote:
Originally posted by chiragkhabaria

Peter, I guess he is trying to learn the concepts of Derived Table, since no way this looks like a professional Assignment..

But there is no progress? Last post is as bad as the others. I can't see how that even remotely could work.

The query

if not exists (select @IdTag as Idtag , @colata as colata
from 
(
SELECT distinct RepTagConfig.TagTable
FROM RepTableConfig INNER JOIN RepTagConfig 
ON RepTableConfig.IdRepTable = RepTagConfig.IdRepTable
WHERE (RepTableConfig.TableName = N'Heats' and IdTag = '1')
) d

where @IdTag = @HeatIDField and @Colata = @HeatNumber
)
begin
print 'non esiste' 
end
else 
print 'esiste'

select a variable and check if that exist?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources