Welcome to Sign in | Help
in Search

CASE in WHERE

Last post 07-03-2008, 12:44 PM by ssandu. 6 replies.
Sort Posts: Previous Next
  •  07-03-2008, 10:51 AM 5242

    CASE in WHERE

    Cum pot sa fac urmatorul select (acum da eroare)
    SELECT * from tabel
    WHERE utilizator IN
    CASE
    WHEN USER_NAME() = 'user1' THEN ('user1', 'user2')
    WHEN USER_NAME() = 'user3' THEN ('user3', 'user2')
    DEFAULT USER_NAME()
    END

    Se poate ?


    Sorin Sandu
  •  07-03-2008, 11:14 AM 5243 in reply to 5242

    Re: CASE in WHERE

    ssandu:
    Cum pot sa fac urmatorul select (acum da eroare)
    SELECT * from tabel
    WHERE utilizator IN
    CASE
    WHEN USER_NAME() = 'user1' THEN ('user1', 'user2')
    WHEN USER_NAME() = 'user3' THEN ('user3', 'user2')
    DEFAULT USER_NAME()
    END

    Se poate ?


    Poti pleca de la urmatoarea solutie (ce-i bazaconia aia cu DEFAULT ?):

    CREATE TABLE ListaUtilizatori
    (
    Utilizator VARCHAR(25),
    Lista VARCHAR(25)
    );
    GO

    INSERT INTO ListaUtilizatori VALUES('user1','user1')
    INSERT INTO ListaUtilizatori VALUES('user1','user2')
    INSERT INTO ListaUtilizatori VALUES('user3','user3')
    INSERT INTO ListaUtilizatori VALUES('user3','user2')
    GO


    SELECT *
    FROM tabel
    WHERE utilizator IN (SELECT Lista FROM ListaUtilizatori WHERE Utilizator = USER_NAME())

  •  07-03-2008, 11:19 AM 5244 in reply to 5243

    Re: CASE in WHERE

    Ma gandeam sa nu mai fie nevoie de inca un tabel

    Sorin Sandu
  •  07-03-2008, 11:19 AM 5245 in reply to 5242

    Re: CASE in WHERE

    Workaround :

    select * from tabel
    where USER_NAME() = 'user1' and utilizator in ('user1', 'user2')
    union all
    select * from tabel
    where USER_NAME() = 'user2' and utilizator in ('user3', 'user2')
    union all
    select * from tabel
    where utilizator  = USER_NAME()





    Ignat Andrei
    http://serviciipeweb.ro/iafblog
  •  07-03-2008, 11:22 AM 5246 in reply to 5244

    Re: CASE in WHERE

    ssandu:
    Ma gandeam sa nu mai fie nevoie de inca un tabel

    SELECT *
    FROM tabel
    WHERE utilizator IN
      (
      SELECT Lista
      FROM
        (
        SELECT 'user1' AS Utilizator, 'user1' AS Lista
        UNION
        SELECT 'user1' AS Utilizator, 'user2' AS Lista
        UNION
        SELECT 'user3' AS Utilizator, 'user3' AS Lista
        UNION
        SELECT 'user3' AS Utilizator, 'user2' AS Lista
        ) AS Q
      WHERE Utilizator = USER_NAME())

  •  07-03-2008, 12:32 PM 5247 in reply to 5245

    Re: CASE in WHERE

    Sau altă variantă (ceva mai eficientă decât cea a lui Andrei şi probabil mai aproape de rezultatul dorit):

    select * from tabel
    where USER_NAME() = 'user1' and utilizator in ('user1', 'user2')
    or USER_NAME() = 'user2' and utilizator in ('user3', 'user2')
    or USER_NAME() not in ('user1','user2') and utilizator = USER_NAME()

    Răzvan
  •  07-03-2008, 12:44 PM 5249 in reply to 5247

    Re: CASE in WHERE

    Deoarece am cam multe variante am adoptat varianta cu un tabel separat.
    Va multumesc ptr. raspunsuri

    Sorin Sandu
View as RSS news feed in XML
Powered by Community Server (Commercial Edition), by Telligent Systems