Re: Oracle brain twister

Addressed to: Rasmus Lerdorf <rasmus@linuxcare.com> php-dev@lists.php.net ** Reply to note from Rasmus Lerdorf <rasmus@linuxcare.com> Wed, 14 Jun 2000 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='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? > 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 = B.package_id AND A.extract_value <> B.extract_value AND A.extract_tag = 'NAME'; Should return: package_it Name Arch 100 perl i386 101 php i386 Rick Rick Widmer Internet Marketing Specialists www.developersdesk.com