Records with no (revenue) value not returned in results set

Database
Enthusiast

Records with no (revenue) value not returned in results set

Sorry, i'm a newbie and inherited this query.

I am pulling revenue information for customers located in BC.

The issue i am having is that when there is ZERO revenue, the record is not returned in the results set.

I would like to see the record returned with ZERO in the revenue column.

Would be very appreciative and grateful for any insight you can provide!

 

select

x.cust_nbr,

x.cust_nm,

x.adr_ln1_desc,

x.city_nm,

x.st_cd,

x.pstl_cd,

x.sale_dist_nbr,

x.sale_terr_nbr,

x.sale_terr_seg_nbr,

 

(CASE

WHEN x.orig_cntry_cd=x.dest_cntry_cd and x.shp_pay_cd NOT IN ('S') THEN 'Payor'

WHEN x.orig_cntry_cd<>x.dest_cntry_cd and x.shp_pay_cd NOT IN ('S') THEN 'Payor'

ELSE 'Other'

END) as RevType,

 

sum(net_rev_CAD) net_rev_CAD

 

from

(select a.rev_dt_yyyymm,a.shpr_cust_acct_nbr as cust_nbr, b.cust_nm, b.SHPR_ZIP,  a.orig_cntry_cd, a.dest_cntry_cd,a.svc_ctgy_cd, a.svc_bas_cd, b.natl_acct_nbr,'S' as shp_pay_cd, y.exch_rate_amt, a.net_rev_amt,

             f.adr_ln1_desc, f.st_cd, f.city_nm, f.pstl_cd, s.cust_acct_nbr, s.sale_div_nbr, s.sale_grp_nbr, s.sale_org_nbr, s.sale_rgn_nbr, s.sale_area_nbr, s.sale_dist_nbr, s.sale_terr_nbr, s.sale_terr_seg_nbr,

                                                CAST(a.net_rev_amt*y.exch_rate_amt AS decimal(11,2)) net_rev_CAD, a.shp_pce_qty                                                                                                                                                                         

from CRP_PROD_VIEW_DB.REVENUE a

LEFT join UI_ISH_PROD_DB.CUSTOMER b ON a.shpr_cust_acct_nbr = cast(b.cust_nbr AS varchar(20))

 

INNER JOIN                                                                                                                                                                        

( SELECT  f.cust_nbr,  f.adr_ln1_desc,  f.st_cd, f.city_nm, f.pstl_cd

  FROM UI_ISH_PROD_DB.contact_address f

  WHERE  cont_role_type_cd = 'PS' AND st_cd = 'BC'

  GROUP BY 1,2,3,4,5

) f

ON a.shpr_cust_acct_nbr = cast(f.cust_nbr AS varchar(20)) 

 

LEFT  JOIN

( SELECT s.cust_acct_nbr, s.sale_div_nbr, s.sale_grp_nbr, s.sale_org_nbr, s.sale_rgn_nbr, s.sale_area_nbr, s.sale_dist_nbr, s.sale_terr_nbr, s.sale_terr_seg_nbr

  FROM UI_ISH_PROD_DB.SIMULATION s

  WHERE prim_cvge_flg = 'Y' AND align_type_cd = 'P'

  GROUP BY 1,2,3,4,5,6,7,8,9

  ) s

  ON a.shpr_cust_acct_nbr = cast(s.cust_acct_nbr AS varchar(20))

 

 

INNER JOIN                                                                                                                                                                       

( SELECT CAST(CAST (y.eff_exch_rate_dt AS date format 'YYYYMM') AS char(6)) AS year_mth_nbr ,  AVG(y.exch_rate_amt) AS exch_rate_amt                                                                                                                                                                                

  FROM UI_ISH_PROD_DB.currency_exchange y 

  WHERE y.curr_cd='CAD'                                                                                                                                                                    

  GROUP BY 1                                                                                                                                                                     

 ) y                                                                                                                                                                           

ON y.year_mth_nbr=a.rev_dt_yyyymm and shp_pay_cd <> 'B' and a.rev_dt_yyyymm between '201701' and '201712'

 

union all

 

select a.rev_dt_yyyymm,c.fx_cust_acct_nbr as cust_nbr, b.cust_nm,  b.SHPR_ZIP,  a.orig_cntry_cd,a.dest_cntry_cd,a.svc_ctgy_cd, a.svc_bas_cd,b.natl_acct_nbr,'S' as shp_pay_cd, y.exch_rate_amt, a.net_rev_amt,

            f.adr_ln1_desc, f.st_cd, f.city_nm, f.pstl_cd, s.cust_acct_nbr, s.sale_div_nbr, s.sale_grp_nbr, s.sale_org_nbr, s.sale_rgn_nbr, s.sale_area_nbr, s.sale_dist_nbr, s.sale_terr_nbr, s.sale_terr_seg_nbr,

                                                CAST(a.net_rev_amt*y.exch_rate_amt AS decimal(11,2)) net_rev_CAD, a.shp_pce_qty                                                                                                                                                                        

from CRP_PROD_VIEW_DB.XREVENUE a

INNER JOIN CRP_PROD_VIEW_DB.CRP_FXGND_FX_ACCOUNT_XREF c ON a.shpr_cust_acct_nbr = c.fxgnd_cust_acct_nbr and c.fy_nbr = 2018 and c.qtr_nbr = 3 

LEFT JOIN UI_ISH_PROD_DB.CUSTOMER b ON c.fx_cust_acct_nbr = cast(b.cust_nbr AS varchar(20))                                                                                                                                                            

 

LEFT JOIN                                                                                                                                                                        

( SELECT  f.cust_nbr,  f.adr_ln1_desc,  f.st_cd, f.city_nm, f.pstl_cd

  FROM UI_ISH_PROD_DB.contact_address f

  WHERE  cont_role_type_cd = 'PS' AND st_cd = 'BC'

  GROUP BY 1,2,3,4,5

) f

ON c.fx_cust_acct_nbr = cast(f.cust_nbr AS varchar(20)) 

 

LEFT JOIN

( SELECT s.cust_acct_nbr, s.sale_div_nbr, s.sale_grp_nbr, s.sale_org_nbr, s.sale_rgn_nbr, s.sale_area_nbr, s.sale_dist_nbr, s.sale_terr_nbr, s.sale_terr_seg_nbr

  FROM UI_ISH_PROD_DB.SIMULATION s

  WHERE prim_cvge_flg = 'Y' AND align_type_cd = 'P'

 GROUP BY 1,2,3,4,5,6,7,8,9

  ) s

  ON c.fx_cust_acct_nbr = cast(s.cust_acct_nbr AS varchar(20))

 

 

INNER JOIN                                                                                                                                                                       

( SELECT CAST(CAST (y.eff_exch_rate_dt AS date format 'YYYYMM') AS char(6)) AS year_mth_nbr ,  AVG(y.exch_rate_amt) AS exch_rate_amt                                                                                                                                                                                

  FROM UI_ISH_PROD_DB.currency_exchange y                                                                                                             

  WHERE y.curr_cd='CAD'                                                                                                                                                                    

  GROUP BY 1                                                                                                                                                                     

 ) y                                                                                                                                                                           

ON y.year_mth_nbr=a.rev_dt_yyyymm

where a.rev_src_cd = 'GRND' and shp_pay_cd <> 'B' and a.rev_dt_yyyymm between '201701' and '201712'

 

union all

 

select a.rev_dt_yyyymm,a.payr_cust_acct_nbr as cust_nbr, b.cust_nm,  b.SHPR_ZIP,  a.orig_cntry_cd,a.dest_cntry_cd,a.svc_ctgy_cd, a.svc_bas_cd,b.natl_acct_nbr,shp_pay_cd, y.exch_rate_amt, a.net_rev_amt,

            f.adr_ln1_desc, f.st_cd, f.city_nm, f.pstl_cd, s.cust_acct_nbr, s.sale_div_nbr, s.sale_grp_nbr, s.sale_org_nbr, s.sale_rgn_nbr, s.sale_area_nbr, s.sale_dist_nbr, s.sale_terr_nbr, s.sale_terr_seg_nbr,

                                                CAST(a.net_rev_amt*y.exch_rate_amt AS decimal(11,2)) net_rev_CAD,a.shp_pce_qty                                                                                                                                                                        

from CRP_PROD_VIEW_DB.REVENUE a

LEFT JOIN UI_ISH_PROD_DB.customer b ON a.payr_cust_acct_nbr = cast(b.cust_nbr AS varchar(20))

 

INNER JOIN                                                                                                                                                                        

( SELECT  f.cust_nbr,  f.adr_ln1_desc,  f.st_cd, f.city_nm, f.pstl_cd

  FROM UI_ISH_PROD_DB.contact_address f

  WHERE  cont_role_type_cd = 'PS' AND st_cd = 'BC'

  GROUP BY 1,2,3,4,5

) f

ON a.payr_cust_acct_nbr = cast(f.cust_nbr AS varchar(20)) 

 

LEFT JOIN

( SELECT s.cust_acct_nbr, s.sale_div_nbr, s.sale_grp_nbr, s.sale_org_nbr, s.sale_rgn_nbr, s.sale_area_nbr, s.sale_dist_nbr, s.sale_terr_nbr, s.sale_terr_seg_nbr

  FROM UI_ISH_PROD_DB.SIMULATION s

  WHERE prim_cvge_flg = 'Y' AND align_type_cd = 'P'

  GROUP BY 1,2,3,4,5,6,7,8,9

  ) s

  ON a.payr_cust_acct_nbr = cast(s.cust_acct_nbr AS varchar(20))

 

 

INNER JOIN                                                                                                                                                                       

( SELECT CAST(CAST (y.eff_exch_rate_dt AS date format 'YYYYMM') AS char(6)) AS year_mth_nbr ,  AVG(y.exch_rate_amt) AS exch_rate_amt                                                                                                                                                                                

  FROM UI_ISH_PROD_DB.currency_exchange y                                                                                                                          

  WHERE y.curr_cd='CAD'                                                                                                                                                                    

  GROUP BY 1                                                                                                                                                                     

 ) y                                                                                                                                                                          

ON y.year_mth_nbr=a.rev_dt_yyyymm and a.rev_dt_yyyymm between '201701' and '201712'

 

union all

 

select a.rev_dt_yyyymm,c.fx_cust_acct_nbr as cust_nbr, b.cust_nm,  b.SHPR_ZIP,  a.orig_cntry_cd,a.dest_cntry_cd,a.svc_ctgy_cd, a.svc_bas_cd, b.natl_acct_nbr,shp_pay_cd, y.exch_rate_amt, a.net_rev_amt,

           f.adr_ln1_desc, f.st_cd, f.city_nm, f.pstl_cd, s.cust_acct_nbr, s.sale_div_nbr, s.sale_grp_nbr, s.sale_org_nbr, s.sale_rgn_nbr, s.sale_area_nbr, s.sale_dist_nbr, s.sale_terr_nbr, s.sale_terr_seg_nbr,

                                                CAST(a.net_rev_amt*y.exch_rate_amt AS decimal(11,2)) net_rev_CAD, a.shp_pce_qty                                                                                                                                                                        

from CRP_PROD_VIEW_DB.XREVENUE a

INNER JOIN CRP_PROD_VIEW_DB.CRP_FXGND_FX_ACCOUNT_XREF c ON a.payr_cust_acct_nbr = c.fxgnd_cust_acct_nbr and c.fy_nbr = 2018 and c.qtr_nbr = 3

LEFT JOIN UI_ISH_PROD_DB.customer b ON c.fx_cust_acct_nbr= cast(b.cust_nbr AS varchar(20))                                                                                                                                                           

 

INNER JOIN                                                                                                                                                                        

( SELECT  f.cust_nbr,  f.adr_ln1_desc,  f.st_cd, f.city_nm, f.pstl_cd

  FROM UI_ISH_PROD_DB.contact_address f

  WHERE  cont_role_type_cd = 'PS' AND st_cd = 'BC'

  GROUP BY 1,2,3,4,5

  ) f

ON c.fx_cust_acct_nbr = cast(f.cust_nbr AS varchar(20)) 

 

LEFT JOIN

( SELECT s.cust_acct_nbr, s.sale_div_nbr, s.sale_grp_nbr, s.sale_org_nbr, s.sale_rgn_nbr, s.sale_area_nbr, s.sale_dist_nbr, s.sale_terr_nbr, s.sale_terr_seg_nbr

  FROM UI_ISH_PROD_DB.SIMULATION s

  WHERE prim_cvge_flg = 'Y' AND align_type_cd = 'P'

  GROUP BY 1,2,3,4,5,6,7,8,9

  ) s

  ON  c.fx_cust_acct_nbr = cast(s.cust_acct_nbr AS varchar(20))

 

INNER JOIN                                                                                                                                                                        

( SELECT CAST(CAST (y.eff_exch_rate_dt AS date format 'YYYYMM') AS char(6)) AS year_mth_nbr ,  AVG(y.exch_rate_amt) AS exch_rate_amt                                                                                                                                                                                

  FROM UI_ISH_PROD_DB.currency_exchange y                                                                                                             

  WHERE y.curr_cd='CAD'                                                                                                                                                                    

  GROUP BY 1                                                                                                                                                                     

 ) y                                                                                                                                                                           

ON y.year_mth_nbr=a.rev_dt_yyyymm

where a.rev_src_cd = 'GRND' and a.rev_dt_yyyymm between '201701' and '201712'

and b.cust_nbr  in

) x

 

group by 1,2,3,4,5,6,7,8,9,10

5 REPLIES
Teradata Employee

Re: Records with no (revenue) value not returned in results set

Currently you have: CRP_PROD_VIEW_DB.REVENUE a LEFT join UI_ISH_PROD_DB.CUSTOMER b

In other words, keep all Revenue rows even if you can't find a matching Customer.

If you wan to keep all Customer rows even if you can't find matching Revenue, this should be reversed (e.g. Change LEFT to RIGHT).

You may need to make kother changes as well.

Junior Contributor

Re: Records with no (revenue) value not returned in results set

Usually you need to switch to an Outer Join to get those zeroes, but there are already lots of Outer Joins and it's hard to tell where it has to be placed.

 

Just two remarks:

The final and b.cust_nbr in changes the Left to an Inner Join (assuming the IN-list was just ommited), it might be enough to add it to the join:

LEFT JOIN UI_ISH_PROD_DB.customer b ON c.fx_cust_acct_nbr= cast(b.cust_nbr AS varchar(20)) 
and b.cust_nbr in ...                                                                                                                                                          

But this is just a maybe...

 

And all those cast(**bleep** AS varchar(20)) in the join conditions are a performance killer, your data model should be fixed.

 

Enthusiast

Re: Records with no (revenue) value not returned in results set

Hi Fred -

i changed the 4 references to RIGHT however still didn't work.

 

FROM:

from CRP_PROD_VIEW_DB.REVENUE a

LEFT join UI_ISH_PROD_DB.CUSTOMER b

 

TO:

from CRP_PROD_VIEW_DB.REVENUE a

RIGHT join UI_ISH_PROD_DB.CUSTOMER b

Enthusiast

Re: Records with no (revenue) value not returned in results set


@dnoethwrote:

Usually you need to switch to an Outer Join to get those zeroes, but there are already lots of Outer Joins and it's hard to tell where it has to be placed.

 

Just two remarks:

The final and b.cust_nbr in changes the Left to an Inner Join (assuming the IN-list was just ommited), it might be enough to add it to the join:

LEFT JOIN UI_ISH_PROD_DB.customer b ON c.fx_cust_acct_nbr= cast(b.cust_nbr AS varchar(20)) 
and b.cust_nbr in ...                                                                                                                                                          

But this is just a maybe...

 

And all those cast(**bleep** AS varchar(20)) in the join conditions are a performance killer, your data model should be fixed.

 


Hi there!

Thanks, yes, the last b.cust_nbr should be removed.

copy/paste oversight :)

Teradata Employee

Re: Records with no (revenue) value not returned in results set

As Dieter says, it's hard to be sure how much needs to change.

But it looks like the joins to the derived tables for exchange rate should be LEFT OUTER joins instead of INNER joins.