SQL select case

Ahoj,

potreboval by som pomoct s nasledujucim:

mam tabulku:

CREATE TABLE test
(number INTEGER NOT NULL,
string VARCHAR(255) NOT NULL,
count INTEGER,
UNIQUE(number), UNIQUE(string))

a potrebujem vytvorit sql query, ktora vrati:
- 1, ak je tabulka prazdna,
- number z riadka, v ktorom sa zhoduje nejaky retazec s retazcom zo stlpca string
- number z riadka, v ktorom je count == 0
- max(count) + 1 inak

moj pokus:
SELECT CASE
WHEN (COUNT(*) = 0) THEN 1
WHEN (string = ?) THEN (SELECT number FROM test WHERE string = ?)
WHEN (MIN(count) = 0) THEN (SELECT number FROM test WHERE count = 0)
ELSE (MAX(number) + 1) END
FROM test

pre tabulku s hodnotami:
1 | aaa | 1
2 | bbb | 1
3 | ccc | 1
4 | ddd | 1

nefunguje spravne, ked pre retazec 'aaa' vrati 5 namiesto 1, ale pre retazec 'ddd' uz vrati spravny vysledok (4)

Dakujem za rady

Pridať komentár

- Re: SQL select case 31.03.2010 | 19:13
  GeoRW Návštevník
  no ta query je nejaka divna; miesas tam groupovacie funkcie (WHEN (COUNT(*) = 0) THEN 1 ) a output pre jednotlive riadky (WHEN (string = ?) THEN (SELECT number FROM test WHERE string = ?)); to by som rad vedel, ze na akej databaze ti tato query zbehne :-)
  
  Neviem, ci sa trafim, ale asi to chces takto
  
  SELECT CASE WHEN (SELECT count(*) FROM "TEST") = 0 THEN 1 WHEN "STRING" = 'aaa' THEN (SELECT "NUMBER" FROM "TEST" WHERE "STRING" = 'aaa') WHEN (SELECT min("COUNT") FROM "TEST") = 0 THEN (SELECT "NUMBER" FROM "TEST" WHERE "COUNT" = 0) ELSE (SELECT max("NUMBER") FROM "TEST") END AS "COLUMN" FROM "TEST"
  
  BTW, pomenovavat stlpce v tabulke tzv. reserved words nie je dobra prax (preto davam TEST, NUMBER, STRING do zatvoriek)
  Odpovedať

Pridať komentár