Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SubQuery returns more than 1 value Expand / Collapse
Author
Message
Posted Monday, February 18, 2013 5:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 12:24 PM
Points: 9, Visits: 83
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!
Post #1421384
Posted Monday, February 18, 2013 6:15 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:24 PM
Points: 953, Visits: 2,898
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

Post #1421395
Posted Tuesday, February 19, 2013 4:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 12:24 PM
Points: 9, Visits: 83
Thanks That fixed it
Post #1421882
Posted Tuesday, February 19, 2013 6:16 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:24 PM
Points: 953, Visits: 2,898
No Problem
Post #1421906
Posted Wednesday, February 20, 2013 2:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
good one micky....


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1421998
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse