Thread: sql can i substitute

sql can i substitute

From
Kenneth Gonsalves
Date:
hi

table:

name varchar(10)
fruit integer

i want to write an sql statement like this:

select fruit from table

which should return 'good' if fruit = 1 and 'bad' if fruit =2 and 'rotten' if 
fruit =3

can it be done?

kg


Re: [despammed] sql can i substitute

From
Andreas Kretschmer
Date:
am  17.12.2004, um 16:55:45 +0530 mailte Kenneth Gonsalves folgendes:
> hi
> 
> table:
> 
> name varchar(10)
> fruit integer
> 
> i want to write an sql statement like this:
> 
> select fruit from table
> 
> which should return 'good' if fruit = 1 and 'bad' if fruit =2 and 'rotten' if 
> fruit =3
> 
> can it be done?

Okay:

test=# select * from fruit; name  | fruit
--------+-------Apple  |     1Banana |     2Cherry |     3
(3 Zeilen)


test=# select name, fruit,  case
test-# when fruit = 1 then 'good'
test-# when fruit = 2 then 'bad'
test-# when fruit = 3 then 'rotten' end from fruit; name  | fruit |  case
--------+-------+--------Apple  |     1 | goodBanana |     2 | badCherry |     3 | rotten
(3 Zeilen)



Please read
http://www.postgresql.org/docs/7.4/interactive/functions-conditional.html#AEN11381



Regards, Andreas
-- 
Andreas Kretschmer    (Kontakt: siehe Header)              Tel. NL Heynitz:  035242/47212
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net===    Schollglas Unternehmensgruppe    === 


Re: [despammed] sql can i substitute

From
Kenneth Gonsalves
Date:
On Friday 17 December 2004 05:15 pm, Andreas Kretschmer wrote:

> test=# select * from fruit;
>   name  | fruit
> --------+-------
>  Apple  |     1
>  Banana |     2
>  Cherry |     3
> (3 Zeilen)
>
>
> test=# select name, fruit,  case
> test-# when fruit = 1 then 'good'
> test-# when fruit = 2 then 'bad'
> test-# when fruit = 3 then 'rotten' end from fruit;
>   name  | fruit |  case
> --------+-------+--------
>  Apple  |     1 | good
>  Banana |     2 | bad
>  Cherry |     3 | rotten
> (3 Zeilen)

thanks - opened up a whole new world for me. One more thing, the values come 
under the column 'case', can i avoid having the column 'case' and get the 
'good', 'bad' and 'rotten' under the column 'fruit'?

kg


Re: [despammed] sql can i substitute

From
Andreas Kretschmer
Date:
am  17.12.2004, um 17:48:15 +0530 mailte Kenneth Gonsalves folgendes:
> 
> thanks - opened up a whole new world for me. One more thing, the values come 
> under the column 'case', can i avoid having the column 'case' and get the 
> 'good', 'bad' and 'rotten' under the column 'fruit'?

Yes, simple:

test=# select name,  case
test-# when fruit = 1 then 'good'
test-# when fruit = 2 then 'bad'
test-# when fruit = 3 then 'rotten' end as fruit from fruit; name  | fruit
--------+--------Apple  | goodBanana | badCherry | rotten
(3 Zeilen)


Regards, Andreas
-- 
Andreas Kretschmer    (Kontakt: siehe Header)              Tel. NL Heynitz:  035242/47212
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net===    Schollglas Unternehmensgruppe    === 


Re: sql can i substitute

From
"D'Arcy J.M. Cain"
Date:
On Fri, 17 Dec 2004 16:55:45 +0530
Kenneth Gonsalves <lawgon@thenilgiris.com> wrote:
> i want to write an sql statement like this:
> 
> select fruit from table
> 
> which should return 'good' if fruit = 1 and 'bad' if fruit =2 and
> 'rotten' if fruit =3

An alternative to Andreas' suggestion would be to create a simple lookup
table and join them.  This is good if the real life example can get
larger and/or the list can change and you don't want to modify code
every time it does.  

[totally made up output]
fstate_id | fstate_name
----------+-------------       1 | good       2 | bad       3 | rotten

SELECT fstate.fstate_name AS "Fruit state"
FROM table, fstate
WHERE table.fstate_id = fstate.fstate_id;

Now you can easily add another state:

INSERT INTO fstate VALUES (4, 'smelly');

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: sql can i substitute

From
Kenneth Gonsalves
Date:
On Friday 17 December 2004 06:12 pm, D'Arcy J.M. Cain wrote:

> An alternative to Andreas' suggestion would be to create a simple lookup
> table and join them.  This is good if the real life example can get
> larger and/or the list can change and you don't want to modify code
> every time it does.

yes, but in this case the list wont change, and i'm trying to port mysql to 
pgsql without disturbing as far as possible the mysql queries. basically 
replacing some 'set' and 'enum' datatypes - one table has six of thes, and 
adding six tables is not on

kg


Re: [despammed] sql can i substitute

From
Kenneth Gonsalves
Date:
On Friday 17 December 2004 05:54 pm, Andreas Kretschmer wrote:

> Yes, simple:
>
> test=# select name,  case
> test-# when fruit = 1 then 'good'
> test-# when fruit = 2 then 'bad'
> test-# when fruit = 3 then 'rotten' end as fruit from fruit;

can one do the same thing for an 'insert' statement?

kg


Re: [despammed] sql can i substitute

From
Achilleus Mantzios
Date:
O Kenneth Gonsalves έγραψε στις Dec 17, 2004 :

> On Friday 17 December 2004 05:54 pm, Andreas Kretschmer wrote:
> 
> > Yes, simple:
> >
> > test=# select name,  case
> > test-# when fruit = 1 then 'good'
> > test-# when fruit = 2 then 'bad'
> > test-# when fruit = 3 then 'rotten' end as fruit from fruit;
> 
> can one do the same thing for an 'insert' statement?

its an expression, so yes, 
INSERT INTO foo3 VALUES (2,case when 't' then 'bar' else 'foo' end);
but whats the point?

> 
> kg
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html
> 

-- 
-Achilleus



Re: sql can i substitute

From
Bruno Wolff III
Date:
On Fri, Dec 17, 2004 at 16:55:45 +0530, Kenneth Gonsalves <lawgon@thenilgiris.com> wrote:
> hi
> 
> table:
> 
> name varchar(10)
> fruit integer
> 
> i want to write an sql statement like this:
> 
> select fruit from table
> 
> which should return 'good' if fruit = 1 and 'bad' if fruit =2 and 'rotten' if 
> fruit =3
> 
> can it be done?

Yes; use a CASE statement.


Re: sql can i substitute

From
Bruno Wolff III
Date:
On Fri, Dec 17, 2004 at 18:22:48 +0530, Kenneth Gonsalves <lawgon@thenilgiris.com> wrote:
> On Friday 17 December 2004 06:12 pm, D'Arcy J.M. Cain wrote:
> 
> > An alternative to Andreas' suggestion would be to create a simple lookup
> > table and join them.  This is good if the real life example can get
> > larger and/or the list can change and you don't want to modify code
> > every time it does.
> 
> yes, but in this case the list wont change, and i'm trying to port mysql to 
> pgsql without disturbing as far as possible the mysql queries. basically 
> replacing some 'set' and 'enum' datatypes - one table has six of thes, and 
> adding six tables is not on

You might be better off using domains and a constraint to implement
set types. Storing numbers instead of names will save a little space,
but unless you are having some problems with resources, using the strings
directly will be simpler.