Re: Oracle brain twister

To clarify my last response, and I don't have your DB, something like select t1.package_id, t1.ARCH, t2.NAME from blah as t1, blah as t2 where ((t1.package_id = t2.package_id) and (t1.ARCH != "") && (t2.NAME != "")); might do it for ya. Note the join sytax where they point at the same table, joined on the package_id. I'm don't have to do JOINs very often, so someone else could probably refine this a little more but this worked for me. TTFN! -Szii At 06:13 PM 6/14/00 -0700, you wrote: >Hey, I am allowed to ask questions too.. ;) > >I am stuck on an SQL problem. I have the following representative table: > >package_id extract_tag extract_value > > 100 ARCH i386 > 100 NAME perl > 101 ARCH i386 > 101 NAME php > >I need to perform order by and where clauses on the above data using the >extract_tag as if the extract_tag was a column. So, I flip the table >using: > >create or replace view blah as select >package_id,decode(extract_tag,'ARCH',extract_value) ARCH,decode(extract_tag,'NAME',extract_value) NAME >from pkg_extract > >This gives me a view which looks like this: > >package_id ARCH NAME > > 100 i386 > 100 perl > 101 i386 > 101 php > >what I need to do is something like: > > select * from blah where ARCH='i386' and NAME='php' > >Given the way my view looks, this query returns no rows. I need my view >to look like this: > >package_id ARCH NAME > 100 i386 perl > 101 i386 php > >Preferably I'd like to be able to do some sort of distinct/group by magic >in my view creation query to achieve this, but I can't wrap my head around >it enough to figure out how to do that. Failing that, can I run some >query on my view that compacts it like that? > >-Rasmus > > >-- >PHP Database Mailing List (http://www.php.net/) >To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net >For additional commands, e-mail: php-db-help@lists.php.net >To contact the list administrators, e-mail: php-list-admin@lists.php.net > > >