Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    16

    Unanswered: Access function to Oracle

    I'm converting some SQL statements that are currently used in Access to access data from an Oracle db.

    Anyone knows the correct SQL function to use?

    Access
    Code:
    where (Mid([tops_bill_item].[tariff_cd],7,2))="RF")
    I've tried using the substr function

    Code:
    where SUBSTR("BI"."TARIFF_CD",7,2) = "FT"
    but it doesn't seem to work.. Anyone able to assist me? Thanks in advance

  2. #2
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    HI,

    Try this

    where SUBSTR("BI"||"TARIFF_CD",7,2) = "FT"
    SATHISH .

  3. #3
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Access function to Oracle

    Try

    Code:
    where SUBSTR(BI.TARIFF_CD,7,2) = "FT"
    That should work so long as your tablename or table alias is BI, and the column is TARIFF.

  4. #4
    Join Date
    Nov 2003
    Posts
    16
    Got it.. Thanks guys.. Seems like TOAD recognized double quotes as references to columns..

    Code:
    AND SUBSTR(BI.TARIFF_CD,7,2) = 'RF'
    Works fine. Thanks again!

  5. #5
    Join Date
    Nov 2003
    Posts
    16
    One question .. Anything wrong with my Oracle SQL conversion from Access?

    Oracle :
    Code:
    SELECT VES.VSL_NM, VES.OUT_VOY_NBR, TO_CHAR(BAT.ATB_DTTM, 'MM/DD/YYYY') AS TEMPDATE, BIL.BILL_NBR, BIL.TXN_CD, 
    BIL.SLOT_OPR_CD, BIL.CNTR_OPR_CD, BIL.ITEM_NBR, BIL.TARIFF_CD, BIL.TARIFF_DESC, BIL.NBR_CNTR 
    
    FROM BILL BIL,BAT BAT, VESS VES 
    WHERE ((VES.VV_CD=BAT.VV_CD) AND (BIL.VV_CD=VES.VV_CD))
    AND (TO_CHAR(BAT.ATB_DTTM, 'MM/DD/YYYY') BETWEEN '{?StartDate}' AND '{?EnddDate}')
    AND (BIL.TARIFF_TYPE = 'P') 
    AND (BIL.TARIFF_MAIN_CAT_CD = 'SV') 
    AND (BIL.TOTAL_ITEM_AMT > 0) 
    AND (BIL.POST_IND <> 'E')
    
    AND (SUBSTR(BIL.TARIFF_CD,7,2)='RF' 
    OR SUBSTR(BIL.TARIFF_CD,7,2)='RH' 
    OR SUBSTR(BIL.TARIFF_CD,7,2)='RH' 
    OR SUBSTR(BIL.TARIFF_CD,7,2)='MR' 
    OR SUBSTR(BIL.TARIFF_CD,7,2)='HR' 
    OR SUBSTR(BIL.TARIFF_CD,7,2)='FT')
    
    ORDER BY 
    VES.VSL_NM,
    VES.OUT_VOY_NBR,
    BAT.ATB_DTTM,
    BIL.CUST_CD,
    BIL.SLOT_OPR_CD,
    BIL.CNTR_OPR_CD,
    BIL.TARIFF_SUB_CAT_CD,
    BIL.ITEM_NBR

    Generated by Access
    Code:
    SELECT VESS.VSL_NM, VESS.OUT_VOY_NBR, BAT.ATB_DTTM, BILL.BILL_NBR, BILL.TXN_CD, BILL.SLOT_OPR_CD, 
    BILL.CNTR_OPR_CD, BILL.ITEM_NBR, BILL.TARIFF_CD, BILL.TARIFF_DESC, BILL.NBR_CNTR
    
    FROM (BILL INNER JOIN VESS ON BILL.VV_CD = VESS.VV_CD) 
    INNER JOIN BAT ON VESS.VV_CD = BAT.VV_CD
    
    WHERE (((BAT.ATB_DTTM) Between CDate([Enter From Date mm/dd/yyyy:]) And CDate([Enter To Date mm/dd/yyyy:])) 
    AND ((BILL.TARIFF_TYPE)="P") 
    AND ((BILL.TARIFF_MAIN_CAT_CD)="SV") 
    AND ((BILL.TOTAL_ITEM_AMT)>0) 
    AND ((BILL.POST_IND)<>"E") 
    
    AND ( (Mid([BILL].[TARIFF_CD],7,2))="RF" 
    Or    (Mid([BILL].[TARIFF_CD],7,2))="RH" 
    Or    (Mid([BILL].[TARIFF_CD],7,2))="MR" 
    Or    (Mid([BILL].[TARIFF_CD],7,2))="HR" 
    Or    (Mid([BILL].[TARIFF_CD],7,2))="FT") )
    
    ORDER BY VESS.VSL_NM, 
    VESS.OUT_VOY_NBR, 
    BAT.ATB_DTTM, 
    BILL.CUST_CD, 
    BILL.SLOT_OPR_CD, 
    BILL.CNTR_OPR_CD, 
    BILL.TARIFF_SUB_CAT_CD, 
    BILL_.ITEM_NBR;
    The one from Access(original) returns only 2 records while oracle's returns 15.. Thanks

  6. #6
    Join Date
    Nov 2003
    Posts
    16
    Nvm.. Somehow the Oracle query retrieves 2002 entries for some reason.

Posting Permissions

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