[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Help with WHERE clause

Posted on 2011-10-12
6
Medium Priority
?
232 Views
Last Modified: 2012-05-12
How can I test each member of an IN statement in a where clause?

For example:
SELECT REQ_CDE FROM REQUIREMENTS WHERE REQ_CDE IN ('APPL','GOALS','CTRN1','CTRN2','CTRN3') AND COMPLETION_STS = 'C'

Each req_cde must return a completion_sts = 'C", I believe the way this is coded if any of them are 'C" it will return true.

Thanks!
0
Comment
Question by:jasonbrandt3
  • 4
  • 2
6 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 36958305
try


SELECT a.REQ_CDE 
FROM REQUIREMENTS a
inner join 
(
select 'APPL' as req
union
select'GOALS'
union
select'CTRN1'
union
select 'CTRN2'
union
select 'CTRN3') b on a.req_cde = b.req
where a.COMPLETION_STS = 'C'

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 36958321
although your existing query will return all req_cde that has completion_sts = 'c'

so I'm not sure I'm understanding your question. Can you please post sample data and expected resutls?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 36958337
or maybe you're looking for this

SELECT a.REQ_CDE 
FROM REQUIREMENTS a
WHERE a.req_cde = ALL (
select 'APPL' as req
union
select'GOALS'
union
select'CTRN1'
union
select 'CTRN2'
union
select 'CTRN3') and a.COMPLETION_STS = 'C'

Open in new window

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:jasonbrandt3
ID: 36958370
Let me show you the whole statement I'm having an issue with and the result which is not correct..

CASE WHEN cdy.stage in ('UACEP', 'UDEPS','UREG', 'TACEP', 'TREG', 'TDEPS') THEN 'YES'
      WHEN cdy.stage in ('TAPP','TAPPN') AND EXISTS (SELECT REQ_CDE FROM REQUIREMENTS WHERE (REQ_CDE IN ('APPL','GOALS','CTRN1','CTRN2','CTRN3') AND
      COMPLETION_STS = 'C')) THEN 'YES'
ELSE 'NO' END

So in my CASE statement when they are in these stages ('UACEP', 'UDEPS','UREG', 'TACEP', 'TREG', 'TDEPS') return a 'Yes'.

When the stage is in ('TAPP','TAPPN')  and a row in the requirements table exists containing these requirements ('APPL','GOALS','CTRN1','CTRN2','CTRN3') and all of these existing requirements are set to 'C' then return a 'YES'

I have a record where the following exist PHST,GOALS,APPL,CTRN1, but only 3/4 have 'C', it should return 'NO'.  Hope that makes sense!  Thanks for help!
0
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 36958412
can you post some sample data ??

Also in your requirements table is there another column that groups those req_cde together so that we can say yes, they all exists and they all have completion_sts=C?
0
 

Author Closing Comment

by:jasonbrandt3
ID: 36958452
I just figured it out, there is actually another column that indicates if everthing is complete, I just missed it.  Thanks so much for the assistance.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Screencast - Getting to Know the Pipeline

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question