thanks... i was playing around with sub-queries. Your way is simpler.
Cant believe we had 3 guys post the the exact same incorrect answer on
here though :)
Martin Burbridge <pobox002_at_bebub.com> wrote in message news:<Xns92FDC6734E40Dpobox002bebubcom_at_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 Thu Jan 09 2003 - 06:37:23 CST
