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
 derived table
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

casati74
Posting Yak Master

Italy
109 Posts

Posted - 09/07/2006 :  05:12:37  Show Profile
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

Sweden
30281 Posts

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

Edited by - SwePeso on 09/07/2006 05:30:43
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 09/07/2006 :  05:21:43  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 09/07/2006 :  05:22:31  Show Profile  Visit SwePeso's Homepage
Casati and Chirag, look at the WHERE clause. Notice something strange?

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


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 09/07/2006 05:23:32
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 09/07/2006 :  05:25:30  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message
Aww dam copy paste..

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

I am Confused..

Chirag
Go to Top of Page

casati74
Posting Yak Master

Italy
109 Posts

Posted - 09/07/2006 :  05:34:11  Show Profile
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???
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 09/07/2006 :  05:38:21  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message

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)


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

Edited by - chiragkhabaria on 09/07/2006 05:39:56
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 09/07/2006 :  05:38:38  Show Profile  Visit SwePeso's Homepage
You are STILL only selecting one column from the derived table, and that column is TagTable.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 09/07/2006 :  05:42:29  Show Profile  Visit SwePeso's Homepage
And that's even weirder. IdTag is supposed to be BOTH 1 and @HeatIDField ?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

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

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 09/07/2006 :  05:47:01  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message
Peter, I guess he is trying to learn the concepts of Derived Table, since no way this looks like a professional Assignment..

Chirag
Go to Top of Page

casati74
Posting Yak Master

Italy
109 Posts

Posted - 09/07/2006 :  05:49:16  Show Profile
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 09/07/2006 :  06:10:10  Show Profile  Visit SwePeso's Homepage
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

Edited by - SwePeso on 09/07/2006 06:24:42
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.09 seconds. Powered By: Snitz Forums 2000