derivated 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

derivated table

Author

Topic

casati74
Posting Yak Master

109 Posts

Posted - 2006-09-04 : 03:59:44

hello,
i wrote this select but don't run why?????

declare @HeatIDField int, @HeatNumber int, @InvolvedTable nvarchar(50)
set @HeatIDField = '1'
set @HeatNumber = '761386'
set @InvolvedTable= 'Heats'
select IdTag, Colata from (SELECT RepTagConfig.TagTable
FROM RepTableConfig INNER JOIN
RepTagConfig ON RepTableConfig.IdRepTable = RepTagConfig.IdRepTable
WHERE (RepTableConfig.TableName = N'Heats'))
where (IdTag = @HeatIDField) and (Colata = @HeatNumber)

if I start select one at time it run correctly!!!

I need help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-04 : 04:04:43

you need to named the derived table


declare @HeatIDField int, @HeatNumber int, @InvolvedTable nvarchar(50)
set @HeatIDField = '1'
set @HeatNumber = '761386'
set @InvolvedTable= 'Heats'
select IdTag, Colata from (SELECT RepTagConfig.TagTable
FROM RepTableConfig INNER JOIN
RepTagConfig ON RepTableConfig.IdRepTable = RepTagConfig.IdRepTable
WHERE (RepTableConfig.TableName = N'Heats')) d
where (IdTag = @HeatIDField) and (Colata = @HeatNumber)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-04 : 04:06:52

Or in nicely formatted manner


declare @HeatIDField 	int, 
	@HeatNumber 	int, 
	@InvolvedTable 	nvarchar(50)

set 	@HeatIDField 	= '1'
set 	@HeatNumber 	= '761386'
set 	@InvolvedTable	= 'Heats'

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

[EDIT]

quote:
But I have a IdTag and Colata column!!!

[/EDIT]

casati74
Posting Yak Master

109 Posts

Posted - 2006-09-04 : 04:14:10

I tried this solution but I recive an invalid column name error message!!!!

But I have a IdTag and Colata column!!!

I tried d.IdTag and d.Colata but i recive the same errore message; why?????

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 04:16:04

Oh! Where is the column Colata in the derived table? The examples above will produce an error.
Never mind, since Colata should be equal to @HeatNumber anyway, just substitute

SELECT IdTag, Colata

with

SELECT IdTag, @HeatNumber

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-04 : 04:19:30

Should have also validate the query

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 04:21:51

This is another approach that will give the the same answer without derived table

SELECT		IdTag,
		Colata
FROM		RepTableConfig
INNER JOIN	RepTagConfig ON RepTableConfig.IdRepTable = RepTagConfig.IdRepTable
WHERE		RepTableConfig.TableName = @InvolvedTable
		AND IdTag = @HeatIDField
		AND Colata = @HeatNumber

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 04:24:20

I still don't get the meaning of the query?
Why select columns that has the exact values of the variables?

This will produce no, one or many rows with the same values...

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-04 : 04:26:47

And the derived table is actually redundant.

Or maybe this is just part of a larger query

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 04:46:00

Exactly. See

select d.IdTag, d.Colata 
from (some joins..) d
where 	d.IdTag = @HeatIDField and d.Colata = @HeatNumber)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources