T-SQL

Re: CASE in WHERE

ssandu — Thu, 03 Jul 2008 09:44:23 GMT

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

Re: CASE in WHERE

rsocol — Thu, 03 Jul 2008 09:32:06 GMT

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

Re: CASE in WHERE

B_gd_n[ ]Sahlean — Thu, 03 Jul 2008 08:22:54 GMT

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())

Re: CASE in WHERE

ignatandrei — Thu, 03 Jul 2008 08:19:48 GMT

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()

Re: CASE in WHERE

ssandu — Thu, 03 Jul 2008 08:19:00 GMT

Ma gandeam sa nu mai fie nevoie de inca un tabel

Re: CASE in WHERE

B_gd_n[ ]Sahlean — Thu, 03 Jul 2008 08:14:30 GMT

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())

CASE in WHERE

ssandu — Thu, 03 Jul 2008 07:51:01 GMT

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 ?