X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 960769FA4D8 for ; Thu, 8 Jun 2006 14:05:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 79790-09 for ; Thu, 8 Jun 2006 14:04:56 -0300 (ADT) X-Greylist: delayed 00:35:23.554866 by SQLgrey- Received: from orion.lnec.pt (orion.lnec.pt [193.136.104.22]) by postgresql.org (Postfix) with ESMTP id 6C5A29FA1A4 for ; Thu, 8 Jun 2006 14:04:55 -0300 (ADT) Received: from postbox.lnec.pt (ldapcentral.lnec.pt [193.136.104.83]) by orion.lnec.pt (Postfix) with ESMTP id 198F9176CE; Thu, 8 Jun 2006 17:29:19 +0100 (WEST) Received: from [193.136.208.14] (cti-dcosta.lnec.pt [193.136.208.14]) (authenticated bits=0) by postbox.lnec.pt (8.12.9-20030917/8.12.9) with ESMTP id k58GTI4p022775; Thu, 8 Jun 2006 17:29:18 +0100 Message-ID: <4488512D.209@lnec.pt> Date: Thu, 08 Jun 2006 17:32:45 +0100 From: Dias Costa User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: George Handin CC: Richard Broersma Jr , pgsql-sql@postgresql.org Subject: Re: Query to return modified results at runtime? References: <20060608043236.96435.qmail@web31810.mail.mud.yahoo.com> <44884752.7030303@dafunks.com> In-Reply-To: <44884752.7030303@dafunks.com> Content-Type: multipart/alternative; boundary="------------050606040609020509020100" X-LNEC-Orion-MailScanner: Found to be clean X-LNEC-Orion-MailScanner-SpamCheck: not spam, SpamAssassin (score=-0.96, required 5, BAYES_00 -1.00, HTML_40_50 0.04, HTML_MESSAGE 0.00, HTML_TITLE_EMPTY 0.00) X-LNEC-Orion-MailScanner-From: dcosta@lnec.pt X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/102 X-Sequence-Number: 25338 This is a multi-part message in MIME format. --------------050606040609020509020100 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi, You can use the construct *Case When* but You have to have Your information structured (even if only in Your mind) in order to achive the results You want. So, suposse You have for the color Blue the letter A, for the color Red the letter D, for the color Green the letter B and finally for the color Orange the letter C. For the following data: create table dcosta.colors (id numeric(3), Color varchar(12)); insert into dcosta.colors values(1, 'Blue'); insert into dcosta.colors values(2, 'Red'); insert into dcosta.colors values(3, 'Green'); insert into dcosta.colors values(4, 'Orange'); You can use the following instruction: * *SELECT ID, Color, CASE WHEN color = 'Blue' THEN 'A' WHEN color = 'Red' THEN 'D' WHEN color = 'Green' THEN 'B' WHEN color = 'Orange' THEN 'C' ELSE 'other' END FROM dcosta.colors; Obviously You can ommit the column Color from the select clause. Hope I helped Dias Costa George Handin wrote: > Richard Broersma Jr wrote: > >>> ID Color >>> --- ------- >>> 1 Blue >>> 2 Red >>> 3 Green >>> 4 Orange >>> >>> How would I rewrite the query to return results where the colors are >>> replaced by letters to give the following results? >>> >>> ID Color >>> --- ------- >>> 1 A >>> 2 D >>> 3 B >>> 4 C >> >> >> >> http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html >> >> >> this is probably the easiest to implement but hard to mangage over >> time. Another solution would >> be to create color_code table that is referenced by your test table. >> Then when you can create a >> query as: select a.ID, b.code from test as a join color_code as b on >> a.color = b.color; >> >> There are additional solutions to this also. But these two are >> probably the easiest. > > > Thanks! > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq --------------050606040609020509020100 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 
Hi,


You can use the construct Case When but You have to have
Your information structured (even if only in Your mind) 
in order to achive the results You want.

So, suposse You have for the color Blue the letter A,
for the color Red the letter D, for the color Green the 
letter B and finally for the color Orange the letter C.

For the following data:

create table dcosta.colors
(id     numeric(3),
 Color  varchar(12));

insert into dcosta.colors values(1, 'Blue');
insert into dcosta.colors values(2, 'Red');
insert into dcosta.colors values(3, 'Green');
insert into dcosta.colors values(4, 'Orange');

You can use the following instruction:


SELECT ID, Color, 
       CASE WHEN color = 'Blue'   THEN 'A'
            WHEN color = 'Red'    THEN 'D'
            WHEN color = 'Green'  THEN 'B'
            WHEN color = 'Orange' THEN 'C'
            ELSE 'other'
       END
FROM dcosta.colors;

Obviously You can ommit the column Color from the select clause.


Hope I helped
Dias Costa




George Handin wrote:
Richard Broersma Jr wrote:
ID    Color
---   -------
1     Blue
2     Red
3     Green
4     Orange

How would I rewrite the query to return results where the colors are replaced by letters to give the following results?

ID    Color
---   -------
1     A
2     D
3     B
4     C


http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html

this is probably the easiest to implement but hard to mangage over time.  Another solution would
be to create color_code table that is referenced by your test table.  Then when you can create a
query as: select a.ID, b.code from test as a join color_code as b on a.color = b.color;

There are additional solutions to this also. But these two are probably the easiest.

Thanks!

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq

--------------050606040609020509020100--