February 18, 2013 at 5:13 pm
Hi,
I'm looking for a little help with this query. I'm trying to write a query that returns true if a visit exists in a Fact table and not in an error table and has a message NOT LIKE 'w%' otherwise it returns false
I have this query which returns all the visits that would be false visits but I need to output to be false not the visit number.
SELECT E.VisitID
FROM Fact F LEFT OUTER JOIN Error E
ON F.VisitID = E.VisitID
WHERE E.VisitID IS NOT NULL AND E.Message NOT LIKE 'w%'
I was thinking I could do a CASE Like this
SELECT CASE
WHEN F.VisitID = (SELECT F.VisitID
FROM Fact F LEFT OUTER JOIN Error E
ON F.VisitID = E.VisitID
WHERE E.VisitID IS NOT NULL AND EEPE.MessageNumber NOT LIKE 'w%')THEN 'False'
ELSE 'True'
END
FROM Fact F JOIN Error E
ON F.VisitID = E.VisitID
But that give me the problem of SubQuery returns more than 1 value.
Can anyone please point me a better direction or give me some help with my query?
Thanks!
February 18, 2013 at 6:15 pm
You could use and EXISTS in you case statement rather than an =
Not sure if I've got you logic right, but something like the following
-- Some Test Data
;with Fact as (
SELECT *
FROM (VALUES (1), (2), (3), (4), (5)) AS Fact(VisitID)
)
, Error as (
SELECT *
FROM (VALUES (1,'a'), (3,'w also has another'), (3,'a'), (4, 'w just this one'), (5,'a')) AS Error(VisitID, [Message])
)
-- The Query
SELECT f.VisitID, CASE
WHEN EXISTS(SELECT 1 FROM Error e WHERE e.visitID = f.visitID and [Message] not like 'w%') THEN 'false'
ELSE 'true'
END
FROM Fact f
February 19, 2013 at 4:07 pm
Thanks That fixed it
February 19, 2013 at 6:16 pm
No Problem
February 20, 2013 at 2:58 am
good one micky....
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply