pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery to return modified results at runtime?
5+ messages / 4 participants
[nested] [flat]
* Query to return modified results at runtime?
@ 2006-06-08 00:29 George Handin <postgresql@dafunks.com>
2006-06-08 04:32 ` Re: Query to return modified results at runtime? Richard Broersma Jr <rabroersma@yahoo.com>
2006-06-08 15:51 ` Re: Query to return modified results at runtime? Kenneth B Hill <ken@scottshill.com>
0 siblings, 2 replies; 5+ messages in thread
From: George Handin @ 2006-06-08 00:29 UTC (permalink / raw)
To: pgsql-sql
I have a query:
SELECT * FROM testtable;
Where the results are:
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query to return modified results at runtime?
2006-06-08 00:29 Query to return modified results at runtime? George Handin <postgresql@dafunks.com>
@ 2006-06-08 04:32 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-06-08 15:50 ` Re: Query to return modified results at runtime? George Handin <postgresql@dafunks.com>
1 sibling, 1 reply; 5+ messages in thread
From: Richard Broersma Jr @ 2006-06-08 04:32 UTC (permalink / raw)
To: George Handin <postgresql@dafunks.com>; pgsql-sql
> 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query to return modified results at runtime?
2006-06-08 00:29 Query to return modified results at runtime? George Handin <postgresql@dafunks.com>
2006-06-08 04:32 ` Re: Query to return modified results at runtime? Richard Broersma Jr <rabroersma@yahoo.com>
@ 2006-06-08 15:50 ` George Handin <postgresql@dafunks.com>
2006-06-08 16:32 ` Re: Query to return modified results at runtime? Dias Costa <dcosta@lnec.pt>
0 siblings, 1 reply; 5+ messages in thread
From: George Handin @ 2006-06-08 15:50 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: pgsql-sql
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!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query to return modified results at runtime?
2006-06-08 00:29 Query to return modified results at runtime? George Handin <postgresql@dafunks.com>
2006-06-08 04:32 ` Re: Query to return modified results at runtime? Richard Broersma Jr <rabroersma@yahoo.com>
2006-06-08 15:50 ` Re: Query to return modified results at runtime? George Handin <postgresql@dafunks.com>
@ 2006-06-08 16:32 ` Dias Costa <dcosta@lnec.pt>
0 siblings, 0 replies; 5+ messages in thread
From: Dias Costa @ 2006-06-08 16:32 UTC (permalink / raw)
To: George Handin <postgresql@dafunks.com>; +Cc: Richard Broersma Jr <rabroersma@yahoo.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query to return modified results at runtime?
2006-06-08 00:29 Query to return modified results at runtime? George Handin <postgresql@dafunks.com>
@ 2006-06-08 15:51 ` Kenneth B Hill <ken@scottshill.com>
1 sibling, 0 replies; 5+ messages in thread
From: Kenneth B Hill @ 2006-06-08 15:51 UTC (permalink / raw)
To: George Handin <postgresql@dafunks.com>; +Cc: pgsql-sql
On Wed, 2006-06-07 at 19:29 -0500, George Handin wrote:
> I have a query:
>
> SELECT * FROM testtable;
>
> Where the results are:
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
It looks like you may want to use a validation table:
Name: color_validate
ID Color Code
--- --- ---
1 Blue A
2 Red D
3 Green B
4 Orange C
Then try the following query:
SELECT table_a.ID, color_validate.Code
FROM table_a, color_validate
WHERE (table_a.ID = color_validate.ID);
-Ken
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-06-08 16:32 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-08 00:29 Query to return modified results at runtime? George Handin <postgresql@dafunks.com>
2006-06-08 04:32 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-06-08 15:50 ` George Handin <postgresql@dafunks.com>
2006-06-08 16:32 ` Dias Costa <dcosta@lnec.pt>
2006-06-08 15:51 ` Kenneth B Hill <ken@scottshill.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox