r/SQL•
Clickar

Oracle ANSI Outer Join

Oracle

For the sake of keeping this short I need to make an outer join and unfortunately it has to be done in old ANSI format. The issue I am having is that I need to match 1 field to the substring on another field.

An example of the field on the main table is

557214479

On the joining table the field has the same unique number followed by SN then some trailing numbers that are not relevant

557214479SN36384376361

I wanted to use what is pasted below and it worked fine as an inner join but when trying to make it an outer join i get the error two tables cannot be outer joined to each other. I have tried a ton of combinations and nothing seems to work.

x.id = substr(ce_sn_link.reference_nbr(+),0,(instr(ce_sn_link.reference_nbr,'SN')-1))

I cannot just use substr because the number of digits before the SN that I need to match on is based off an ever growing primary key that started as 7 digits and is now up to 9 digits. I could do this very easily if I didnt HAVE to use ANSI style but without needing to explain why I would like to keep it in this format if possible. I am open to any suggestions or assistance.

THANKS!!

Posted Sep 6, 2024, 5:26 PM UTC

52
Loading comments…