Access to Oracle SQL conversion

[zedd]

Hi guys.. new guy in the house. Need some help with my Query

I converted some Access query to Oracle, but I'm not getting the same number of records. Below are the 2 queries :

Oracle SQL

SELECT VES.SHPG_SVC_CD, BERTHING.ATB_DTTM, VES.VSL_NM, VES.OUT_VOY_NBR, CNTR.LOAD_SLOT_OPR_CD, CNTR.DISC_SLOT_OPR_CD, CNTR.CNTR_OPR_CD, CNTR.PLOAD, CNTR.PDISC1, CNTR.PDEST, CNTR.SIZE_FT, CNTR.CAT_CD, CNTR.STATUS, NOM.VSL_NM, NOM.OUT_VOY_NBR
FROM TOPS.VESSEL_CALL VES, TOPS.BERTHING BERTHING, TOPS.CNTR CNTR, TOPS.NOMINATED_VSL NOM
WHERE ((VES.VV_CD = BERTHING.VV_CD) AND (VES.VV_CD = CNTR.DISC_VV_CD)
AND (CNTR.NOM_LOAD_VV_CD = NOM.NOM_VV_CD(+)) AND (CNTR.LOAD_VV_CD = VES.VV_CD(+)))
AND (TO_CHAR(BERTHING.ATB_DTTM, 'MM/DD/YYYY') BETWEEN '10/01/2003' AND '11/30/2003')
AND (TO_CHAR(BERTHING.ATB_DTTM, 'YYYY') BETWEEN '2003' AND '2003')
AND (CNTR.PURP_CD = 'TS')


Access SQL

SELECT TOPS_VESSEL_CALL.SHPG_SVC_CD, TOPS_BERTHING.ATB_DTTM, TOPS_VESSEL_CALL.VSL_NM, TOPS_VESSEL_CALL.OUT_VOY_NBR, TOPS_CNTR.LOAD_SLOT_OPR_CD, TOPS_CNTR.DISC_SLOT_OPR_CD, TOPS_CNTR.CNTR_OPR_CD, TOPS_CNTR.PLOAD, TOPS_CNTR.PDISC1, TOPS_CNTR.PDEST, TOPS_CNTR.SIZE_FT, TOPS_CNTR.CAT_CD, TOPS_CNTR.STATUS, TOPS_NOMINATED_VSL.VSL_NM, TOPS_NOMINATED_VSL.OUT_VOY_NBR
FROM (((TOPS_VESSEL_CALL INNER JOIN TOPS_BERTHING ON TOPS_VESSEL_CALL.VV_CD = TOPS_BERTHING.VV_CD)
INNER JOIN TOPS_CNTR ON TOPS_VESSEL_CALL.VV_CD = TOPS_CNTR.DISC_VV_CD)
LEFT JOIN TOPS_NOMINATED_VSL ON TOPS_CNTR.NOM_LOAD_VV_CD = TOPS_NOMINATED_VSL.NOM_VV_CD)
LEFT JOIN TOPS_VESSEL_CALL AS TOPS_VESSEL_CALL_1 ON TOPS_CNTR.LOAD_VV_CD = TOPS_VESSEL_CALL_1.VV_CD
WHERE ((TOPS_BERTHING.ATB_DTTM) Between CDate([Enter From Date mm/dd/yyyy:]) And CDate([Enter To Date mm/dd/yyyy:]))
AND ((TOPS_CNTR.PURP_CD)="TS"))


Thanks in advance

[slimdave]

AND (TO_CHAR(BERTHING.ATB_DTTM, 'MM/DD/YYYY') BETWEEN '10/01/2003' AND '11/30/2003')

This is bad code ... you are comparing strings, not dates, and will not do what you think it will.

Better practice would be ...

AND BERTHING.ATB_DTTM BETWEEN TO_DATE('10/01/2003','MM/DD/YYYY') AND TO_DATE('11/30/2003','MM/DD/YYYY')

[zedd]

Originally posted by slimdave

AND (TO_CHAR(BERTHING.ATB_DTTM, 'MM/DD/YYYY') BETWEEN '10/01/2003' AND '11/30/2003')

This is bad code ... you are comparing strings, not dates, and will not do what you think it will.

Better practice would be ...

AND BERTHING.ATB_DTTM BETWEEN TO_DATE('10/01/2003','MM/DD/YYYY') AND TO_DATE('11/30/2003','MM/DD/YYYY')

Actually, I did have that. Problem is, for some reason, it misses the end of the month. Eg, the 30th Nov. I'll get records until the 29th.

[jmodic]

Originally posted by zedd
Actually, I did have that. Problem is, for some reason, it misses the end of the month. Eg, the 30th Nov. I'll get records until the 29th.

That's because the following code

AND BERTHING.ATB_DTTM BETWEEN TO_DATE('10/01/2003','MM/DD/YYYY')
                          AND TO_DATE('11/30/2003','MM/DD/YYYY')

returns true only for ATB_DTTM dates that have no time portion on the last day of November. In other words, for the last day of November it will be true only for those records that have ATB_DTM exatly at midnight between 29th and 30th of November, ie TO_DATE('11/30/2003 00:00:00','MM/DD/YYYY HH24:MI:SS'). Apparently all your ATB_DTTM values on 11/30/2003 have time portion greaqter than '00:00:00', that's why they were not included in the resultset.

To fix this, you have many options, for example:

1. (this option will prevent the usage of the index on ATB_DTTM if it exists, so might not be the best choice)

AND TRUNC(BERTHING.ATB_DTTM)
  BETWEEN TO_DATE('10/01/2003','MM/DD/YYYY')
      AND TO_DATE('11/30/2003','MM/DD/YYYY')

2.

AND BERTHING.ATB_DTTM
  BETWEEN TO_DATE('10/01/2003','MM/DD/YYYY')
      AND TO_DATE('11/30/2003 23:59:59','MM/DD/YYYY HH24:MI:SS')

3.

AND BERTHING.ATB_DTTM >= TO_DATE('10/01/2003','MM/DD/YYYY')
AND BERTHING.ATB_DTTM < TO_DATE('12/01/2003','MM/DD/YYYY')

etc etc

[zedd]

Thanks for the advice.. I'll probably use those for my future reports cause I have to restart from scratch again cause Crystal report doesnt' allow changing of SQL for cross tab reports.

Anyways, back to the original question.. The queries returned aren't the same. What I suspect is not because of the dates, but the way the tables are joined. Are they correct for the Oracle version in comparison with the Access?

[zedd]

Messed around with the SQL last night .. still no go.

Oracle SQL


SELECT *
FROM tops.berthing berthing, tops.vessel_call ves, tops.cntr cntr, tops.nominated_vsl nom, tops.vessel_call vessel_call_1
WHERE ((berthing.vv_cd = ves.vv_cd)
AND (ves.vv_cd = cntr.disc_vv_cd)
AND (nom.nom_vv_cd = cntr.nom_load_vv_cd)
AND (vessel_call_1.vv_cd = cntr.load_vv_cd))


Access SQL


SELECT *
FROM (((TOPS_VESSEL_CALL INNER JOIN TOPS_BERTHING ON TOPS_VESSEL_CALL.VV_CD = TOPS_BERTHING.VV_CD)
INNER JOIN TOPS_CNTR ON TOPS_VESSEL_CALL.VV_CD = TOPS_CNTR.DISC_VV_CD)
LEFT JOIN TOPS_NOMINATED_VSL ON TOPS_CNTR.NOM_LOAD_VV_CD = TOPS_NOMINATED_VSL.NOM_VV_CD)
LEFT JOIN TOPS_VESSEL_CALL AS TOPS_VESSEL_CALL_1 ON TOPS_CNTR.LOAD_VV_CD = TOPS_VESSEL_CALL_1.VV_CD


How exactly do I correct the left and inner joins from Access to Oracle? Oh ya, I'm on 8i version if that matters Thanks