CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user2380064
user2380064

Reputation: 27

SQL COALESCE with multiple CASE conditions not returning correct data with IS NULL

I have four tables AG_AGENTS, Px_PAXWEB, vw_POSTCODE, vwPXPaxWeb and the fields are AG_AGENTS.SALES_AREA_DESC, Px_Paxweb.COUNTRY, vwPxPaxweb.SALES_AREA, vw_POSTCODE.POSTCODE.

My query is returning all correct values except

  1. When AG_AGENTS ='Dom. -NAT', if Px_PaxWeb.COUNTRY <> 'AUT' then vwPxPaxWeb.SALES_AREA ( returning correct value till here) if vwPxPaxWeb.SALES_AREA IS NULL Then AG_AGENTS.SALES_AREA_DESC
  2. When AG_AGENTS ='Dom. -NAT', if Px_PaxWeb.COUNTRY ='AUT' Then vw_POSTCODE.POSTCODE( returning correct value till here) if vw_POSTCODE.POSTCODE IS NULL Then AG_AGENTS.SALES_AREA_DESC

Basically not returning AG_AGENTS.SALES_AREA_DESC when vw_POSTCODE.POSTCODE IS NULL and not returning AG_AGENTS.SALES_AREA_DESC when vwPxPaxWeb.SALES_AREA IS NULL

My SQL query is as below

CASE WHEN AG_AGENTS.SALES_AREA_DESC = 'Dom. - NAT' 
THEN (
    CASE WHEN PX_PAXWEB.COUNTRY = 'AUT' THEN vw_POSTCODE.POSTCODE
         WHEN PX_PAXWEB.COUNTRY <> 'AUT' THEN vwPxPaxWeb.SALES_AREA
         ELSE COALESCE (vw_POSTCODE.POSTCODE,vwPxPaxWeb.SALES_AREA,AG_AGENTS.SALES_AREA_DESC) END
)
ELSE 
(
    CASE WHEN AG_AGENTS.SALES_AREA_DESC = 'Int. – Inbound' Then vwPxPaxweb.SALES_AREA
         ELSE COALESCE( vwPxPaxweb.SALES_AREA,AG_AGENTS.SALES_AREA_DESC) END
)
END AS SALES_AREA_DESC_2

Upvotes: 0

Views: 8668

Answers (2)

Jesuraja
Jesuraja

Reputation: 3844

Try this:

CASE AG_AGENTS.SALES_AREA_DESC
    WHEN 'Dom. - NAT' THEN
    (
        COALESCE(CASE 
                    WHEN PX_PAXWEB.COUNTRY = 'AUT' THEN vw_POSTCODE.POSTCODE
                    WHEN PX_PAXWEB.COUNTRY <> 'AUT' THEN vwPxPaxWeb.SALES_AREA END,
                 AG_AGENTS.SALES_AREA_DESC)
    )
    WHEN 'Int. – Inbound' THEN vwPxPaxweb.SALES_AREA
    ELSE COALESCE(vwPxPaxweb.SALES_AREA,AG_AGENTS.SALES_AREA_DESC)
END AS SALES_AREA_DESC_2

Upvotes: 0

Matt
Matt

Reputation: 1155

I tried to follow your description. Both seem to be solved with an ISNULL (shown below). But you say this doesn't work?

CASE WHEN AG_AGENTS.SALES_AREA_DESC = 'Dom. - NAT' 
     THEN CASE
          WHEN PX_PAXWEB.COUNTRY = 'AUT' 
          THEN ISNULL(vw_POSTCODE.POSTCODE, AG_AGENTS.SALES_AREA_DESC) --#2 fix
          WHEN PX_PAXWEB.COUNTRY <> 'AUT' 
          THEN ISNULL(vwPxPaxWeb.SALES_AREA,AG_AGENTS.SALES_AREA_DESC) --#1 fix
          ELSE COALESCE (vw_POSTCODE.POSTCODE,vwPxPaxWeb.SALES_AREA,AG_AGENTS.SALES_AREA_DESC)
          END
     ELSE CASE
          WHEN AG_AGENTS.SALES_AREA_DESC = 'Int. – Inbound'
          THEN vwPxPaxweb.SALES_AREA
          ELSE COALESCE(vwPxPaxweb.SALES_AREA,AG_AGENTS.SALES_AREA_DESC) 
          END
     END AS SALES_AREA_DESC_2

Upvotes: 1

Related Questions