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.
Author |
Topic |
Niki
Yak Posting Veteran
51 Posts |
Posted - 2012-11-16 : 14:33:25
|
I am using following sub-quary in my Case statement. The sub quary does not get any results, but in case statement it still assigns the value as if 'EXISTS' is true.select d.studentno from dbo.tblS Sinner join tblD D on S.StudentNo = D.studentno where (D.exitdate is null or (D.entrydate <= '20120820' and D.exitdate >= '20120820')) and S.StudentNo = '402886';Using this in following select statment - SELECT ID, FN, LN (case when schno = '100' then '210' else when EXISTS (select d.studentno from dbo.tblS S inner join tblD D on S.StudentNo = D.studentno where (D.exitdate is null or (D.entrydate <= '20120820' and D.exitdate >= '20120820'))) then '250'end) D_TYPEFROM dbo.tblS WHERE StudentNo = '402886';I get the result for D_TYPE = 250, while I expect nothing there for this student which does not exist in tblD.Please help!Niki |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-11-16 : 14:43:21
|
Your "sub-query" is not corrilated. You will notice that your sub-query does not referece that table or tables that you are actually selecting from (the rest of the query that you didn't post). So, the sub-query is showing that a values exists for that time-frame:select d.studentno from dbo.tblS Sinner join tblD D on S.StudentNo = D.studentno where (D.exitdate is null or (D.entrydate <= '20120820' and D.exitdate >= '20120820'))AND S.StudentNo = ?? You need to corrilate the StudentNo to the other table/tables |
|
|
Niki
Yak Posting Veteran
51 Posts |
Posted - 2012-11-16 : 15:39:11
|
The main quary is for all students, and in above subquary I am just checking one student that I know should not have value 250. The main quary is selecting data elements (ID,FN,LN etc & many more ) from tblS & various other tables. It is huge quary so I did not put everything in there.Niki |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-11-16 : 16:22:43
|
I'm not sure what you are saying. Do you understand what the issue is? You've provided a partial query that has syntax and logic issues. I *think* I understand what you are trying, but you have provided no DDL, DML or expected output. Here are some links that can help you understand how to ask a database question better so we canhelp you more easily:http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxHere is some sample code, similar to what you are doing, that shows the difference having the EXISTS clause correlated to the dominate table:DECLARE @Student TABLE( StudentNo INT NOT NULL, ExitDate DATETIME)DECLARE @OtherTable TABLE( StudentNo INT, FN CHAR(1), LN CHAR(1), schno CHAR(3))INSERT @Student VALUES(1, '2011-01-01'),(2, '2012-01-01'),(3, '2013-01-01'),(4, '2014-01-01'),(5, '2015-01-01')INSERT @OtherTable VALUES(1, 'A', 'B', '100'),(2, 'A', 'B', '200'),(3, 'A', 'B', '200'),(4, 'A', 'B', '200'),(5, 'A', 'B', '100')-- Show that we get two rowsSELECT *FROM @StudentWHERE ExitDate BETWEEN '2012-01-01' AND '2013-01-01' -- UN-Correlated SELECT O.*, CASE WHEN O.schno = '100' THEN '210' WHEN EXISTS ( SELECT * FROM @Student WHERE ExitDate BETWEEN '2012-01-01' AND '2013-01-01' ) THEN '300' END AS D_TypeFROM @OtherTable AS O-- Correlated SELECT O.*, CASE WHEN O.schno = '100' THEN '210' WHEN EXISTS ( SELECT * FROM @Student AS S WHERE ExitDate BETWEEN '2012-01-01' AND '2013-01-01' AND S.StudentNo = O.StudentNo ) THEN '300' END AS D_TypeFROM @OtherTable AS O |
|
|
Niki
Yak Posting Veteran
51 Posts |
Posted - 2012-11-16 : 17:37:37
|
Thank you Lamprey, it makes sense. Sorry I yet have to learn how to ask my questions. Here is what I am doing, I beleive mine is correlated.DECLARE @Student TABLE( StudentNo INT NOT NULL, FN CHAR(10), FN CHAR(10), schno CHAR(3) )DECLARE @OtherTable TABLE( StudentNo INT, ExitDate DATETIME)INSERT @Student VALUES(1, 'John', 'Smith', '100'),(2, 'Chuck', 'Doe', '200'),INSERT @OtherTable VALUES(3, '2013-01-01'),(4, '2014-01-01'),(5, '2015-01-01')SELECT s.StudentNo,FN,LN CASE WHEN s.schno = '100' THEN '210' WHEN EXISTS ( SELECT o.StudentNo FROM @OtherTable AS o WHERE ExitDate BETWEEN '2012-01-01' AND '2013-01-01' AND s.StudentNo = o.StudentNo ) THEN '300' END AS D_TypeFROM @Student AS sNiki |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-16 : 22:23:03
|
the latest posted code looks fine, are you still having issues in getting correct values?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Niki
Yak Posting Veteran
51 Posts |
Posted - 2012-11-19 : 11:01:35
|
Yes, Visakh. Another DBA is looking at it; will let you know what we find out.Niki |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-20 : 01:45:39
|
Cool...let us know if you face any more issues------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|