Query worked in SQL 2000 but not in 2005
-
The query below was working fine until we upgraded to SQL 2005. It still runs, but now I am getting a '---' in each of the actual_date fields whether there is a date or not. The odd thing is it works fine for individual shipment records. For example, if I add a 'and shipment = 38565' line after the and data.style = p.prodcode line at the end, I get correct results. Unfortunately I need to return all shipments, not just one.
Anyone have any ideas what as changed between 2000 and 2005 that would cause this? I've broken this thing apart and can't seem to pinpoint what's happening.
Thanks, -T
select data.shipment,
cal.name,
po_line,
data.style,
p.prodcat1 as omnia_sbu,
Left(p.prodcat1, 3) as sbu_mstr,
isnull(CONVERT(CHAR(10), OrigCRD, 101),'---') OrigCRD,
isnull(CONVERT(CHAR(10), CRD, 101),'---') CRD,
isnull(OrigNDC, '') OrigNDC,
isnull(CONVERT(CHAR(10), NDC, 101),'---') NDC,
isnull(OrigLogP, '---') OrigLogP,
isnull(LogP,'---') LogP,
isnull(po_mode, '---') po_mode,
isnull(data.agent, '---') agent,
isnull(rtrim(agent),'---') + isnull(ship_mode,'---') agent_key,
isnull(left(agent, 2),'---') agent_mstr,
isnull(left(agent, 2),'---') + isnull(ship_mode,'---') agent_mstr_key,
isnull(data.factory_id, '---') factory_id,
isnull(data.factory, '---') factory,
isnull(rtrim(data.factory), '---') + isnull(ship_mode,'---') as fact_key,
isnull(data.broker, '---') broker,
isnull(left(broker, 3), '---') broker_mstr,
isnull(template, '---') ship_cal,
isnull( ship_mode, '---') ship_mode,
isnull(CONVERT(CHAR(10), brd_actual_date, 101),'---') brd_actual_date,
isnull(CONVERT(CHAR(10), crd_actual_date, 101),'---') crd_actual_date,
isnull(CONVERT(CHAR(10), cls_actual_date, 101),'---') cls_actual_date,
isnull(CONVERT(CHAR(10), dep_actual_date, 101),'---') dep_actual_date,
isnull(CONVERT(CHAR(10), arv_actual_date, 101),'---') arv_actual_date,
isnull(CONVERT(CHAR(10), dlv_actual_date, 101),'---') dlv_actual_date,
isnull(CONVERT(CHAR(10), rcv_actual_date, 101),'---') rcv_actual_date,
isnull(CONVERT(CHAR(10), idc_actual_date, 101),'---') idc_actual_date,
isnull(CONVERT(CHAR(10), bkd_actual_date, 101),'---') bkd_actual_date,
isnull(CONVERT(CHAR(10), aci_actual_date, 101),'---') aci_actual_date,
isnull(CONVERT(CHAR(10), dlt_actual_date, 101),'---') dlt_actual_date,
isnull(CONVERT(CHAR(10), lot_actual_date, 101),'---') lot_actual_date,
isnull(CONVERT(CHAR(10), dcr_actual_date, 101),'---') dcr_actual_date,
isnull(CONVERT(CHAR(10), ccd_actual_date, 101),'---') ccd_actual_date,
units ship_units
from
(select shipment.shipment, lot po_line, prbunhea.style, CASE ltrim(prbunhea.misc12)
WHEN '' THEN '---'
ELSE ltrim(prbunhea.misc12) END OrigCRD,
CASE ltrim(prbunhea.misc4)
WHEN '' THEN '---'
ELSE ltrim(prbunhea.misc4) END CRD,
ltrim(prbunhea.misc13) OrigNDC,
CASE ltrim(revdd)
WHEN '' THEN '---'
ELSE revdd END NDC,
CASE ltrim(orig_shipcal)
WHEN '' THEN '---'
ELSE ltrim(orig_shipcal) END OrigLogP,
CASE ltrim(prbunhea.defshipcal)
WHEN '' THEN '---'
ELSE ltrim(prbunhea.defshipcal) END LogP,
CASE ltrim(prbunhea.misc14)
WHEN '' THEN '---'
ELSE ltrim(prbunhea.misc14) END po_mode,
CASE ltrim(shipment.misc5)
WHEN '' THEN '---'
ELSE ltrim(shipment.misc5) END ship_mode,
prbunhea.rdacode agent,
shipment.broker,
prbunhea.rfactory factory_id,
ship_to_1 factory,
sum(shipped.unitship) units
from prbunhea, shipped, shipment, shshipto where
prbunhea.lot = shipped.cut
and shipment.shipment = shipped.shipment
and shshipto.factory = prbunhea.rfactory
group by shipment.shipment, lot, prbunhea.style,prbunhea.misc12,
prbunhea.misc4, prbunhea.misc13, shipment.misc5, revdd,
orig_shipcal, prbunhea.DefShipCal, prbunhea.misc14,
prbunhea.rdacode, shipment.broker, prbunhea.rfactory, ship_to_1
) data,
(select CAST(tam.name AS CHAR(20)) AS name,
tam.id_ta,
tam.template,
brd_actual_date,
crd_actual_date,
cls_actual_date,
dep_actual_date,
arv_actual_date,
dlv_actual_date,
rcv_actual_date,
idc_actual_date,
bkd_actual_date,
aci_actual_date,
dlt_actual_date,
lot_actual_date,
dcr_actual_date,
ccd_actual_date
from
(select * from tamaster where calendarmodule = 'Shipment') as tam left outer join
(select id_ta,
max(brd_actual_date) as brd_actual_date,
max(crd_actual_date) as crd_actual_date,
max(cls_actual_date) as cls_actual_date,
max(dep_actual_date) as dep_actual_date,
max(arv_actual_date) as arv_actual_date,
max(dlv_actual_date) as dlv_actual_date,
max(rcv_actual_date) as rcv_actual_date,
max(idc_actual_date) as idc_actual_date,
max(bkd_actual_date) as bkd_actual_date,
max(aci_actual_date) as aci_actual_date,
max(dlt_actual_date) as dlt_actual_date,
max(lot_actual_date) as lot_actual_date,
max(dcr_actual_date) as dcr_actual_date,
max(ccd_actual_date) as ccd_actual_date
from
(
-- CRD start
select id_ta,
--name,
crd_actual_date=
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as brd_actual_date ,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'CRD - CARGO READY DATE'
-- CRD end
union all
-- BRD start
select id_ta,
--name,
null as crd_actual_date,
brd_actual_date=
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null WHEN 1900 THEN null
ELSE act_finish
END ,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'BRD - BOOKING REQUEST DATE'
-- BRD end
union all
-- CLS start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
cls_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'CLS - SHIP CLOSE'
-- CLS end
union all
-- DEP start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
dep_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'DEP - CONFIRMED ON BOARD'
-- DEP end
union all
-- ARV start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
arv_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'ARV - ACTUAL DATE OF ARRIVAL'
-- ARV end
union all
-- DLV start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
dlv_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'DLV - CARGO RECEIVED AT DC (POD)'
-- DLV end
union all
-- RCV start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
rcv_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'RCV - RECEIVE DC'
-- RCV end
union all
-- IDC start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
idc_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'IDC - IN DISTRIBUTION CENTER'
-- IDC end
union all
-- BKD start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
bkd_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'BKD - BOOKING APPROVED'
-- BKD end
union all
-- ACI start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
aci_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'ACI - DATE ENTRY IS FILED WITH CUSTOMS'
-- ACI end
union all
-- DLT start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
dlt_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as lot_actual_date,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'DLT - ARRIVAL AT ATL RAIL / CY'
-- DLT end
union all
-- LOT start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
lot_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as dcr_actual_date,
null as ccd_actual_date
from tacalendar where name = 'LOT - LOADED ON TRUCK OR TRAIN'
-- LOT end
union all
-- DCR start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
dcr_actual_date =
CASE year(act_finish)
WHEN 1899 THEN null WHEN 1900 THEN null
ELSE act_finish
END,
null as ccd_actual_date
from tacalendar where name = 'DCR - DOCUMENTS RECEIVED FOR CLEARANCE'
-- DCR end
union all
-- CCD start
select id_ta,
--name,
null as crd_actual_date,
null as brd_actual_date,
null as cls_actual_date,
null as dep_actual_date,
null as arv_actual_date,
null as dlv_actual_date,
null as rcv_actual_date,
null as idc_actual_date,
null as bkd_actual_date,
null as aci_actual_date,
null as dlt_actual_date,
null as lot_actual_date,
null as dcr_actual_date,
ccd_actual_date =
CASE year(act_finish)
WHEN 1900 THEN null
WHEN 1899 THEN null
ELSE act_finish
END
from tacalendar where name = 'CCD - CUSTOMS CLEARED'
-- CCD end
) as cal group by id_ta ) as cal_temp
on tam.id_ta = cal_temp.id_ta) as cal, tmp_wcc_out_products p
where cast(data.shipment as char(20)) = cast(cal.name as char(20))
and data.style = p.prodcode
order by data.shipment, data.po_line
-
First thing I would do, rewrite the query for SQL Server 2005. I'd use CTE's to replace the derived tables in your query and I would use ANSI standard join syntax in the FROM clause(s) to help make clear the differences from the join criteria and the query criteria.
As written, I'd have to rewrite it to help you and I don't have the time right now.
And yes, I am currently experiencing a similiar issue with queries that ran faster in 2000 than 2005. Finding that adding some new indexes is helping our issue.
-
Thanks for the suggestion. Unfortunately I haven't written anything in 2005, so I wouldn't know where to start. One interesting thing I've found is that I can return up to 11 shipment records, but no more. If I go over, it no longer returns the dates.
-
Being at work right now, can't take the time. If I can, I will see what I can do from home if some else hasn't already jumped in to help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply