The following is my query, I am trying to convert from Oracle to SQL and I have hit a roadblock with this one. The line this is referring to is a datetime2 data type so I am unclear why there is an issue. Any help will be appreciated, sql below:
select j."LODDTE" as "Date Loaded",
j."Part Cat" as "Part Cat1",
sum(j."shpqty") as "Loaded Qty", j."WH ID"
from (SELECT ord.ordnum,
ord_line.ordlin,
ord_line.vc_var_fld8,
shipment.alcdte as Allocated,
ord_line.vc_var_fld18,
ord_line.prtnum,
substring(prtmst.prtfam, 1, 2) as "Part_Family",
CASE prtmst.prtfam
WHEN 'S1' THEN 'SRT'
WHEN 'S2' THEN 'SRT'
WHEN 'AA' THEN 'CML ACC'
WHEN 'CC' THEN 'CML ACC'
WHEN 'RA' THEN 'CML ACC'
WHEN 'SA' THEN 'CML ACC'
WHEN 'NS' THEN 'CML ACC'
WHEN 'AY' THEN 'CML ACC'
WHEN 'SP' THEN 'SPP'
WHEN 'SP3' THEN 'SPP'
WHEN 'SPB' THEN 'SPP'
WHEN 'SPMTO' THEN 'SPP'
WHEN 'M1' THEN 'MRT'
WHEN 'IS' THEN 'MRT'
WHEN 'HT' THEN 'RES ACCY'
WHEN 'PA' THEN 'RES ACCY'
WHEN 'AP' THEN 'RES ACCY'
WHEN 'CA' THEN 'RES ACCY'
WHEN 'SS' THEN 'DLS'
WHEN 'SM' THEN 'DLS'
WHEN 'FS' THEN 'DLS'
WHEN 'FM' THEN 'DLS'
WHEN 'AQ' THEN 'Acc'
WHEN 'CA' THEN 'Acc'
WHEN 'DH' THEN 'Acc'
WHEN 'DS' THEN 'Acc'
WHEN 'GY' THEN 'Acc'
WHEN 'HM' THEN 'Acc'
WHEN 'ME' THEN 'Acc'
WHEN 'MF' THEN 'Acc'
WHEN 'UV' THEN 'Acc'
WHEN 'VN' THEN 'Acc'
WHEN 'SC' THEN 'Acc'
WHEN 'FV' THEN 'VRF'
WHEN 'SV' THEN 'VRF'
WHEN 'FC' THEN 'CML FAN COIL'
WHEN 'T1' THEN 'CML SPLIT'
WHEN 'TS' THEN 'ECO ACC'
WHEN 'FN' THEN 'RES COIL'
WHEN 'FR' THEN 'FAN COIL'
WHEN 'SR' THEN 'RES SPLIT'
WHEN 'DA' THEN 'DLS ACC'
WHEN 'VR' THEN 'VRF ACC'
WHEN 'LT' THEN 'LOGO'
WHEN 'GH' THEN 'Geo'
END AS "Part Cat",
ord_line.ordqty,
sum(shipment_line.shpqty) as "shpqty",
min(ord_line.vc_var_fld12) as "Division",
ord.adddte as "Ord Date",
min(ord_line.early_shpdte) as "CRSD",
min(ord_line.late_shpdte) as "TCPSD",
min(ord_line.late_dlvdte) as "CPSD",
ord.vc_var_fld2,
ord.vc_var_fld5,
shipment.ship_id,
shipment.super_ship_id,
shipment.adddte as "Date Added",
car_move.car_move_id,
shipment.track_num,
stop.stop_nam,
Substring(car_move.vc_var_fld3, 5, 2) + '-' + Substring(car_move.vc_var_fld3, 7, 2) + '-' + Substring(car_move.vc_var_fld3, 1, 4) PickUp_date,
Substring(car_move.vc_var_fld1, 5, 2) + '-' + Substring(car_move.vc_var_fld1, 7, 2) + '-' + Substring(car_move.vc_var_fld1, 1, 4) Finalize_Date,
shipment.shpsts,
shipment.srvlvl as "service level",
shipment.carcod,
shipment.VC_SHIPMENT_VFLD3,
trlr.trlr_typ,
trlr.trlr_num,
shipment.stgdte,
shipment.loddte AS "LODDTE",
trlr.close_dte,
trlr.dispatch_dte AS "Dispatch Date",
a2.adrnam Bill_To_Name,
a2.CONT_NAME Sold_To_Number,
ord.vc_var_fld4 CSR_Name,
ord_line.vc_var_fld1,
adrmst.adrnam,
adrmst.adrln1,
adrmst.adrcty,
adrmst.adrstc,
adrmst.adrpsz,
adrmst.ctry_name,
ord.wh_id as "WH ID"
FROM R09CABPRD.ord,
R09CABPRD.ord_line,
R09CABPRD.prtmst,
R09CABPRD.adrmst,
R09CABPRD.adrmst a2,
R09CABPRD.shipment_line,
R09CABPRD.shipment,
R09CABPRD.stop,
R09CABPRD.car_move,
R09CABPRD.trlr
WHERE ord.wh_id = '4300'
and ord.wh_id = ord_line.wh_id
AND ord.client_id = ord_line.client_id
AND ord_line.prtnum = prtmst.prtnum
AND ord.ordnum = ord_line.ordnum
AND ord.rt_adr_id = adrmst.adr_id
AND ord.bt_adr_id = a2.adr_id
AND ord_line.wh_id = shipment_line.wh_id
AND ord_line.client_id = shipment_line.client_id
AND ord_line.ordnum = shipment_line.ordnum
AND ord_line.ordlin = shipment_line.ordlin
AND ord_line.ordsln = shipment_line.ordsln
AND shipment_line.wh_id = shipment.wh_id
AND shipment_line.ship_id = shipment.ship_id
AND shipment.stop_id = stop.stop_id
AND stop.car_move_id = car_move.car_move_id
AND car_move.trlr_id = trlr.trlr_id
and prtmst.prtfam not in ('AA', 'CC', 'RA', 'SA', 'AY', 'HT', 'PA', 'AP', 'DA', 'VR', 'LT', 'TS', 'AP', 'PA', 'AQ', 'CA', 'DH', 'DS', 'GY', 'HM', 'ME', 'MF', 'UV', 'VN', 'SC')
AND adrmst.adrcty not in ('DO NOT SHIP', 'BILLING ONLY', 'BILLING PURPOSE ONLY')
AND adrmst.adrnam not in ('BILLING ONLY DO NOT SHIP', 'Billing only', '**Billing Only DT***', '**BILLING ONLY**DO NOT SHIP**', 'BILLING ONLY - DO NOT SHIP', 'Billing Only - Do not ship', 'DO NOT SHIP - CLAIM 02421929 OVR', 'DO NOT SHIP CLAIM_02442085_OVR', 'billing only do not ship', 'Billing Only')
AND (trlr.dispatch_dte > convert(datetime, '7/29/2020 00:00:00', 126))
GROUP BY ord.ordnum,
ord.adddte,
ord.vc_var_fld2,
ord.vc_var_fld5,
shipment.ship_id,
shipment.super_ship_id,
shipment.track_num,
car_move.car_move_id,
stop.stop_nam,
car_move.vc_var_fld3,
car_move.vc_var_fld1,
shipment.adddte,
shipment.loddte,
trlr.close_dte,
shipment.stgdte,
shipment.carcod,
shipment.shpsts,
shipment.srvlvl,
ord_line.ordlin,
ord_line.vc_var_fld8,
shipment.alcdte,
ord_line.vc_var_fld18,
ord_line.prtnum,
substring(prtmst.prtfam, 1, 2),
prtmst.prtfam,
ord_line.ordqty,
trlr.trlr_typ,
trlr.trlr_num,
trlr.dispatch_dte,
a2.adrnam,
a2.CONT_NAME,
ord.vc_var_fld4,
ord_line.vc_var_fld1,
adrmst.adrnam,
adrmst.adrln1,
adrmst.adrcty,
adrmst.adrstc,
adrmst.adrpsz,
adrmst.ctry_name,
ord.rush_flg,
stop.track_num,
shipment.VC_SHIPMENT_VFLD3,
ord.wh_id) j
group by j."LODDTE",
j."Part Cat",
j."WH ID"
order by j."LODDTE" asc
TIA Lisa
