Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Jori.

Asked: February 08, 2019 - 8:51 am UTC

Last updated: July 13, 2023 - 12:34 pm UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 1000+ times

You Asked

Hi
Is it possible to join ALL_TABLES and normal table?
I like make dynamic SQL and select columns that shown after selected from list.
Something like this:

select
*
from table1 a
where a.columns in (SELECT DISTINCT
A.COLUMN_NAME
FROM SYS.ALL_TAB_COLUMNS A
WHERE A.OWNER = 'ME'
AND A.TABLE_NAME = 'Table1'
AND A.COLUMN_NAME LIKE '%CONF%')

and Chris said...

Sure, you can join all_tables to any other table/view:

create table t as 
  select * from dba_objects;
  
select at.owner, count(*) 
from   all_tables at
join   t 
on     t.object_name = at.table_name
and    t.object_type = 'TABLE'
group  by at.owner;

OWNER               COUNT(*)   
APEX_040200                452 
MDSYS                      130 
CHRIS                       78 
OUTLN                        6 
CTXSYS                      53 
OLAPSYS                      2 
FLOWS_FILES                  1 
HR                           9 
SYSTEM                     182 
DVSYS                       36 
SCOTT                        4 
DBSNMP                      20 
GSMADMIN_INTERNAL           19 
OJVMSYS                      6 
ORDSYS                       5 
OE                          11 
PM                           2 
SH                          19 
APPQOSSYS                    4 
XDB                         29 
ORDDATA                     90 
IX                           8 
SYS                       1256 
WMSYS                       40 
LBACSYS                     22


Though I suspect that's not what you're trying to do...

In your query:

select *
from table1 a
where a.columns in (SELECT DISTINCT
A.COLUMN_NAME
FROM SYS.ALL_TAB_COLUMNS A
WHERE A.OWNER = 'ME'
AND A.TABLE_NAME = 'Table1'
AND A.COLUMN_NAME LIKE '%CONF%')


You're getting the list of column names in me.table1 like '%CONF%'. Then returning the rows in table1 that store these values in table1.columns. Not the columns with these names.

If you want to join based on the name of the column, you'll need dynamic SQL.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, July 13, 2023 - 12:06 pm UTC

a.columns doesn't exist.

Chris Saxon
July 13, 2023 - 12:34 pm UTC

There could be TABLE1.COLUMNS. Though as stated I suspect that's not what the OP is trying to do.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.