Query syntax error

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

Euro5Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
Add one more line to your query:

) x

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Euro5Author Commented:
YES!! THANK YOU!!!!!
PortletPaulEE Topic AdvisorCommented:
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 AdvisorCommented:
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.