| Author |
 Topic  |
|
|
casati74
Posting Yak Master
Italy
109 Posts |
 Posted - 09/04/2006 : 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)
Singapore
16746 Posts |
Posted - 09/04/2006 : 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)
KH
|
 |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 09/04/2006 : 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]
KH
|
Edited by - khtan on 09/04/2006 04:21:57 |
|
|
|
casati74
Posting Yak Master
Italy
109 Posts |
Posted - 09/04/2006 : 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
Sweden
29138 Posts |
Posted - 09/04/2006 : 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)
Singapore
16746 Posts |
Posted - 09/04/2006 : 04:19:30
|
 Should have also validate the query
KH
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/04/2006 : 04:21:51
|
This is another approach that will give the the same answer without derived tableSELECT 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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/04/2006 : 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 |
Edited by - SwePeso on 09/04/2006 04:25:20 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 09/04/2006 : 04:26:47
|
And the derived table is actually redundant.
Or maybe this is just part of a larger query
KH
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 09/04/2006 : 04:46:00
|
Exactly. Seeselect d.IdTag, d.Colata
from (some joins..) d
where d.IdTag = @HeatIDField and d.Colata = @HeatNumber)
Peter Larsson
Helsingborg, Sweden |
|
|
| |
Topic |
|
derivated table
Forum Locked
