Re: How to do this as a singleton select?

From: Martin Burbridge <pobox002_at_bebub.com>
Date: Thu, 09 Jan 2003 00:32:42 GMT
Message-ID: <Xns92FDC6734E40Dpobox002bebubcom@216.148.227.77>

"Ryan" <rgaffuri_at_cox.net> wrote in
news:i13T9.14617$t06.462344_at_news2.east.cox.net:

> 
> "Greg Akins" <insomnia_at_cvzoom.net> wrote in message
> news:3e1c7444$1_at_post.usenet.com...

>>
>>
>> "Ryan Gaffuri" <rgaffuri_at_cox.net> wrote in message
>> news:1efdad5b.0301081021.fb21748_at_posting.google.com...
>> > I cant remember how to do this as one select statement.
>> >
>> > SELECT TABLE_NAME
>> > FROM USER_TAB_COLUMNS
>> > WHERE TABLE_NAME = p_tableName(this is a variable passed in)
>> > and COLUMN_NAME = ... however, there are 4 column_names and all
>> > of them have to appear.
>> >
>> > I know I can do it with 4 implicit cursors and just pass the
>> > results to the next, but how do I do it with a subquery?
>> >
>> > SELECT column
>> > into v_column
>> > from table
>> > where column = 'A'
>> >
>> > select column
>> > into v_column2
>> > from table
>> > where column = v_column
>> >
>> > and so on...
>> >
>> >
>> > I know its basic, but I cant remember how to do this. Went brain
>> > dead.
>>
>> select table_name
>> from user_tab_columns
>> where table_name = p_tablename
>> and column_name in ('A', 'B', 'C')
>>
>> or
>>
>> select table_name
>> from user_tab_columns
>> where table_name = p_tablename
>> and column_name in (select column from table where column = v_column
>> or column = v_column2)
>>
>> Is that what you were looking for?
>>

> 
> The answer is not an 'IN' Since I need all of them. IN is 'A' or 'B'
> or 'C' 
> 
> I need all 4 of them.
> 
>

You could use in and a group by and count that you get all four.

SQL> select table_name
  2 from all_tab_columns
  3 where table_name = 'EMP'
  4 and column_name in ('EMPNO','ENAME','JOB','MGR')   5 group by table_name
  6 having count(*) = 4;

TABLE_NAME

EMP SQL> edi
Wrote file afiedt.buf

  1 select table_name
  2 from all_tab_columns
  3 where table_name = 'EMP'
  4 and column_name in ('EMPNO','ENAME','JOB','NOTINEMP')   5 group by table_name
  6* having count(*) = 4
SQL> / no rows selected

SQL> Martin Received on Wed Jan 08 2003 - 18:32:42 CST