| Author |
 Topic  |
|
|
Niki
Starting Member
46 Posts |
 Posted - 11/16/2012 : 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 S
inner 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_TYPE
FROM 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 |
Edited by - Niki on 11/16/2012 15:46:14
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3856 Posts |
Posted - 11/16/2012 : 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 S
inner 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 |
Edited by - Lamprey on 11/16/2012 14:45:36 |
 |
|
|
Niki
Starting Member
46 Posts |
Posted - 11/16/2012 : 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
Flowing Fount of Yak Knowledge
3856 Posts |
Posted - 11/16/2012 : 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.aspx
Here 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 rows
SELECT *
FROM @Student
WHERE 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_Type
FROM
@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_Type
FROM
@OtherTable AS O |
|
|
|
Niki
Starting Member
46 Posts |
Posted - 11/16/2012 : 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_Type
FROM
@Student AS s
Niki |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 11/16/2012 : 22:23:03
|
the latest posted code looks fine, are you still having issues in getting correct values?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Niki
Starting Member
46 Posts |
Posted - 11/19/2012 : 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
India
48012 Posts |
Posted - 11/20/2012 : 01:45:39
|
Cool...let us know if you face any more issues
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
EXISTS clause help
