outer joins with passthrough query?

[Guest]

Hi - I am writing a passthrough query with a nested query in Access and am
trying to create an outer join. However, the outer join is not working as
expected and I am loosing a couple of records. MY SQL syntax is:

select
i.ticket_userid,
i.ticket_date,
i.ticket_seq_no,
i.tran_type,
i.start_date,
i.end_date,
i.pymnt_date,
i.sys_interest,
i.sttl_stts,
s.DF as DF_Start
from main_intp i, (select * from eur_obs_df where cur = 'USD') s
where
s.apply_date = i.start_date (+)
and i.ticket_seq_no = 25
and i.ticket_date = '13-Oct-2005';

Does anyone please have any idea why this is not working?

Thanks.

 

[Gary Walter]

Hi - I am writing a passthrough query with a nested query in Access and am
trying to create an outer join. However, the outer join is not working as
expected and I am loosing a couple of records. MY SQL syntax is:

select
i.ticket_userid,
i.ticket_date,
i.ticket_seq_no,
i.tran_type,
i.start_date,
i.end_date,
i.pymnt_date,
i.sys_interest,
i.sttl_stts,
s.DF as DF_Start
from main_intp i, (select * from eur_obs_df where cur = 'USD') s
where
s.apply_date = i.start_date (+)
and i.ticket_seq_no = 25
and i.ticket_date = '13-Oct-2005';

Hi Dave,

I am not familiar with MY SQL syntax,
but in Access

you can't filter on the inner table of an
outer join and get meaningful results.

Is that what you are doing here?

Did you try 2 subqueries in your FROM clause?
(just a guess)

select
i.ticket_userid,
i.ticket_date,
i.ticket_seq_no,
i.tran_type,
i.start_date,
i.end_date,
i.pymnt_date,
i.sys_interest,
i.sttl_stts,
s.DF as DF_Start
from
(select * from main_intp
where ticket_seq_no = 25
and ticket_date = '13-Oct-2005') i,
(select * from eur_obs_df where cur = 'USD') s
where
s.apply_date = i.start_date (+) ;

Again, just a guess interpolating from
a similar problem that happens in Access.

good luck,

gary

 

[Guest]

Thanks Gary - good guess! It has worked although strangely I have to put the
(+) on the other side to the table I want all records from:

select
i.ticket_userid,
.......
s.DF as DF_Start
from (select * from main_intp where ticket_seq_no = 25 and ticket_date =
'13-Oct-2005') i, (select * from eur_obs_df where cur = 'USD') s
where
i.start_date = s.apply_date (+)


returns all results from sub query i.

Works OK but is the other way to what I would expect. (does anyone know why?)

Thanks for you help!