HOW to structure SQL CASE STATEMENT with multiple conditions

Question

I would like to convert the below logic into a SQL query, and i know that it can be acheived using a CASE WHEN THEN ELSE statement , but i am not sure how i can put it together in the query. Please help.

Where AG_AGENTS.SALES_AREA_DESC = Dom. - NAT
If PX_PAXWEB.COUNTRY = AUT then PX_PAXWEB.POSTCODE
If PX_PAXWEB.COUNTRY <> AUT then vwPxPaxweb.SALES_AREA
If point 2 and/or 3 is null then just use AG_AGENTS.SALES_AREA_DESC
Where AG_AGENTS.SALES_AREA_DESC = Int. – Inbound, then vwPxPaxweb.SALES_AREA

If point 5 is null then use AG_AGENTS.SALES_AREA_DESC

  The new field can be called as SALES_AREA_DESC_2

you have 2-4 after your where statement, but they would go into a select to be displayed right? — crthompson
– crthompson, Commented Jul 8, 2014 at 5:52

czeinerb · Accepted Answer · 2015-03-09 17:20:20Z

The general logic is:

select 
    CASE WHEN test1 = 'a' THEN 1
        WHEN test1 = 'b' THEN 2
        WHEN (test1 = 'c') AND (test2 is null) THEN 3
        ELSE 0 END as myfield
from mytable

In your case, if I understand your query correctly it is:

CASE WHEN (AG_AGENTS.SALES_AREA_DESC = Dom. - NAT) 
        AND (ISNULL(PX_PAXWEB.COUNTRY,'') = AUT) THEN PX_PAXWEB.POSTCODE
     WHEN (AG_AGENTS.SALES_AREA_DESC = Dom. - NAT) 
        AND (ISNULL(PX_PAXWEB.COUNTRY,'') <> AUT) THEN vwPxPaxweb.SALES_AREA
     WHEN (AG_AGENTS.SALES_AREA_DESC = Dom. - NAT) 
             AND (PX_PAXWEB.COUNTRY is null) THEN AG_AGENTS.SALES_AREA_DESC
     WHEN (AG_AGENTS.SALES_AREA_DESC = Int. – Inbound) 
        THEN vwPxPaxweb.SALES_AREA
     WHEN (AG_AGENTS.SALES_AREA_DESC = Int. – Inbound) 
        THEN AG_AGENTS.SALES_AREA_DESC
     WHEN (AG_AGENTS.SALES_AREA_DESC is null)
            OR (Int. is null) OR (Inbound is null) 
        THEN AG_AGENTS.SALES_AREA_DESC
     ELSE AG_AGENTS.SALES_AREA_DESC -- or some other exit value

END as SALES_AREA_DESC_2

Note: a few thing are not clear from your questions so I had to make assumptions: - Assumed "Dom.", "NAT", etc are fields (bit odd w the dot..) - I'm not sure which fields can be NULL (re: "If point 2 and/or 3 is null ", etc), so I picked all for the last branch of the case. So you can figure out the rest.

Collectives™ on Stack Overflow

HOW to structure SQL CASE STATEMENT with multiple conditions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related