troubleshooting Question

Oracle 10g - CASE against count

Avatar of JDCam
JDCam asked on
Oracle DatabaseSalesSQL* oracle syntax
11 Comments1 Solution128 ViewsLast Modified:
I need some help with a CASE statement.

I am looking at sales order lines and linking out to a reference table. I want the case to output 'MY STRING' If at least 1 line on the order has the value 'WILLIAMS'. Nothing output if no lines contain this value.

So far I wrote a query to return the reference, then added a count where the reference equals Williams.

Select count(ITEM) from (
    select D5.ORD_NUM, D5.ORD_LEV1 ITEM, D3.ALT_INVT_REP_CODE BRAND
    from E_ORD_D5 D5
    LEFT JOIN M_ITEM_D3 D3 on D3.COMP_CODE = D5.COMP_CODE and D3.CUST_CODE = D5.CUST_CODE and D3.ITEM_CODE = D5.ORD_LEV1
    where D5.comp_code = 'W1' and D5.ord_num = 935373 
    )
WHERE BRAND = 'WILLIAMS'

Not sure how to make this part of a CASE. (of if CASE is even the best approach)
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros