Обсуждение: sql can i substitute
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
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 ===
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
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 ===
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.
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
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
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
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.
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.