-
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
-
I though they would get optimized in oracle itself?!
-
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
-
