Access to Oracle SQL conversion
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Access to Oracle SQL conversion

  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Access to Oracle SQL conversion

    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
    Last edited by zedd; 01-05-2004 at 11:21 PM.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Code:
    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 ...

    Code:
    AND BERTHING.ATB_DTTM BETWEEN TO_DATE('10/01/2003','MM/DD/YYYY') AND TO_DATE('11/30/2003','MM/DD/YYYY')
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jan 2004
    Posts
    4
    Originally posted by slimdave
    Code:
    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 ...

    Code:
    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.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
    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)
    Code:
    AND TRUNC(BERTHING.ATB_DTTM)
      BETWEEN TO_DATE('10/01/2003','MM/DD/YYYY')
          AND TO_DATE('11/30/2003','MM/DD/YYYY')
    2.
    Code:
    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.
    Code:
    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
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jan 2004
    Posts
    4
    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?
    Last edited by zedd; 01-06-2004 at 08:57 PM.

  6. #6
    Join Date
    Jan 2004
    Posts
    4
    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
    Last edited by zedd; 01-06-2004 at 08:58 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width