CODEWITHSUNDEEP
sqlsql-servert-sql
Bryan Tran
Bryan Tran

Reputation: 143

Nested where statement

Is there a way to structure a WHERE statement to act like this pseudocode? Kind of like a nested WHERE statement?

SELECT
   t.Val1
   ,t.Val2
   ,t.Val3
   ,t.Val4
   ,t.Val5
FROM table t
WHERE
   IF t.Val1 = 'ABC' 
      THEN WHERE t.Val2 = t.Val3
   ELSE
      WHERE t.Val4 = t.Val5

Using MS SQL Server

Upvotes: 2

Views: 49

Answers (3)

Anthony Hancock
Anthony Hancock

Reputation: 931

A third alternative to the other solutions would be IIF expressions.

DROP TABLE IF EXISTS #Test;

CREATE TABLE #Test
(
    TestID TINYINT PRIMARY KEY
    ,Val1 VARCHAR(3)
    ,Val2 INT
    ,Val3 INT
    ,Val4 INT
    ,Val5 INT
)
;
GO

INSERT INTO #Test
VALUES
(1, 'ABC', 1, 1, 1, 2)
,(2, 'ABC', 1, 2, 2, 2)
,(3, 'XYZ', 1, 1, 1, 2)
,(4, 'XYZ', 1, 2, 2, 2)
;

SELECT
    *
FROM #Test
WHERE IIF(Val1 = 'ABC', Val2, Val4) = IIF(Val1 = 'ABC', Val3, Val5)
;

Upvotes: 1

EhSaN kHoSrAvI eSfArJaNi
EhSaN kHoSrAvI eSfArJaNi

Reputation: 56

you can use case statement instead of where condition. even you can to make computed column by case statement like the below link :

https://blog.sqlauthority.com/2016/04/27/sql-server-computed-column-conditions-case-statement/

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Just use boolean logic:

WHERE (t.Val1 = 'ABC' AND t.Val2 = t.Val3) OR
      (t.Val1 <> 'ABC' AND t.Val4 = t.Val5)

This formulation assumes that val1 is never NULL -- NULL values are easily handled, if necessary.

Upvotes: 3

Related Questions