SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 derivated table
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

casati74
Posting Yak Master

Italy
109 Posts

Posted - 09/04/2006 :  03:59:44  Show Profile
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)

Singapore
17430 Posts

Posted - 09/04/2006 :  04:04:43  Show Profile
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) 



KH

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17430 Posts

Posted - 09/04/2006 :  04:06:52  Show Profile
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]

KH


Edited by - khtan on 09/04/2006 04:21:57
Go to Top of Page

casati74
Posting Yak Master

Italy
109 Posts

Posted - 09/04/2006 :  04:14:10  Show Profile
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?????
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/04/2006 :  04:16:04  Show Profile  Visit SwePeso's Homepage
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17430 Posts

Posted - 09/04/2006 :  04:19:30  Show Profile
Should have also validate the query


KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/04/2006 :  04:21:51  Show Profile  Visit SwePeso's Homepage
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

Edited by - SwePeso on 09/04/2006 04:26:01
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/04/2006 :  04:24:20  Show Profile  Visit SwePeso's Homepage
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

Edited by - SwePeso on 09/04/2006 04:25:20
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17430 Posts

Posted - 09/04/2006 :  04:26:47  Show Profile
And the derived table is actually redundant.

Or maybe this is just part of a larger query


KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/04/2006 :  04:46:00  Show Profile  Visit SwePeso's Homepage
Exactly. See
select d.IdTag, d.Colata 
from (some joins..) d
where 	d.IdTag = @HeatIDField and d.Colata = @HeatNumber)


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000