DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Help needed to convert Access query to normal sql....

  1. #1
    Join Date
    Sep 2000
    Posts
    103
    Hi,

    Can someone please me convert this Access query to an oracle sql query.

    I'm not really sure how this inner and left join works.

    SELECT ADV.Account_Year AS ColCode, ADV.Account_Year AS ColDesc, ADV.Account_Item_ID AS RowCode, IIF(ISNULL(LT.Translation), AI.Account_Item, LT.Translation) AS RowDesc, ADV.Value AS Amount, ER.Exchange_Rate AS DefRate, UER.Exchange_Rate AS UseRate, CYE.Year_End, TIL.Line_No, TIL.Hierarchy_Level, TIL.SubTotal_ID, AI.Negative_Polarity
    FROM (((((Account_Data_Values AS ADV INNER JOIN Account_Items AS AI ON ADV.Account_Item_ID = AI.Account_Item_ID)
    INNER JOIN Company_Year_Ends AS CYE ON (ADV.AMB_No = CYE.AMB_No) AND (ADV.Account_Year = CYE.Account_Year))
    INNER JOIN Template_Item_Link TIL ON TIL.Account_Item_ID = ADV.Account_Item_ID)
    LEFT JOIN EXCHANGE_RATES AS ER ON (CYE.YEAR_END = ER.DATE_REPORTED) AND (CYE.REPORTED_CURRENCY = ER.COUNTRY_CODE))
    LEFT JOIN (SELECT * FROM User_Exchange_Rates WHERE User_ID = 'SingleUser') UER ON UER.Country_Code = ER.Country_Code AND UER.Date_Reported = ER.Date_Reported)
    LEFT JOIN (SELECT * FROM Language_Translations WHERE Country_Code = 'UK') LT ON LT.Account_Item_ID = ADV.Account_Item_ID
    WHERE ADV.Account_Year IN (2001, 2000, 1999, 1998, 1997, 1996, 1995, 1994)
    AND ADV.AMB_No = 87200
    AND ADV.Business_Class_ID = 0
    AND TIL.Nat_Template_ID = 622
    ORDER BY ADV.Account_Year DESC, AI.Account_Item, ADV.Account_Item_ID

    Thanks a lot for the help.

    pst

  2. #2
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    I though they would get optimized in oracle itself?!
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  3. #3
    Join Date
    Sep 2000
    Posts
    103
    I'm sorry, but I didnt understand by what you mean by get optimized by oracle. CAn I just run this query as shown above in oracle? I would have thought not.
    I just want to run this query on sql*plus.

    thanks
    pst

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    A LEFT join is the same as an outer join ( http://technet.oracle.com/docs/produ...03sch.htm#1142 )

    A INNER join is the same as a regular join ( http://technet.oracle.com/docs/produ...si.htm#1023200 )
    Jeff Hunter

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