Mental note–syntax for simple left outer join on oracle

I’m too old school, I use where exists and union, but not enough left outer joins.

select table_name, simd

from all_tables LEFT OUTER JOIN ol910.f9860 ON ltrim(rtrim(siobnm)) = ltrim(rtrim(table_name))

where table_name like 'F98%' and owner = 'PP910';




The example above is great for CNC people (and JDE DBA’s) as it gives you a description of the extremely helpful table names in JDE.  Note that the left outer join helps when there is package build files in the mix – things that are not in object librarian.  Note that this is also fairly timely with another recent post about finding table names or counts – or something.




TABLE_NAME                     SIMD                                                        

------------------------------ ------------------------------------------------------------

F98745                         Smart Field Named Mappings                                  

F98750                         Forms Design Aid Text Information                           

F98751                         Forms Design Aid Specification Information                  

F98752                         Forms Design Aid/Software Versions Repository Header Info.  

F98753                         Forms Design Aid/Software Versions Repository Detail Info.  

F98760                         Report Design Aid Text Information                          

F98761                         Report Design Aid Specification Info                        

F98762                         JDEBLC - Behavior Information                               

F98950                         User Overrides Table                                        

F98950D                        User Overrides Language Table                               

F989998                        Java Persistent Object Cross Reference Table