0

I am working on 2 Tables. One contains 657 rows matching my filter criteria, and the other contains 193 records.

I successfully joined them using Oracle's "AFAIK" syntax:

select
  ecp.portfolio_acct, ecp.posn_id cost_posn_id, ecp.asset_id 
from MID_COST_POSITION ecp, MID_CASH_POSITION cas
where ecp.portfolio_acct = 10183306
  and ecp.portfolio_acct = cas.portfolio_acct(+)
  and ecp.asset_id       = cas.asset_id(+)
;

However, I cannot seem to replicate these results using ANSI-SQL (or, ISO-sql):

select
  ecp.portfolio_acct, ecp.posn_id cost_posn_id, ecp.asset_id 
from MID_COST_POSITION ecp
LEFT OUTER JOIN MID_CASH_POSITION cas
  on ecp.portfolio_acct = cas.portfolio_acct
where ecp.asset_id      = cas.asset_id
  and ecp.portfolio_acct = 10183306 
;

I have tried various JOINs without success. In the 1st example I had to Outer Join 2 separate fields, which I do not know how to accomplish using ANSI-SQL.

I appreciate any guidance anyone can offer. Thank you!

CarCrazyBen
  • 1,066
  • 5
  • 14
  • 37
  • The link to https://stackoverflow.com/questions/4001045/how-do-i-convert-a-legacy-left-outer-join-statement-in-oracle gave me the answer I need. The order of "ON", "AND", and "WHERE" clauses quite definitely make a difference. select ecp.portfolio_acct, ecp.posn_id cost_posn_id, ecp.asset_id from MID_COST_POSITION ecp LEFT JOIN MID_CASH_POSITION cas on cas.asset_id = ecp.asset_id and ecp.portfolio_acct = cas.portfolio_acct where ecp.portfolio_acct = 10183306 order by 1, 3; – CarCrazyBen Jun 13 '17 at 17:10

1 Answers1

3
select
  ecp.portfolio_acct, ecp.posn_id cost_posn_id, ecp.asset_id 
from MID_COST_POSITION ecp
LEFT OUTER JOIN MID_CASH_POSITION cas
  on ecp.portfolio_acct = cas.portfolio_acct
and ecp.asset_id      = cas.asset_id
Where ecp.portfolio_acct = 10183306 
;

You were turning this into an inner join with the following where condition because it now has to meet that condition in the result set:

ecp.asset_id      = cas.asset_id
HLGEM
  • 94,695
  • 15
  • 113
  • 186