Case when syntax help

Database
New Member

Case when syntax help

Hello All,

 

I'm trying to build a query that links two tables that has a counts a given column when their key values match, count the same column when they don't match, and count total in that column. Below is the simplified query.

 

Thank you for your help ahead of time.

 

 

 

SyntaxEditor Code Snippet

create MULTISET volatile table RDC_DOOR_PILOT_TRAILER as()WITH DATA UNIQUE  PRIMARY INDEX ( RDC_DRPILT_BILL_NBR, RDC_NBR,STR_NBR,FSCL_YR_WK_KEY_VAL,FSCL_YR) ON COMMIT PRESERVE ROWS;

select
case when A.OUT_FRT_BILL_NBR  in RDC_DOOR_PILOT_TRAILER.RDC_DRPILT_BILL_NBR then count(distinct [PR_SC_INTG_VIEWS.DC_WMS_OUTB_PO].OUTB_FRT_BILL_NBR) end as DOOR_TRAILER_CNT,case when A.OUT_FRT_BILL_NBR <> RDC_DOOR_PILOT_TRAILER.RDC_DRPILT_BILL_NBR then count(distinct [PR_SC_INTG_VIEWS.DC_WMS_OUTB_PO].OUTB_FRT_BILL_NBR) end as NONDOOR_TRAILER_CNT,count(distinct [PR_SC_INTG_VIEWS.DC_WMS_OUTB_PO].OUTB_FRT_BILL_NBR) end as Total_TRAILER_CNT,

from PR_SC_INTG_VIEWS.DC_WMS_OUTB_PO A
full outer join RDC_DOOR_PILOT_TRAILER on
RDC_DOOR_PILOT_TRAILER.RDC_DRPILT_BILL_NBR =A.OUTB_FRT_BILL_NBR

Group by 1,2,3,4,5;

 

1 REPLY
Teradata Employee

Re: Case when syntax help

Are you aiming for something closer to this?

select
count(distinct CASE when A.OUT_FRT_BILL_NBR = B.RDC_DRPILT_BILL_NBR THEN A.OUTB_FRT_BILL_NBR end) as DOOR_TRAILER_CNT
,count(distinct CASE when A.OUT_FRT_BILL_NBR <> B.RDC_DRPILT_BILL_NBR THEN A.OUTB_FRT_BILL_NBR end) as NONDOOR_TRAILER_CNT
,count(distinct A.OUTB_FRT_BILL_NBR) end as Total_TRAILER_CNT
from PR_SC_INTG_VIEWS.DC_WMS_OUTB_PO A
full outer join RDC_DOOR_PILOT_TRAILER B on
B.RDC_DRPILT_BILL_NBR =A.OUTB_FRT_BILL_NBR

 

You seem to expect table A to contain all the values and table B to be a subset. If that's true, LEFT JOIN is sufficient and you don't need FULL JOIN.

 

If there potentially are many duplicate values, eliminate duplicates before the join (and use COUNT instead of COUNT DISTINCT); for example

... 
from (SELECT OUT_FRT_BILL_NBR FROM PR_SC_INTG_VIEWS.DC_WMS_OUTB_PO GROUP BY 1) A
...

 

By the way, don't use SQL Server notation [square brackets].