Re: How to do this as a singleton select?

At least someone tried, maybe the information provided wasn't clear enuf?

My question is this; if you already know the tablename you want, why is your query returning just the tablename? And, your second set of examples seem to be much different from your first.

"Ryan Gaffuri" <rgaffuri_at_cox.net> wrote in message news:1efdad5b.0301090437.1df3b216_at_posting.google.com...
> 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 - 08:33:12 CST