Re: Re: Oracle brain twister

Forwarding note from: php3@developersdesk.com Wed, 14 Jun 2000 20:52:13 MST Oops, wrong list... Also, I made a little change. Addressed to: Rasmus Lerdorf <rasmus@linuxcare.com> php-dev@lists.php.net ** Reply to note from Rasmus Lerdorf <rasmus@linuxcare.com> Wed, 14 Jun 200= 0 18:13:03 -0700 (PDT) > > 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=3D'i386' and NAME=3D'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? > does this help? its MySQL, but I think it will convert... SELECT A.pachage_id, A.extract_value as Name, B.extract_value AS arch from blah AS A, blah AS B WHERE A.package_id =3D B.package_id AND A.extract_value <> B.extract_value AND A.extract_tag =3D 'NAME'; Should return: package_it Name Arch 100 perl i386 101 php i386 I think it would be better if the first AND clause was: A.extract_tag <> B.extract_tag