SQL select case

Sekcia: Programovanie 20.03.2010 | 20:27
Juro   Návštevník
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