By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,444 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

Convert query using outer join from Oracle to Postgresql

P: 61
hello all...
I'm trying to convert the query which uses outer join statment from Oracle to PostgreSQL but I have some problems with this query
may some one help me for this problem?
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. ACT_ACCOUNT.ID_ACCOUNT, ACT_ACCOUNT.CODE_ACCOUNT,  
  3. ACT_ACCOUNT.NAME_AR,  ACT_ACCOUNT.NAME_EN,  ACT_ACCOUNT.ACCOUNT_PID, 
  4. DECODE(ACT.CODE_ACCOUNT,null,0,ACT.CODE_ACCOUNT)as "CODE_PARENT_ACCOUNT",   
  5. DECODE(ACT.NAME_AR,null,' ',ACT.NAME_AR)as "NAME_PARENT_ACCOUNT",
  6. ACT_ACCOUNT.NOTES1,  ACT_ACCOUNT.NOTES2,  
  7. ACT_ACCOUNT.ID_INCOME, ACT_ACCOUNT.ID_FINANCIAL_CENTER,
  8. ACT_ACCOUNT.ID_COMPLEX_FINANCIAL_CENTER,
  9. ACT_ACCOUNT.ID_MONETARY_FLOW, ACT_ACCOUNT.ID_OWNERSHIP_CHANGE,
  10. ACT_ACCOUNT.INCOME_IS_POSITIVE,
  11. ACT_ACCOUNT.FINANCIAL_CENTER_IS_POSITIVE,
  12. ACT_ACCOUNT.MONETARY_FLOW_IS_POSITIVE,
  13. ACT_ACCOUNT.OWNERSHIP_CHANGE_IS_POSITIVE,
  14. ACT_ACCOUNT.ACT_TYPE, ACT_ACCOUNT.ACT_NATURE,
  15. ACT_ACCOUNT.FINAL_ACCOUNT, ACT_ACCOUNT.IS_FINAL,
  16. ACT_ACCOUNT.ACTIVE_INDICATOR, ACT_ACCOUNT.DATE_CREATED,
  17. ACT_ACCOUNT.DATE_UPDATED, ACT_ACCOUNT.ID_USER,
  18. ACT_ACCOUNT_DETAIL.ID_ACCOUNT,
  19. ACT_ACCOUNT_DETAIL.BUSINESS, ACT_ACCOUNT_DETAIL.PHONE,
  20. ACT_ACCOUNT_DETAIL.PHONE2, ACT_ACCOUNT_DETAIL.MOBILE,
  21. ACT_ACCOUNT_DETAIL.FAX, ACT_ACCOUNT_DETAIL.ADDRESS1,
  22. ACT_ACCOUNT_DETAIL.ADDRESS2, ACT_ACCOUNT_DETAIL.ID_CITY,
  23. ACT_ACCOUNT_DETAIL.POB, ACT_ACCOUNT_DETAIL.E_MAIL,
  24. ACT_ACCOUNT_DETAIL.WEB_SITE, ACT_ACCOUNT_DETAIL.SPECIAL_FILE,
  25. ACT_ACCOUNT_DETAIL.ID_TRUST_PERIOD,
  26. ACT_TRUST_PERIOD.PERIOD, ACT_ACCOUNT.DATE_ACT_CREATED,
  27. ACT_ACCOUNT_DETAIL.ID_CURRENCY, GEN_CURRENCY.NAME_CURRENCY,
  28. GEN_CURRENCY.EXCHANGE, ACT_ACCOUNT_DETAIL.HAS_MARK,
  29. ACT_ACCOUNT_DETAIL.LAST_YEAR_BALANCE,
  30. ACT_ACCOUNT_DETAIL.ID_SALES_MAN,
  31. ACT_ACCOUNT_DETAIL.TRUST_LIMIT,
  32. ACT_ACCOUNT.ID_BOX_ACCOUNT, BOX_ACT.CODE_ACCOUNT,
  33. BOX_ACT.NAME_AR, BOX_ACT.NAME_EN
  34.  
  35. FROM ACT_ACCOUNT, ACT_ACCOUNT_DETAIL, 
  36.         ACT_ACCOUNT ACT,   ACT_ACCOUNT BOX_ACT,
  37.         GEN_CURRENCY, ACT_TRUST_PERIOD      
  38.  
  39. WHERE 
  40. ACT_ACCOUNT.ACCOUNT_PID = ACT.ID_ACCOUNT(+) 
  41.  
  42. AND ACT_ACCOUNT.ACTIVE_INDICATOR <> 'I'
  43.  
  44. AND ACT_ACCOUNT.ID_BOX_ACCOUNT = BOX_ACT.ID_ACCOUNT(+)
  45.  
  46. AND ACT_ACCOUNT.ID_ACCOUNT= ACT_ACCOUNT_DETAIL.ID_ACCOUNT
  47.  
  48. AND ACT_ACCOUNT_DETAIL.ID_CURRENCY = GEN_CURRENCY.ID_CURRENCY(+)
  49.  
  50. AND ACT_ACCOUNT_DETAIL.ID_TRUST_PERIOD = ACT_TRUST_PERIOD.ID_TRUST_PERIOD(+)
  51.  
Oct 5 '10 #1

✓ answered by rski

Use LEFT OUTER JOIN clauses
http://www.postgresql.org/docs/9/sta...pressions.html
They can also be use in Oracle.

Share this Question
Share on Google+
1 Reply


Expert 100+
P: 700
Use LEFT OUTER JOIN clauses
http://www.postgresql.org/docs/9/sta...pressions.html
They can also be use in Oracle.
Oct 6 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.