Outer Join on a subselect

[djbjr]

I have a complex insert statement that joins to a table that is specific to a small number of employees in our company, hence I need an outer join. The problem is that I have to join to the MAX(effective_sequence_nbr) with an outer join. I keep getting errors on that join

example

and a.effseq = (SELECT max(d.effseq(+))
FROM load_empl_hist_comp d
WHERE d.emplid = a.emplid
AND d.effective_date_key = a.effective_date_key
AND d.effseq <= a.effseq)


ERROR at line 99:
ORA-01705:
an outer join cannot be
specified on a correlation column

OK so where do I put the (+)???

Thanks in advance

 

[sem]

Not sure, but try this one:

a.effseq = (SELECT nvl(max(d.effseq), a.effseq)
FROM load_empl_hist_comp d
WHERE d.emplid = a.emplid
AND d.effective_date_key = a.effective_date_key
AND d.effseq <= a.effseq)

Regards, Dima

 

[saketbansal]

Make statement like this

select * from tab_a , (SELECT emplid, .effective_date_key , max(d.effseq)
FROM load_empl_hist_comp
group by emplid, .effective_date_key ) tab_b
where
--- now here u can relate tab_b (in line view) and tab_a with outer join


HTH...
Saket

 

[djbjr]

Thanks for your help guys but I dont want to eliminate rows with a lesser value.

For example I can have an employee with 3 actions on one day so the UK will be emplid, effdt, effseq
I want to match the emplid, effdt, and effseq to the table in question, but if there is no match on that table, I want to grab the matching emplid, effdt, and max(effseq).

Your tip will only grab the max(effseq) for every emplid/effdt.

i.e.

TABLE A TABLE B
ID DATE EFFSEQ ID DATE EFFSEQ VALUE
1 1/1 1 1 1/1 1 A
1 1/2 1 1 1/2 1 B
1 1/2 2 1 1/2 2 C
1 1/2 3


So for my first row I want to grab the value A
B for the second row and C for the 3rd row.

Now the 4th row matches emplid and date but not effseq, so I want to grab the max(effseq) for that row.

Is that more clear?

 

[saketbansal]

I think something like this can help

select *
from tab_a , (SELECT emplid, .effective_date_key , max(d.effseq) as meffseq
FROM load_empl_hist_comp
group by emplid, .effective_date_key ) tab_b , load_empl_hist_comp
where
tab_a.emplid = load_empl_hist_comp.emplid
and tab_a.effective_date_key = load_empl_hist_comp.effective_date_key
and tab_a.effseq = load_empl_hist_comp.effseq
and tab_a.emplid = tab_b.emplid (+ )
and tab_a.effective_date_key = tab_b.effective_date_key (+)
and tab_a.effseq = tab_b.meffseq(+)