Query syntax error

Euro5
Euro5 used Ask the Experts™
on
I am getting an error but I can't figure out where it is wrong.  It is critical that I figure this out quickly!

Syntax error, expected something like an 'EXCEPT' keyword or an 'UNION' keyword or a 'MINUS' keyword between ')' and ';'

However, there is not even a ';' in the query and I have gone through line by line.

Can anyone help????



SELECT 
OpCo, 
Dom_Intl, 
CASE 
-----SMARTPOST PART-------
 WHEN surch_desc IN ('SP DAS - C','SP DAS - Extended') THEN 'Delivery Area Surcharge'
 WHEN surch_desc IN ('SP Non-Machinable', 'Non-Machinable') THEN 'Non-Machinable'
 WHEN surch_desc = 'Fuel Surcharge' THEN 'Fuel Surcharge'
 WHEN surch_desc = 'Package Relabel' THEN 'Package Relabel'
------PARCEL PART-------
 WHEN surch_desc IN ('Additional Handling Charge - N') THEN 'Additional Handling Surcharge - Non-stackable'
 WHEN surch_desc LIKE '%Addl Handling Charge%' THEN 'Additional Handling Surcharge' 
 WHEN SUBSTR(surch_desc,1, 19) = 'Additional Handling' THEN 'Additional Handling Surcharge' 
 WHEN surch_desc LIKE '%STACKABLE%' THEN 'Additional Handling Surcharge - Non-stackable'
 WHEN SUBSTR(surch_desc,1,3) = 'AHS' THEN 'Additional Handling Surcharge' 
 WHEN surch_desc IN ('Address Correction - Grd','Address Correction') THEN 'Address Correction' 
 WHEN SUBSTR(surch_desc,1, 21) = 'Additional Entry Line' THEN 'Ancillary Clearance Service Fees' 
 WHEN surch_desc IN  ('Electronic Entry Fee','Fax Fee','Storage Fee','U.S. Food & Drug Admin Process','Broker Document Transfer Fee','Special Brokerage Processing','Processing Fee','Individual Entry From Fee','Extra Storage Fee','Export Clearance Fee','Entry Copy Fee','Advancement Fee') THEN 'Ancillary Clearance Service Fees' 
 WHEN surch_desc LIKE '%FDA%' THEN 'Ancillary Clearance Service Fees' 
 WHEN surch_desc = 'Broker SELECTiON Option' THEN 'Broker Select Option Fee'
 WHEN surch_desc = 'Clearance Entrance Fee' THEN 'Clearance Entry Fee' 
 WHEN surch_desc IN ('Inaccessible Dangerous Goods','Inacessible Dangerous Goods','Dry Ice','Accessible Dangerous Goods') THEN 'Dangerous Goods'
 WHEN surch_desc LIKE '%Declared Value%' THEN 'Declared Value' 
 WHEN surch_desc LIKE '%DAS%' THEN 'Delivery Area Surcharge' 
 WHEN SUBSTR(surch_desc,1,7) = 'FHD DAS' THEN 'Delivery Area Surcharge' 
 WHEN surch_desc = 'Reattempted Delivery' THEN 'Delivery Reattempts'
 WHEN surch_desc LIKE '%Duty%' THEN 'Duties and Taxes' 
 WHEN surch_desc LIKE '%Dty%' THEN 'Duties and Taxes' 
 WHEN surch_desc LIKE '%Tax%' THEN 'Duties and Taxes'  
 WHEN surch_desc LIKE '%GST%' THEN 'Duties and Taxes' 
 WHEN surch_desc LIKE '%HST%' THEN 'Duties and Taxes' 
 WHEN surch_desc LIKE '%QST%' THEN 'Duties and Taxes' 
 WHEN surch_desc LIKE '%Service Tax%' THEN 'Duties and Taxes' 
 WHEN surch_desc LIKE '%Mexico%' THEN 'Duties and Taxes'  
 WHEN surch_desc LIKE '%Canada%' THEN 'Duties and Taxes'  
 WHEN surch_desc LIKE '%Government%' THEN 'Duties and Taxes' 
 WHEN surch_desc LIKE '%Terminal Fee%' THEN 'Duties and Taxes' 
 WHEN surch_desc LIKE '%Postal Fee%' THEN 'Duties and Taxes' 
 WHEN surch_desc LIKE '%VAT%' THEN 'Duties and Taxes' 
 WHEN surch_desc LIKE '%Customs%' THEN 'Duties and Taxes'
 WHEN surch_desc IN ('Airport Transfer Fee','Low Value Entry Exception Fee','Free Commercial Zone','BSO AWB RevalidatiON Fee') THEN 'Duties and Taxes'
 WHEN surch_desc = 'Internet EEI Request' THEN 'Electronic Export Information Filing Fee'
 WHEN surch_desc IN ('Oncall Pickup','Return On Call Surcharge','Courier Pickup Charge') THEN 'Express On-Call Pickup Charge (Courier Pickup Charge)' 
 WHEN surch_desc = 'Extra Labor' THEN 'Extra Services Charge' 
 WHEN surch_desc = 'FedEx Collect on Delivery Service' THEN 'FedEx Collect On Delivery (C.O.D.)'
 WHEN surch_desc LIKE '%C.O.D.%' THEN 'FedEx Collect On Delivery (C.O.D.)'
 WHEN surch_desc IN ('Live Entry Processing Fee','Indirect Signature Required','Adult Signature Required','Direct Signature Required') THEN 'FedEx Delivery Signature Options' 
 WHEN surch_desc IN ('Return Email Label','Return Email Label Surcharge') THEN 'FedEx Email Return Label' 
 WHEN surch_desc = 'Express Tag Surcharge' THEN 'FedEx ExpressTag' 
 WHEN surch_desc LIKE '%Alternate Address Pickup%' THEN 'FedEx Ground Alternate Address Pickup'
 WHEN surch_desc = 'Call Tag' THEN 'FedEx Ground Call Tag'
 WHEN surch_desc IN ('FedEx Evening Home Delivery') THEN 'FedEx Home Delivery Convenient Delivery Options' 
 WHEN surch_desc IN ('ON Demand Care','On Demand Care Fee') THEN 'FedEx On Demand Care' 
 WHEN surch_desc IN ('Return Printed Label','Return Printed Label Surcharge') THEN 'FedEx Print Return Label' 
 WHEN surch_desc IN ('Ground Fuel Surcharge', 'Fuel Surcharge') THEN 'Fuel Surcharge' 
 WHEN surch_desc LIKE '%NDOC%' THEN 'Ground On-Call Pickup Charge' 
 WHEN surch_desc LIKE '%SDOC%'  THEN 'Ground On-Call Pickup Charge' 
 WHEN surch_desc = 'Hazardous Material' THEN 'Hazardous Materials' 
 WHEN surch_desc = 'Inside Delivery Charge' THEN 'Inside Delivery Charge'
 WHEN surch_desc = 'Inside Pickup Charge' THEN 'Inside Pickup Charge'
 WHEN surch_desc = 'Out of Delivery Area' THEN 'International Out-of-Delivery-Area Surcharge' 
 WHEN surch_desc = 'Out of Pickup Area' THEN 'International Out-of-Pickup-Area Surcharge' 
 WHEN surch_desc IN ('H0 Delivery','H1 Delivery','H2 Delivery','H3 Delivery','H4 Delivery','H5 Delivery','H6 Delivery','H7 Delivery') THEN 'Metro Service Area Delivery' 
 WHEN surch_desc IN ('H0 Pickup','H1 Pickup','H2 Pickup','H3 Pickup','H4 Pickup','H5 Pickup','H6 Pickup','H7 Pickup') THEN 'Metro Service Area Pickup' 
 WHEN surch_desc = 'MX Linehaul' THEN 'Mexico Linehaul'
 WHEN surch_desc IN ('Missing Account Number','Non entered account number cha','Invalid Number Correction','Non-Account Billing Charge','Invalid Account Number','Account Number Correction') THEN 'Missing or Invalid Account Number'
 WHEN surch_desc LIKE '%Remote Rural%' THEN 'Northern Canada Surcharge'
 WHEN surch_desc LIKE '%Oversize%' THEN 'Oversize Charge'
 WHEN surch_desc IN ('Unauthorized Oversize Surcharge','Oversize Extra Service Fee','Oversize Charge') THEN 'Oversize Charge' 
 WHEN surch_desc = 'Priority Alert' THEN 'Priority Alert' 
 WHEN surch_desc IN ('PA Plus','Domestic Priority Alert Plus F') THEN 'Priority AlertPlus' 
 WHEN surch_desc LIKE '%Residential Delivery%' THEN 'Residential Delivery Charge'
 WHEN surch_desc LIKE '%Residential Deli%' THEN 'Residential Delivery Charge'
 WHEN surch_desc IN ('Express Dom Freight Resi Del','Residential Delivery','Residential Delivery - Grd','FedEx Ground Residential', 'Express Dom Resi Del','FedEx Home Delivery Residential') THEN 'Residential Delivery Charge' 
 WHEN surch_desc = 'Return Pickup Fee' THEN 'Return Pickup' 
 WHEN surch_desc = 'Saturday Delivery' THEN 'Saturday Delivery' 
 WHEN surch_desc = 'Saturday Pickup' THEN 'Saturday Pickup' 
 WHEN Product_Name = 'Non-Transportation Related'  THEN 'Non-Transportation Related' 
 ELSE 'Other' END AS SG_Surcharge,
surch_desc AS Surcharge_Description, 
CAST(misc_amt_usd AS DECIMAL(10,2)) AS USD_Surchange_Amount,  
Occurrences

FROM(SELECT           
CAST(cust.natl_acct_nbr AS VARCHAR(10)) AS National, 
cust.natl_acct_sgrp_nbr AS Subgroup, 
CAST(cust.cust_nbr AS VARCHAR(10)) AS Nine_Digit,
bill.inv_nbr AS Invoice_Number,
CAST('' AS VARCHAR(20)) AS Tracking_Prefix,
bill.shp_trk_nbr AS Tracking_Number,
CAST('Express' AS VARCHAR(25)) AS OpCo,
CAST('International' AS VARCHAR(25)) AS Dom_Intl,
CASE                                                                             
  WHEN bill.svc_bas_cd = '01' THEN 'International Priority'                   
  WHEN bill.svc_bas_cd in ('03','04','05','20') THEN 'International Economy'                        
  WHEN bill.svc_bas_cd in ('06','47','57') THEN 'International First'           
  WHEN bill.svc_bas_cd = '70' THEN 'International Priority Freight'                  
  WHEN bill.svc_bas_cd in ('80','83','86') THEN 'International Economy Freight'                                          
  WHEN bill.svc_bas_cd = '17' THEN 'International Economy DirectDistribution'       
  WHEN bill.svc_bas_cd = '18' THEN 'International Priority DirectDistribution'  
  WHEN bill.svc_bas_cd = '47' THEN 'International First' 
  WHEN bill.svc_bas_cd = '21' THEN 'International MailService Standard'                  
  WHEN bill.svc_bas_cd = '31' THEN 'International MailService Premium'  
  WHEN bill.svc_bas_cd = '96' THEN 'Airport to Airport'
  WHEN bill.svc_bas_cd in ('00', '84') THEN 'Transborder Distribution'                                                                              
  ELSE 'Other'||' '||bill.svc_bas_cd                   
END AS Product_Name, 
bill.pkg_typ_cd AS Package_Type_Cd,
CAST(SUBSTR(inv_month,1,4) AS INTEGER) AS Inv_Year,
CAST(bill.inv_month AS INTEGER) AS Inv_yyyymm,
CAST(SUBSTR(invoice_date,1,4)||'-'||SUBSTR(invoice_date,6,2)||'-01' AS DATE) AS Inv_Month,
CAST(SUBSTR(shp_dt,1,4) AS INTEGER) AS Ship_Year,
CAST(SUBSTR(shp_dt,1,4)||SUBSTR(shp_dt,6,2) AS INTEGER) AS Ship_yyyymm,
CAST(SUBSTR(shp_dt,1,4)||'-'||SUBSTR(shp_dt,6,2)||'-01' AS DATE) AS Ship_Month,
shp_dt,
payr_co AS Payer_Company,
payr_cust_nbr AS Payer_Account_Number,
shp_co_nm AS Ship_Company,
shp_nm AS Ship_Name,
shp_addr AS Ship_Address,
shp_cty AS Ship_City,
shp_st_cd AS Ship_State,
shp_pstl_cd AS Ship_Postal,
CASE
 WHEN shp_st_cd = 'PR' THEN 'PR'
 ELSE shp_cntry 
END AS Ship_Country,
shp_pstl_cd AS Actual_Orig_Zip,
shp_cust_nbr AS Ship_Account_Number,
recp_co_nm AS Recp_Company,
recp_nm AS Recp_Name,
recp_addr AS Recp_Address,
recp_cty AS Recp_City,
recp_st_cd AS Recp_State,
recp_pstl_cd AS Recp_Postal,
CASE
 WHEN recp_st_cd = 'PR' THEN 'PR'
 ELSE recp_cntry_cd 
END AS Recp_Country,
recp_origl_addr AS Orig_Recp_Address,
recp_origl_cty AS Orig_Recp_City,
recp_origl_st_cd AS Orig_Recp_State,
recp_origl_pstl_cd AS Orig_Recp_Postal,
recp_origl_cntry_cd AS Orig_Recp_Country,
CAST(CASE
   WHEN bill.wgt_type_cd = 'K' THEN bill.tot_wgt * 2.2046  
   ELSE bill.tot_wgt 
END AS DECIMAL(8,1)) AS Lbs_Orig_Wgt,
CAST(CASE
   WHEN bill.wgt_type_cd = 'K' THEN bill.shp_rate_wgt * 2.2046  
   ELSE bill.shp_rate_wgt
END AS DECIMAL(8,1)) AS Lbs_Rated_Wgt,
CAST(CASE
   WHEN bill.wgt_type_cd = 'P' THEN bill.tot_wgt * 0.4536  
   ELSE bill.tot_wgt 
END AS DECIMAL(8,1)) AS Kgs_Orig_Wgt,
CAST(CASE
   WHEN bill.wgt_type_cd = 'P' THEN bill.shp_rate_wgt * 0.4536 
   ELSE bill.shp_rate_wgt 
END AS DECIMAL(8,1)) AS Kgs_Rated_Wgt,
CASE
 WHEN ZEROIFNULL(dim_hgt * dim_lth * dim_width) = 0 THEN 'NA'
 ELSE TRIM(dim_hgt)||' x '||TRIM(dim_lth)||' x '||TRIM(dim_width)
END AS Dimensions,

CAST(c.misc_resn_desc AS VARCHAR(100)) AS surch_desc,  
bill.bill_legal_enti AS Payer_Country,
bill.bill_curr_cd AS Original_Currency,
ZEROIFNULL(m.misc_amt * bill.exch_usd) AS misc_amt_usd,
1 AS Occurrences

FROM  UI_ISH_PROD_DB.customer cust, 
UI_ISH_PROD_DB.billed_intl_express bill 

INNER JOIN ui_ish_prod_db.billed_intl_misc m 
ON bill.inv_unique_id = m.inv_unique_id 
AND m.ab_seq_nbr = bill.ab_seq_nbr 

LEFT OUTER JOIN  ui_ish_prod_db.billed_intl_misc_resn_cd c 
ON m.misc_rsn_cd = c.misc_resn_cd 
WHERE  (bill.reb_resn = '' OR bill.reb_resn IS NULL) AND surch_desc NOT IN ('?','Weekday Delivery','Week Day Delivery')
AND cust.cust_nbr = payr_cust_nbr AND bill.shp_dt BETWEEN '2018-01-01' AND '2018-12-31'AND cust.natl_acct_nbr IN (1041) 
UNION ALL

SELECT           
CAST(cust.natl_acct_nbr AS VARCHAR(10)) AS National, 
cust.natl_acct_sgrp_nbr AS Subgroup, 
CAST(cust.cust_nbr AS VARCHAR(10)) AS Nine_Digit,
bill.inv_nbr AS Invoice_Number,
CAST('' AS VARCHAR(20)) AS Tracking_Prefix,
bill.shp_trk_nbr AS Tracking_Number,
CAST('Express' AS VARCHAR(25)) AS OpCo,
CAST('International' AS VARCHAR(25)) AS Dom_Intl,
CASE                                                                             
  WHEN bill.svc_bas_cd = '01' THEN 'International Priority'                   
  WHEN bill.svc_bas_cd in ('03','04','05','20') THEN 'International Economy'                        
  WHEN bill.svc_bas_cd in ('06','47','57') THEN 'International First'           
  WHEN bill.svc_bas_cd = '70' THEN 'International Priority Freight'                  
  WHEN bill.svc_bas_cd in ('80','83','86') THEN 'International Economy Freight'                                          
  WHEN bill.svc_bas_cd = '17' THEN 'International Economy DirectDistribution'       
  WHEN bill.svc_bas_cd = '18' THEN 'International Priority DirectDistribution'  
  WHEN bill.svc_bas_cd = '47' THEN 'International First' 
  WHEN bill.svc_bas_cd = '21' THEN 'International MailService Standard'                  
  WHEN bill.svc_bas_cd = '31' THEN 'International MailService Premium'  
  WHEN bill.svc_bas_cd = '96' THEN 'Airport to Airport'
  WHEN bill.svc_bas_cd in ('00', '84') THEN 'Transborder Distribution'                                                                              
  ELSE 'Other'||' '||bill.svc_bas_cd                   
END AS Product_Name, 
bill.pkg_typ_cd AS Package_Type, 
CAST(SUBSTR(inv_month,1,4) AS INTEGER) AS Inv_Year,
CAST(bill.inv_month AS INTEGER) AS Inv_yyyymm,
CAST(SUBSTR(invoice_date,1,4)||'-'||SUBSTR(invoice_date,6,2)||'-01' AS DATE) AS Inv_Month,
CAST(SUBSTR(shp_dt,1,4) AS INTEGER) AS Ship_Year,
CAST(SUBSTR(shp_dt,1,4)||SUBSTR(shp_dt,6,2) AS INTEGER) AS Ship_yyyymm,
CAST(SUBSTR(shp_dt,1,4)||'-'||SUBSTR(shp_dt,6,2)||'-01' AS DATE) AS Ship_Month,
shp_dt,
payr_co AS Payer_Company,
payr_cust_nbr AS Payer_Account_Number,
shp_co_nm AS Ship_Company,
shp_nm AS Ship_Name,
shp_addr AS Ship_Address,
shp_cty AS Ship_City,
shp_st_cd AS Ship_State,
shp_pstl_cd AS Ship_Postal,
CASE
 WHEN shp_st_cd = 'PR' THEN 'PR'
 ELSE shp_cntry 
END AS Ship_Country,
shp_pstl_cd AS Actual_Orig_Zip,
shp_cust_nbr AS Ship_Account_Number,
recp_co_nm AS Recp_Company,
recp_nm AS Recp_Name,
recp_addr AS Recp_Address,
recp_cty AS Recp_City,
recp_st_cd AS Recp_State,
recp_pstl_cd AS Recp_Postal,
CASE
 WHEN recp_st_cd = 'PR' THEN 'PR'
 ELSE recp_cntry_cd 
END AS Recp_Country,
recp_origl_addr AS Orig_Recp_Address,
recp_origl_cty AS Orig_Recp_City,
recp_origl_st_cd AS Orig_Recp_State,
recp_origl_pstl_cd AS Orig_Recp_Postal,
recp_origl_cntry_cd AS Orig_Recp_Country,
CAST(CASE
   WHEN bill.wgt_type_cd = 'K' THEN bill.tot_wgt * 2.2046  
   ELSE bill.tot_wgt 
END AS DECIMAL(8,1)) AS Lbs_Orig_Wgt,
CAST(CASE
   WHEN bill.wgt_type_cd = 'K' THEN bill.shp_rate_wgt * 2.2046  
   ELSE bill.shp_rate_wgt
END AS DECIMAL(8,1)) AS Lbs_Rated_Wgt,
CAST(CASE
   WHEN bill.wgt_type_cd = 'P' THEN bill.tot_wgt * 0.4536  
   ELSE bill.tot_wgt 
END AS DECIMAL(8,1)) AS Kgs_Orig_Wgt,
CAST(CASE
   WHEN bill.wgt_type_cd = 'P' THEN bill.shp_rate_wgt * 0.4536 
   ELSE bill.shp_rate_wgt 
END AS DECIMAL(8,1)) AS Kgs_Rated_Wgt,
CASE
 WHEN ZEROIFNULL(dim_hgt * dim_lth * dim_width) = 0 THEN 'NA'
 ELSE TRIM(dim_hgt)||' x '||TRIM(dim_lth)||' x '||TRIM(dim_width)
END AS Dimensions,
'Declared Value' AS surch_desc,  
bill.bill_legal_enti AS Payer_Country,
bill.bill_curr_cd AS Original_Currency,
ZEROIFNULL(bill.bill_to_ins * bill.exch_usd) AS misc_amt_usd,
1 AS Occurrences

FROM  ui_ish_prod_db.billed_intl_express bill, 
UI_ISH_PROD_DB.customer cust 
WHERE (bill.reb_resn = '' OR bill.reb_resn IS NULL) AND bill.bill_to_ins > 0  
AND cust.cust_nbr = payr_cust_nbr  AND  Inv_yyyymm BETWEEN 201812 and 201812  AND cust.natl_acct_nbr in (0) UNION ALL

SELECT         
CAST(cust.natl_acct_nbr AS VARCHAR(10)) AS National, 
cust.natl_acct_sgrp_nbr AS Subgroup,
CAST(cust.cust_nbr AS VARCHAR(10)) AS Nine_Digit,
bill.inv_nbr AS Invoice_Number,
grnd_trk_prefix_nbr AS Tracking_Prefix,
bill.shp_trk_nbr AS Tracking_Number,
'Ground' AS OpCo, 
'International' AS Dom_Intl,
CASE                         
            WHEN  bill.svc_bas_cd IN ('92', '092') THEN 'International Ground'
            WHEN  bill.svc_bas_cd IN ('90', '090') THEN 'International Home Delivery'
            ELSE    'OTHER '||svc_bas_cd 
END AS Product_Name,
bill.pkg_typ_cd AS Package_Type, 
CAST(SUBSTR(inv_month,1,4) AS INTEGER) AS Inv_Year,
CAST(bill.inv_month AS INTEGER) AS Inv_yyyymm,
CAST(SUBSTR(invoice_date,1,4)||'-'||SUBSTR(invoice_date,6,2)||'-01' AS DATE) AS Inv_Month,
CAST(SUBSTR(shp_dt,1,4) AS INTEGER) AS Ship_Year,
CAST(SUBSTR(shp_dt,1,4)||SUBSTR(shp_dt,6,2) AS INTEGER) AS Ship_yyyymm,
CAST(SUBSTR(shp_dt,1,4)||'-'||SUBSTR(shp_dt,6,2)||'-01' AS DATE) AS Ship_Month,
shp_dt,
payr_co AS Payer_Company,
payr_cust_nbr AS Payer_Account_Number,
shp_co_nm AS Ship_Company,
shp_nm AS Ship_Name,
shp_addr AS Ship_Address,
shp_cty AS Ship_City,
shp_st_cd AS Ship_State,
shp_pstl_cd AS Ship_Postal,
shp_cntry AS Ship_Country,
shp_pstl_cd AS Actual_Orig_Zip,
shp_cust_nbr AS Ship_Account_Number,
recp_co_nm AS Recp_Company,
recp_nm AS Recp_Name,
recp_addr AS Recp_Address,
recp_cty AS Recp_City,
recp_st_cd AS Recp_State,
recp_pstl_cd AS Recp_Postal,
recp_cntry_cd AS Recp_Country,
recp_origl_addr AS Orig_Recp_Address,
recp_origl_cty AS Orig_Recp_City,
recp_origl_st_cd AS Orig_Recp_State,
recp_origl_pstl_cd AS Orig_Recp_Postal,
recp_origl_cntry_cd AS Orig_Recp_Country,
CAST(CASE
  WHEN bill.wgt_type_cd = 'K' THEN bill.tot_wgt * 2.2046  
  ELSE bill.tot_wgt 
END AS DECIMAL(8,1)) AS Lbs_Orig_Wgt,
CAST(CASE
  WHEN bill.wgt_type_cd = 'K' THEN bill.shp_rate_wgt * 2.2046  
  ELSE bill.shp_rate_wgt 
END AS DECIMAL(8,1)) AS Lbs_Rated_Wgt,
CAST(CASE
  WHEN bill.wgt_type_cd = 'P' THEN bill.tot_wgt * 0.4536  
  ELSE bill.tot_wgt 
END AS DECIMAL(8,1)) AS Kgs_Orig_Wgt,
CAST(CASE
  WHEN bill.wgt_type_cd = 'P' THEN bill.shp_rate_wgt * 0.4536
  ELSE bill.shp_rate_wgt 
END AS DECIMAL(8,1)) AS Kgs_Rated_Wgt,
CASE
 WHEN ZEROIFNULL(dim_hgt * dim_lth * dim_width) = 0 THEN 'NA'
 ELSE TRIM(dim_hgt)||' x '||TRIM(dim_lth)||' x '||TRIM(dim_width)
END AS Dimensions,
CAST(c.misc_resn_desc AS VARCHAR(100)) AS surch_desc,  
bill.bill_legal_enti AS Payer_Country,
bill.bill_curr_cd AS Original_Currency,
ZEROIFNULL(m.misc_amt * bill.exch_usd) AS misc_amt_usd,
1 AS Occurrences

FROM  
UI_ISH_PROD_DB.customer cust, 
ui_ish_prod_db.billed_intl_ground bill 

INNER JOIN ui_ish_prod_db.billed_intl_misc m 
ON bill.INV_UNIQUE_ID = m.inv_unique_id 
AND m.AB_SEQ_NBR = bill.ab_seq_nbr
 
LEFT OUTER JOIN 
ui_ish_prod_db.billed_intl_misc_resn_cd c 
ON m.misc_rsn_cd = c.misc_resn_cd 

WHERE (bill.reb_resn = '' OR bill.reb_resn IS NULL) AND surch_desc NOT IN ('?','Weekday Delivery', 'Week Day Delivery')
AND cust.cust_nbr = payr_cust_nbr  AND  Inv_yyyymm BETWEEN 201812 and 201812  AND cust.natl_acct_nbr in (0) UNION ALL

SELECT           
CAST(cust.natl_acct_nbr AS VARCHAR(10)) AS National, 
cust.natl_acct_sgrp_nbr AS Subgroup, 
CAST(cust.cust_nbr AS VARCHAR(10)) AS Nine_Digit,
bill.inv_nbr AS Invoice_Number,
grnd_trk_prefix_nbr AS Tracking_Prefix,
bill.shp_trk_nbr AS Tracking_Number,
'Ground' AS OpCo, 
'International' AS Dom_Intl,
CASE                         
            WHEN  bill.svc_bas_cd IN ('92', '092') THEN 'International Ground'
            WHEN  bill.svc_bas_cd IN ('90', '090') THEN 'International Home Delivery'
            ELSE    'OTHER '||svc_bas_cd 
END AS Product_Name,
bill.pkg_typ_cd AS Package_Type, 
CAST(SUBSTR(inv_month,1,4) AS INTEGER) AS Inv_Year,
CAST(bill.inv_month AS INTEGER) AS Inv_yyyymm,
CAST(SUBSTR(invoice_date,1,4)||'-'||SUBSTR(invoice_date,6,2)||'-01' AS DATE) AS Inv_Month,
CAST(SUBSTR(shp_dt,1,4) AS INTEGER) AS Ship_Year,
CAST(SUBSTR(shp_dt,1,4)||SUBSTR(shp_dt,6,2) AS INTEGER) AS Ship_yyyymm,
CAST(SUBSTR(shp_dt,1,4)||'-'||SUBSTR(shp_dt,6,2)||'-01' AS DATE) AS Ship_Month,
shp_dt,
payr_co AS Payer_Company,
payr_cust_nbr AS Payer_Account_Number,
shp_co_nm AS Ship_Company,
shp_nm AS Ship_Name,
shp_addr AS Ship_Address,
shp_cty AS Ship_City,
shp_st_cd AS Ship_State,
shp_pstl_cd AS Ship_Postal,
shp_cntry AS Ship_Country,
shp_pstl_cd AS Actual_Orig_Zip,
shp_cust_nbr AS Ship_Account_Number,
recp_co_nm AS Recp_Company,
recp_nm AS Recp_Name,
recp_addr AS Recp_Address,
recp_cty AS Recp_City,
recp_st_cd AS Recp_State,
recp_pstl_cd AS Recp_Postal,
recp_cntry_cd AS Recp_Country,
recp_origl_addr AS Orig_Recp_Address,
recp_origl_cty AS Orig_Recp_City,
recp_origl_st_cd AS Orig_Recp_State,
recp_origl_pstl_cd AS Orig_Recp_Postal,
recp_origl_cntry_cd AS Orig_Recp_Country,
CAST(CASE
  WHEN bill.wgt_type_cd = 'K' THEN bill.tot_wgt * 2.2046  
  ELSE bill.tot_wgt 
END AS DECIMAL(8,1)) AS Lbs_Orig_Wgt,
CAST(CASE
  WHEN bill.wgt_type_cd = 'K' THEN bill.shp_rate_wgt * 2.2046  
  ELSE bill.shp_rate_wgt 
END AS DECIMAL(8,1)) AS Lbs_Rated_Wgt,
CAST(CASE
  WHEN bill.wgt_type_cd = 'P' THEN bill.tot_wgt * 0.4536  
  ELSE bill.tot_wgt 
END AS DECIMAL(8,1)) AS Kgs_Orig_Wgt,
CAST(CASE
  WHEN bill.wgt_type_cd = 'P' THEN bill.shp_rate_wgt * 0.4536
  ELSE bill.shp_rate_wgt 
END AS DECIMAL(8,1)) AS Kgs_Rated_Wgt,
CASE
 WHEN ZEROIFNULL(dim_hgt * dim_lth * dim_width) = 0 THEN 'NA'
 ELSE TRIM(dim_hgt)||' x '||TRIM(dim_lth)||' x '||TRIM(dim_width)
END AS Dimensions,
'Declared Value' AS surch_desc,  
bill.bill_legal_enti AS Payer_Country,
bill.bill_curr_cd AS Original_Currency,
ZEROIFNULL(bill.bill_to_ins * bill.exch_usd) AS misc_amt_usd,
1 AS Occurrences

FROM  ui_ish_prod_db.billed_intl_ground bill, 
UI_ISH_PROD_DB.customer cust 

WHERE (bill.reb_resn = '' OR bill.reb_resn IS NULL) AND bill.bill_to_ins > 0 
AND cust.cust_nbr = payr_cust_nbr AND bill.shp_dt BETWEEN '2018-01-01' AND '2018-12-31'AND cust.natl_acct_nbr IN (0) 
UNION ALL

SELECT 
CAST(cust.natl_acct_nbr AS VARCHAR(10)) AS National, 
cust.natl_acct_sgrp_nbr AS Subgroup, 
CAST(cust.cust_nbr AS VARCHAR(10)) AS Nine_Digit,
bill.inv_nbr AS Invoice_Number,
bill.shp_grnd_trk_prefix AS Tracking_Prefix,
bill.shp_trk_nbr AS Tracking_Number,
'Ground' AS OpCo, 
'Domestic' AS Dom_Intl,
CASE
   WHEN TRIM(LEADING '0' FROM(SHP_GRND_TRK_PREFIX)) LIKE '997%' THEN 'Call Tag'   
   WHEN grnd_svc_cd  IN ('131','135','136','137','140','141','836','842','847','848') THEN 'Return Manager'
   WHEN grnd_svc_cd  IN('018','025','484','284','214','314') THEN 'Package Return Program'
   WHEN grnd_svc_cd IN ('900') THEN 'Returns System'
   WHEN grnd_srt_key IN ('601','602','611','612','621','622','631','632','641','642') THEN 'Multiweight'
   WHEN svc_bas_cd='92' THEN 'Ground' 
   WHEN svc_bas_cd='90' THEN 'Home Delivery'
   ELSE 'Other '||grnd_srt_key 
END AS Product_Name,
bill.pkg_typ_cd AS Package_Type, 
CAST(SUBSTR(bill.inv_dt_yyyymm,1,4) AS INTEGER) AS Inv_Year,
CAST(bill.inv_dt_yyyymm AS INTEGER) AS inv_yyyymm,
CAST(SUBSTR(inv_dt,7,4)||'-'||SUBSTR(inv_dt,1,2)||'-01' AS DATE) AS Inv_Month,
CAST(SUBSTR(shp_dt,7,4) AS INTEGER) AS Ship_Year,
CAST(SUBSTR(shp_dt,7,4)||SUBSTR(shp_dt,1,2) AS INTEGER) AS Ship_yyyymm,
CAST(SUBSTR(shp_dt,7,4)||'-'||SUBSTR(shp_dt,1,2)||'-01' AS DATE) AS Ship_Month,
shp_dt,
cust.cust_nm AS Payer_Company,
payr_cust_nbr AS Payer_Account_Number,
shp_co_nm AS Ship_Company,
shp_nm AS Ship_Name,
shp_addr AS Ship_Address,
shp_cty AS Ship_City,
shp_st_cd AS Ship_State,
shp_pstl_cd AS Ship_Postal,
'US' AS Ship_Country,
grnd_orig_zip AS Actual_Orig_Zip,
shp_cust_nbr AS Ship_Account_Number,
recp_co_nm AS Recp_Company,
recp_nm AS Recp_Name,
recp_addr AS Recp_Address,
recp_cty AS Recp_City,
recp_st_cd AS Recp_State,
recp_pstl_cd AS Recp_Postal,
'US' AS Recp_Country,
recp_origl_addr AS Orig_Recp_Address,
recp_origl_cty AS Orig_Recp_City,
recp_origl_st_cd AS Orig_Recp_State,
recp_origl_pstl_cd AS Orig_Recp_Postal,
CASE 
 WHEN recp_origl_st_cd = '' THEN ''
 ELSE 'US'
END AS Orig_Recp_Country,
CAST(CASE
 WHEN bill.cdo_ind_cd IN ('I','O') THEN 0 
 ELSE bill.grnd_actl_wgt 
END AS DECIMAL(8,1)) AS Lbs_Orig_Wgt,
CAST(CASE
 WHEN bill.cdo_ind_cd IN ('I','O') THEN 0 
 ELSE bill.shp_rate_wgt 
END AS DECIMAL(8,1)) AS Lbs_Rated_Wgt,
CAST(Lbs_Orig_Wgt * 0.4536 AS DECIMAL(8,1)) AS Kgs_Orig_Wgt,
CAST(Lbs_Rated_Wgt * 0.4536 AS DECIMAL(8,1)) AS Kgs_Rated_Wgt,
CASE
 WHEN ZEROIFNULL(dim_vol_qty) = 0 THEN 'NA'
 ELSE TRIM(pkg_dim_length_nbr)||' x '||TRIM(pkg_dim_width_nbr)||' x '||TRIM(pkg_dim_height_nbr)
END AS Dimensions,
CAST(rc.misc_resn_desc AS VARCHAR(100)) AS surch_desc, 
'USA' AS Payer_Country,
'USD' AS Original_Currency,
m.misc_amt AS misc_amt_usd,
1 AS Occurrences

FROM UI_ISH_PROD_DB.customer cust,
UI_ISH_PROD_DB.billed_dom_ground bill 

INNER JOIN UI_ISH_PROD_DB.dom_inv_misc m 
ON bill.INV_UNIQUE_ID = m.inv_unique_id 
AND bill.AB_SEQ_NBR = m.ab_seq_nbr 
LEFT OUTER JOIN ui_ish_prod_db.miscellaneous_reason_code rc   
ON m.misc_resn_cd = rc.misc_resn_cd 
WHERE bill.ab_inv_typ_ind NOT IN ('0') 
AND m.misc_amt >= 0  
AND surch_desc NOT IN ('?','Weekday Delivery', 'Week Day Delivery')
AND cust.cust_nbr = payr_cust_nbr  AND  Inv_yyyymm BETWEEN 201812 and 201812  AND cust.natl_acct_nbr in (0) UNION ALL

SELECT 
CAST(cust.natl_acct_nbr AS VARCHAR(10)) AS National, 
cust.natl_acct_sgrp_nbr AS Subgroup, 
CAST(cust.cust_nbr AS VARCHAR(10)) AS Nine_Digit,
bill.inv_nbr AS Invoice_Number,
CAST('' AS VARCHAR(20)) AS Tracking_Prefix,
bill.shp_trk_nbr AS Tracking_Number,
'Express' AS OpCo,
'Domestic' AS Dom_Intl,
CASE
 WHEN bill.svc_bas_cd = '01' THEN 'Priority Overnight'
 WHEN bill.svc_bas_cd = '05' THEN 'Standard Overnight'
 WHEN bill.svc_bas_cd IN ('03', '04') THEN '2Day'
 WHEN bill.svc_bas_cd = '49' THEN '2Day A.M.'
 WHEN bill.svc_bas_cd = '39' THEN 'First Overnight Freight'
 WHEN bill.svc_bas_cd = '06' THEN 'First Overnight'
 WHEN bill.svc_bas_cd = '70' THEN '1Day Freight'
 WHEN bill.svc_bas_cd IN ( '80', '86')  THEN '2Day Freight'
 WHEN bill.svc_bas_cd = '10' THEN 'Airport to Airport'
 WHEN bill.svc_bas_cd = '20' THEN 'Express Saver'
 WHEN bill.svc_bas_cd = '47' THEN 'Same Day'
 WHEN bill.svc_bas_cd = '83' THEN '3Day Freight'
 ELSE  'Other '|| bill.svc_bas_cd
END AS Product_Name, 
bill.pkg_typ_cd AS Package_Type,
CAST(SUBSTR(bill.inv_dt_yyyymm,1,4) AS INTEGER) AS Inv_Year,
CAST(bill.inv_dt_yyyymm AS INTEGER) AS inv_yyyymm,
CAST(SUBSTR(inv_dt,7,4)||'-'||SUBSTR(inv_dt,1,2)||'-01' AS DATE) AS Inv_Month,
CAST(SUBSTR(shp_dt,7,4) AS INTEGER) AS Ship_Year,
CAST(SUBSTR(shp_dt,7,4)||SUBSTR(shp_dt,1,2) AS INTEGER) AS Ship_yyyymm,
CAST(SUBSTR(shp_dt,7,4)||'-'||SUBSTR(shp_dt,1,2)||'-01' AS DATE) AS Ship_Month,
shp_dt,
cust.cust_nm AS Payer_Company,
payr_cust_nbr AS Payer_Account_Number,
shp_co_nm AS Ship_Company,
shp_nm AS Ship_Name,
shp_addr AS Ship_Address,
shp_cty AS Ship_City,
shp_st_cd AS Ship_State,
shp_pstl_cd AS Ship_Postal,
'US' AS Ship_Country,
shp_pstl_cd AS Actual_Orig_Zip,
shp_cust_nbr AS Ship_Account_Number,
recp_co_nm AS Recp_Company,
recp_nm AS Recp_Name,
recp_addr AS Recp_Address,
recp_cty AS Recp_City,
recp_st_cd AS Recp_State,
recp_pstl_cd AS Recp_Postal,
'US' AS Recp_Country,
recp_origl_addr AS Orig_Recp_Address,
recp_origl_cty AS Orig_Recp_City,
recp_origl_st_cd AS Orig_Recp_State,
recp_origl_pstl_cd AS Orig_Recp_Postal,
CASE 
 WHEN recp_origl_st_cd = '' THEN ''
 ELSE 'US'
END AS Orig_Recp_Country,
CAST(CASE
  WHEN bill.cdo_ind_cd IN ('I','O') THEN 0.00
  ELSE bill.orig_wgt 
END AS DECIMAL(8,1)) AS Lbs_Orig_Wgt,
CAST(CASE
  WHEN bill.cdo_ind_cd IN ('I','O') THEN 0.00 
  ELSE bill.shp_rate_wgt 
END AS DECIMAL(8,1)) AS Lbs_Rated_Wgt,
CAST(Lbs_Orig_Wgt * 0.4536 AS DECIMAL(8,1)) AS Kgs_Orig_Wgt,
CAST(Lbs_Rated_Wgt * 0.4536 AS DECIMAL(8,1)) AS Kgs_Rated_Wgt,
CASE
 WHEN ZEROIFNULL(dim_dimension) = 0 THEN 'NA'
 ELSE TRIM(TRIM(LEADING '0' FROM (Substr(dim_dimension, 3, 3))))||' x '||TRIM(TRIM(LEADING '0' FROM (Substr(dim_dimension, 6, 3))))||' x '||TRIM(TRIM(LEADING '0' FROM (Substr (dim_dimension, 9, 3))))
END AS Dimensions,
CAST(rc.misc_resn_desc AS VARCHAR(100)) AS surch_desc, 
'USA' AS Payer_Country,
'USD' AS Original_Currency,
dm.misc_amt AS misc_amt_usd,
1 AS Occurrences

FROM  UI_ISH_PROD_DB.customer cust, 
UI_ISH_PROD_DB.billed_dom_express bill 

INNER JOIN UI_ISH_PROD_DB.dom_inv_misc dm 
ON bill.INV_UNIQUE_ID = dm.inv_unique_id 
AND    bill.AB_SEQ_NBR = dm.ab_seq_nbr 

LEFT OUTER JOIN ui_ish_prod_db.miscellaneous_reason_code rc 
ON dm.misc_resn_cd = rc.misc_resn_cd 

WHERE bill.ab_inv_typ_ind NOT IN ('0') 
AND dm.misc_amt >= 0 
AND dm.misc_resn_cd NOT IN ('1','16','58','59','61','64','72','73','74','77') 
AND surch_desc NOT IN ('?','Weekday Delivery', 'Week Day Delivery')
AND cust.cust_nbr = payr_cust_nbr  AND  Inv_yyyymm BETWEEN 201812 and 201812  AND cust.natl_acct_nbr in (0) UNION ALL

SELECT           
CAST(cust.natl_acct_nbr AS VARCHAR(10)) AS National, 
cust.natl_acct_sgrp_nbr AS Subgroup, 
CAST(cust.cust_nbr AS VARCHAR(10)) AS Nine_Digit,
NULL AS Invoice_Number,
CAST('' AS VARCHAR(20)) AS Tracking_Prefix,
'' AS Tracking_Number,
'Express' AS OpCo,
'Domestic' AS Dom_Intl,
'Non-Transportation Related' AS Product_Name, 
'NA'  AS Package_Type,
CAST(SUBSTR(bill.inv_dt_yyyymm,1,4) AS INTEGER) AS Inv_Year,
CAST(bill.inv_dt_yyyymm AS INTEGER) AS inv_yyyymm,
CAST(SUBSTR(inv_dt,7,4)||'-'||SUBSTR(inv_dt,1,2)||'-01' AS DATE) AS Inv_Month,
CAST(SUBSTR(bill.non_trans_txn_dt,7,4) AS INTEGER) AS Ship_Year,
CAST(SUBSTR(bill.non_trans_txn_dt,7,4)||SUBSTR(bill.non_trans_txn_dt,1,2) AS INTEGER) AS Ship_yyyymm,
CAST(SUBSTR(bill.non_trans_txn_dt,7,4)||'-'||SUBSTR(bill.non_trans_txn_dt,1,2)||'-01' AS DATE) AS Ship_Month,
bill.non_trans_txn_dt,
cust.cust_nm AS Payer_Company,
payr_cust_nbr AS Payer_Account_Number,
'NA' AS Ship_Company,
'NA' AS Ship_Name,
'NA' AS Ship_Address,
'NA' AS Ship_City,
'NA' AS Ship_State,
'NA' AS Ship_Postal,
'NA' AS Ship_Country,
'NA' AS Actual_Orig_Zip,
NULL AS Ship_Account_Number,
'NA' AS Recp_Company,
'NA' AS Recp_Name,
'NA' AS Recp_Address,
'NA' AS Recp_City,
'NA' AS Recp_State,
'NA' AS Recp_Postal,
'NA' AS Recp_Country,
'NA' AS Orig_Recp_Address,
'NA' AS Orig_Recp_City,
'NA' AS Orig_Recp_State,
'NA' AS Orig_Recp_Postal,
'NA' AS Orig_Recp_Country,
NULL AS Lbs_Orig_Wgt,
NULL AS Lbs_Rated_Wgt,
NULL AS Kgs_Orig_Wgt,
NULL AS Kgs_Rated_Wgt,
'' AS Dimensions,
CAST(ct2.chrg_type_desc AS CHAR(100)) AS surch_desc, 
'USA' AS Payer_Country,
'USD' AS Original_Currency,
nc.chrg_amt AS misc_amt_usd,
1 AS Occurrences

FROM  UI_ISH_PROD_DB.customer cust,
ui_ish_prod_db.billed_dom_non_trans bill 

LEFT OUTER JOIN ui_ish_prod_db.billed_dom_non_trans_chrg nc 
ON bill.inv_unique_id = nc.inv_unique_id 
AND bill.ab_seq_nbr = nc.ab_seq_nbr 

LEFT OUTER JOIN ui_ish_prod_db.Charge_type_code ct2 
ON nc.chrg_type_cd = ct2.chrg_type_cd 

WHERE cust.natl_acct_nbr <> 0
AND surch_desc NOT IN ('?','Weekday Delivery', 'Week Day Delivery')
AND cust.cust_nbr = payr_cust_nbr  AND  Inv_yyyymm BETWEEN 201812 and 201812  AND cust.natl_acct_nbr in (0)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Add one more line to your query:

) x

Author

Commented:
YES!! THANK YOU!!!!!
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
PLEASE do NOT mix old-fashioned join syntax with more recent join syntax

FROM UI_ISH_PROD_DB.customer cust
  , ui_ish_prod_db.billed_dom_non_trans bill
--<<< this is "old-fashioned"
 LEFT JOIN ui_ish_prod_db.billed_dom_non_trans_chrg nc ON bill.inv_unique_id = nc.inv_unique_id
  AND bill.ab_seq_nbr = nc.ab_seq_nbr
 LEFT JOIN ui_ish_prod_db.Charge_type_code ct2 ON nc.chrg_type_cd = ct2.chrg_type_cd
 WHERE cust.natl_acct_nbr <> 0
  AND surch_desc NOT IN ('?', 'Weekday Delivery', 'Week Day Delivery')
 AND cust.cust_nbr = payr_cust_nbr --<< is this the join from "cust" to "bill"?
  AND Inv_yyyymm BETWEEN 201812 AND 201812
  AND cust.natl_acct_nbr IN (0)

The joins where you use LEFT JOIN have a higher precedence over the old-fashioned approach and this can lead to problems that are difficult to de-bug.

To avoid this... be consistent and do not use commas between table names in any from clause.

FROM UI_ISH_PROD_DB.customer cust
  INNER JOIN ui_ish_prod_db.billed_dom_non_trans bill ON  cust.cust_nbr = bill.payr_cust_nbr
--<< is this the join from "cust" to "bill"?
 LEFT JOIN ui_ish_prod_db.billed_dom_non_trans_chrg nc ON bill.inv_unique_id = nc.inv_unique_id
  AND bill.ab_seq_nbr = nc.ab_seq_nbr
 LEFT JOIN ui_ish_prod_db.Charge_type_code ct2 ON nc.chrg_type_cd = ct2.chrg_type_cd
 WHERE cust.natl_acct_nbr <> 0
  AND cust.natl_acct_nbr IN (0)

  AND surch_desc NOT IN ('?', 'Weekday Delivery', 'Week Day Delivery') --<< what table does surch_desc come from?
  AND Inv_yyyymm BETWEEN 201812 AND 201812 --<< what table does Inv_yyyymm come from?

and always use table aliases too :)
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
tip: be careful of NOT (...) it does not handle NULL values

e.g. Only the second of these 2 queries returns any rows:
CREATE TABLE mytable(
   id         INTEGER  NOT NULL PRIMARY KEY 
  ,surch_desc VARCHAR(18)
);
INSERT INTO mytable(id,surch_desc) VALUES (1,'?');
INSERT INTO mytable(id,surch_desc) VALUES (2,'Weekday Delivery');
INSERT INTO mytable(id,surch_desc) VALUES (3,'Week Day Delivery');
INSERT INTO mytable(id,surch_desc) VALUES (4,NULL);


select
     'not in' as qry, *
from mytable
where surch_desc NOT IN ('?', 'Weekday Delivery', 'Week Day Delivery')
;


select
     'and is null' as qry, *
from mytable t
where (surch_desc NOT IN ('?', 'Weekday Delivery', 'Week Day Delivery') or surch_desc IS NULL)
;

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial