Here is the situation:
We recently upgrade to 2005(sp). We have one report that ran fine in 2000 but leaves out data from certain columns (date related) in the results, so we chalked it up to being a non compatiable issue. So, I decided to try and switch the DB back to 2000 compatibility (in our test env) and then back to 2005. After that the report started returning the proper data. We can’t really explain why it worked but it did. So we thought we would try it in prod (we knew it was a long shot) and it didn’t work. So the business needs this report so we thought we would refresh the test system from prod, but now we are back to square one. I was wondering if anyone else has heard or seen anything like this. I am open to any idea’s, no matter how crazy. J The systems are configured identically. Let me know if you need more information.
Thank you.
ScottIm very curious about this situation. Can you post the query that you are running?|||It is very long but here it is...
select data.shipment,
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
We solved the issue by making the following change. I am wondering if SS 2005 handles null values differently.
Before:
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
After:
union all-- DCR start
select id_ta,
--name,
convert(datetime,'12/30/1899',101) crd_actual_date,
convert(datetime,'12/30/1899',101) brd_actual_date,
convert(datetime,'12/30/1899',101) cls_actual_date,
convert(datetime,'12/30/1899',101) dep_actual_date,
convert(datetime,'12/30/1899',101) arv_actual_date,
convert(datetime,'12/30/1899',101) dlv_actual_date,
convert(datetime,'12/30/1899',101) rcv_actual_date,
convert(datetime,'12/30/1899',101) idc_actual_date,
convert(datetime,'12/30/1899',101) bkd_actual_date,
convert(datetime,'12/30/1899',101) aci_actual_date,
convert(datetime,'12/30/1899',101) dlt_actual_date,
convert(datetime,'12/30/1899',101) lot_actual_date,
dcr_actual_date =
CASE year(act_finish)
WHEN 1899 THEN convert(datetime,'12/30/1899',101) WHEN 1900 THEN convert(datetime,'12/30/1899',101)
ELSE act_finish
END,
convert(datetime,'12/30/1899',101) ccd_actual_date
from tacalendar where name = 'DCR - DOCUMENTS RECEIVED FOR CLEARANCE'
-- DCR end|||
I can't see where exactly the problem is by looking at the work workaround that you implemented (at least with a first glance). Is it possible to reduce the query to a small statement that shows that NULLs are treated differently between the two versions of SQL Server ? That will help us investigate the problem.
Thanks a lot,
Leo
No comments:
Post a Comment