Oracle SQL Query to find a table with two columns in same table

goldieretriever used Ask the Experts™
In oracle I'm looking to create a query to find a table that has two column_name's I have the query to find one column name but I only want to select tables that have both column_name on one line. I know I need a sub query but I don't know how to phrase the SQL

Here is my query to find column one column
select owner, table_name, column_name from all_tab_columns where column_name = 'ECN_ID' and owner = 'DELTEK';

I tried this below sql but the 2nd column came back NULL
select a.owner, a.table_name, a.column_name, (
select b.column_name
from all_tab_columns b
where b.column_name = 'ECN_ID' and b.owner = 'DELTEK' and b.owner = a.owner and b.table_name = a.table_name
) column_name_2
from all_tab_columns a where a.column_name like '%APP%' and a.owner = 'DELTEK';
OWNER        TABLE_NAME              COLUMN_NAME                   COLUMN_NAME_2
DELTEK        ADV_EXP_MATCH      APPLIED_AMT                    (null)
DELTEK        ALLOC_APPLIC              APPL_ALLOC_GRP_NO        (null)
DELTEK        ALLOC_APPLIC              APPL_FY_CD                            (null)

When I try to sort it I get
ORA-01427: single-row subquery returns more than one row
01427. 00000 -  "single-row subquery returns more than one row"

This is what I want it to return
OWNER        TABLE_NAME              COLUMN_NAME                   COLUMN_NAME_2
DELTEK        ECN                             APPR_DT                           ECN_ID
DELTEK        ECN_APPRVL              APPRVL_USER_ID                 ECN_ID
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Oracle DBA
What if the table has more than one column that satisfies %APP%?

Your question says you want a list of table names, but your expected results include column names, which is it?

If there cannot be more than one table that has a column with %APP% and you only want a list of tables, this should do it:
SELECT   owner, 
FROM     all_tab_columns 
OR       column_name = 'ECN_ID' 
GROUP BY owner, 
HAVING   count(1)>1

Open in new window

If that is not the case, this should work:
SELECT a.owner, 
FROM   all_tab_columns a 
       join all_tab_columns b 
         ON a.owner = b.owner 
            AND a.table_name = b.table_name 
WHERE  a.column_name = 'ECN_ID' 
       AND b.column_name LIKE '%APP%' 

Open in new window

NOTE:  The second query will return multiple rows if there are multiple columns that satisfy %APP%, the first will return false positives if that is possible.

Those were typed off the top of my head and not tested, but they should be really close.
I would write something like this:

select owner,table_name
,atc1.column_name column_name1
,atc2.column_name column_name2
from all_tab_columns atc1
join all_tab_columns atc2
using (owner,table_name)
where owner='DELTEK'
and atc1.column_name like '%APP%'
and atc2.column_name='ECN_ID'
and atc1.column_name!=atc2.column_name

Open in new window

johnsoneSenior Oracle DBA

What is the point of this clause:

and atc1.column_name!=atc2.column_name


atc1.column_name like '%APP%'


and atc2.column_name='ECN_ID'

are true, then how is it possible that that clause is violated?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

In this case that's right, but imagine that there is an overlap in the two predicates:
and atc1.column_name like '%ECN%'
and atc2.column_name='ECN_ID'

Open in new window

you probably don't want a line with same column as column1 and column2
johnsoneSenior Oracle DBA

But, it is impossible to have an overlap in this situation.  There is no need for that predicate.
No problem, remove it if you don't want it ;) I like to write queries that are still ok with different parameters.
awking00Information Technology Specialist

with cte as
(select table_name, column_name col1
 from user_tab_columns
 where column_name like 'APP%')
select cte.table_name, cte.col1, column_name col2
from user_tab_columns c, cte
where cte.table_name = c.table_name
and  c.column_name = 'ECN_ID';


Thank you for your help both solutions worked.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial