SQL Switch Case in where clause

Question

I tried searching around  but I couldn t find anything that would help me out   I m trying to do this in SQL   declare  locationType varchar 50   declare  locationID int   SELECT column1  column2 FROM viewWhatever WHERE CASE  locationType     WHEN  location  THEN account location    locationID     WHEN  area  THEN xxx location area    locationID     WHEN  division  THEN xxx location division    locationID   I know that I shouldn t have to put     locationID  at the end of each one  but I can t get the syntax even close to being correct  SQL keeps complaining about my     on the first WHEN line     How can I do this

User · Answer

The problem with this is that when the SQL engine goes to evaluate the expression, it checks the FROM portion to pull the proper tables, and then the WHERE portion to provide some base criteria, so it cannot properly evaluate a dynamic condition on which column to check against.

You can use a WHERE clause when you're checking the WHERE criteria in the predicate, such as

WHERE account_location = CASE @locationType
                              WHEN 'business' THEN 45
                              WHEN 'area' THEN 52
                         END

so in your particular case, you're going to need put the query into a stored procedure or create three separate queries.

User · Answer

Try this   WHERE        smartLocationType IS NULL      OR account location              CASE             WHEN  smartLocationType IS NOT NULL                   THEN  smartLocationType             ELSE account location           END

User · Answer

Case Statement in SQL Server Example  Syntax  CASE   expression       WHEN condition 1 THEN result 1    WHEN condition 2 THEN result 2           WHEN condition n THEN result n     ELSE result  END  Example  SELECT contact id  CASE website id   WHEN 1 THEN  TechOnTheNet com    WHEN 2 THEN  CheckYourMath com    ELSE  BigActivities com  END FROM contacts   OR  SELECT contact id  CASE   WHEN website id   1 THEN  TechOnTheNet com    WHEN website id   2 THEN  CheckYourMath com    ELSE  BigActivities com  END FROM contacts

User · Answer

I d say this is an indicator of a flawed table structure  Perhaps the different location types should be separated in different tables  enabling you to do much richer querying and also avoid having superfluous columns around   If you re unable to change the structure  something like the below might work   SELECT       FROM     Test WHERE     Account Location             CASE LocationType           WHEN  location  THEN  locationID           ELSE Account Location         END           AND     Account Location Area             CASE LocationType           WHEN  area  THEN  locationID           ELSE Account Location Area         END         And so forth    We can t change the structure of the query on the fly  but we can override it by making the predicates equal themselves out   EDIT  The above suggestions are of course much better  just ignore mine

User · Answer

Try this query  Its very easy to understand   CREATE TABLE PersonsDetail FirstName nvarchar 20   LastName nvarchar 20   GenderID int   GO  INSERT INTO PersonsDetail VALUES N Gourav   N Bhatia   2                  N Ramesh   N Kumar   1                  N Ram   N Lal   2                  N Sunil   N Kumar   3                  N Sunny   N Sehgal   1                  N Malkeet   N Shaoul   3                  N Jassy   N Sohal   2   GO  SELECT FirstName  LastName  Gender       CASE GenderID     WHEN 1 THEN  Male      WHEN 2 THEN  Female      ELSE  Unknown      END FROM PersonsDetail

User · Answer

declare  locationType varchar 50   declare  locationID int   SELECT column1  column2 FROM viewWhatever WHERE  locationID      CASE  locationType       WHEN  location  THEN account location       WHEN  area  THEN xxx location area        WHEN  division  THEN xxx location division    END

User · Answer

OR operator can be alternative of case when in where condition  ALTER PROCEDURE  dbo   RPT 340bClinicDrugInventorySummary      -- Add the parameters for the stored procedure here       ClinicId BIGINT   0        selecttype int        selectedValue varchar  50  AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements  SET NOCOUNT ON  SELECT     drugstock drugname n cur bal drugname cdrugname clinic cclinicname  FROM drugstock drugname INNER JOIN drugname ON drugstock drugname drugnameid FK   drugname drugnameid PK INNER JOIN drugstock drugndc ON drugname drugnameid PK   drugstock drugndc drugnameid FK INNER JOIN drugndc ON drugstock drugndc drugndcid FK   drugndc drugid PK LEFT JOIN clinic ON drugstock drugname clinicid FK   clinic clinicid PK  WHERE     ClinicId   0 AND 1   1      OR    ClinicId    0 AND drugstock drugname clinicid FK    ClinicId       -- Alternative Case When You can use OR     AND    selecttype   1 AND 1   1      OR    selecttype   2 AND drugname drugnameid PK    selectedValue      OR    selecttype   3 AND drugndc drugid PK    selectedValue      OR    selecttype   4 AND drugname cdrugclass    C2       OR    selecttype   5 AND LEFT drugname cdrugclass  1     C     ORDER BY clinic cclinicname  drugname cdrugname END

User · Answer

The problem with this is that when the SQL engine goes to evaluate the expression, it checks the FROM portion to pull the proper tables, and then the WHERE portion to provide some base criteria, so it cannot properly evaluate a dynamic condition on which column to check against.

You can use a WHERE clause when you're checking the WHERE criteria in the predicate, such as

WHERE account_location = CASE @locationType
                              WHEN 'business' THEN 45
                              WHEN 'area' THEN 52
                         END

so in your particular case, you're going to need put the query into a stored procedure or create three separate queries.

User · Answer

without a case statement     SELECT column1  column2 FROM viewWhatever WHERE       locationType    location  AND account location    locationID      OR       locationType    area  AND xxx location area    locationID      OR       locationType    division  AND xxx location division    locationID

User · Answer

I d say this is an indicator of a flawed table structure  Perhaps the different location types should be separated in different tables  enabling you to do much richer querying and also avoid having superfluous columns around   If you re unable to change the structure  something like the below might work   SELECT       FROM     Test WHERE     Account Location             CASE LocationType           WHEN  location  THEN  locationID           ELSE Account Location         END           AND     Account Location Area             CASE LocationType           WHEN  area  THEN  locationID           ELSE Account Location Area         END         And so forth    We can t change the structure of the query on the fly  but we can override it by making the predicates equal themselves out   EDIT  The above suggestions are of course much better  just ignore mine

User · Answer

I d say this is an indicator of a flawed table structure  Perhaps the different location types should be separated in different tables  enabling you to do much richer querying and also avoid having superfluous columns around   If you re unable to change the structure  something like the below might work   SELECT       FROM     Test WHERE     Account Location             CASE LocationType           WHEN  location  THEN  locationID           ELSE Account Location         END           AND     Account Location Area             CASE LocationType           WHEN  area  THEN  locationID           ELSE Account Location Area         END         And so forth    We can t change the structure of the query on the fly  but we can override it by making the predicates equal themselves out   EDIT  The above suggestions are of course much better  just ignore mine

User · Answer

CREATE PROCEDURE  dbo   Temp Proc Select City       StateId INT AS           BEGIN                    SELECT   FROM tbl City                  WHERE                   StateID   CASE WHEN ISNULL  StateId 0    0 THEN 0 ELSE StateId END ORDER BY CityName         END

User · Answer

OR operator can be alternative of case when in where condition  ALTER PROCEDURE  dbo   RPT 340bClinicDrugInventorySummary      -- Add the parameters for the stored procedure here       ClinicId BIGINT   0        selecttype int        selectedValue varchar  50  AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements  SET NOCOUNT ON  SELECT     drugstock drugname n cur bal drugname cdrugname clinic cclinicname  FROM drugstock drugname INNER JOIN drugname ON drugstock drugname drugnameid FK   drugname drugnameid PK INNER JOIN drugstock drugndc ON drugname drugnameid PK   drugstock drugndc drugnameid FK INNER JOIN drugndc ON drugstock drugndc drugndcid FK   drugndc drugid PK LEFT JOIN clinic ON drugstock drugname clinicid FK   clinic clinicid PK  WHERE     ClinicId   0 AND 1   1      OR    ClinicId    0 AND drugstock drugname clinicid FK    ClinicId       -- Alternative Case When You can use OR     AND    selecttype   1 AND 1   1      OR    selecttype   2 AND drugname drugnameid PK    selectedValue      OR    selecttype   3 AND drugndc drugid PK    selectedValue      OR    selecttype   4 AND drugname cdrugclass    C2       OR    selecttype   5 AND LEFT drugname cdrugclass  1     C     ORDER BY clinic cclinicname  drugname cdrugname END

User · Answer

Try this query  Its very easy to understand   CREATE TABLE PersonsDetail FirstName nvarchar 20   LastName nvarchar 20   GenderID int   GO  INSERT INTO PersonsDetail VALUES N Gourav   N Bhatia   2                  N Ramesh   N Kumar   1                  N Ram   N Lal   2                  N Sunil   N Kumar   3                  N Sunny   N Sehgal   1                  N Malkeet   N Shaoul   3                  N Jassy   N Sohal   2   GO  SELECT FirstName  LastName  Gender       CASE GenderID     WHEN 1 THEN  Male      WHEN 2 THEN  Female      ELSE  Unknown      END FROM PersonsDetail

User · Answer

without a case statement     SELECT column1  column2 FROM viewWhatever WHERE       locationType    location  AND account location    locationID      OR       locationType    area  AND xxx location area    locationID      OR       locationType    division  AND xxx location division    locationID

User · Answer

Try this query  it s very easy and useful  Its ready to execute  USE tempdb GO  IF NOT OBJECT ID  Tempdb  Contacts   IS NULL     DROP TABLE Contacts  CREATE TABLE Contacts ID INT  FirstName VARCHAR 100   LastName VARCHAR 100   INSERT INTO Contacts  ID  FirstName  LastName  SELECT 1   Omid    Karami  UNION ALL SELECT 2   Alen    Fars  UNION ALL SELECT 3   Sharon    b  UNION ALL SELECT 4   Poja    Kar  UNION ALL SELECT 5   Ryan    Lasr  GO   DECLARE  FirstName VARCHAR 100  SET  FirstName    Omid    DECLARE  LastName VARCHAR 100  SET  LastName         SELECT FirstName  LastName FROM Contacts WHERE       FirstName   CASE     WHEN LEN  FirstName   gt  0 THEN   FirstName      ELSE FirstName      END AND     LastName   CASE     WHEN LEN  LastName   gt  0 THEN   LastName      ELSE LastName     END GO

User · Answer

Please try this query  Answer To above post   select  msgID  account id     from viewMailAccountsHeirachy     where      CASE  smartLocationType         WHEN  store  THEN account location         WHEN  area  THEN xxx location area          WHEN  division  THEN xxx location division          WHEN  company  THEN xxx location company      END     smartLocation

User · Answer

The problem with this is that when the SQL engine goes to evaluate the expression, it checks the FROM portion to pull the proper tables, and then the WHERE portion to provide some base criteria, so it cannot properly evaluate a dynamic condition on which column to check against.

You can use a WHERE clause when you're checking the WHERE criteria in the predicate, such as

WHERE account_location = CASE @locationType
                              WHEN 'business' THEN 45
                              WHEN 'area' THEN 52
                         END

so in your particular case, you're going to need put the query into a stored procedure or create three separate queries.

User · Answer

declare  locationType varchar 50   declare  locationID int   SELECT column1  column2 FROM viewWhatever WHERE  locationID      CASE  locationType       WHEN  location  THEN account location       WHEN  area  THEN xxx location area        WHEN  division  THEN xxx location division    END

User · Answer

declare  locationType varchar 50   declare  locationID int   SELECT column1  column2 FROM viewWhatever WHERE  locationID      CASE  locationType       WHEN  location  THEN account location       WHEN  area  THEN xxx location area        WHEN  division  THEN xxx location division    END

User · Answer

Try this   WHERE        smartLocationType IS NULL      OR account location              CASE             WHEN  smartLocationType IS NOT NULL                   THEN  smartLocationType             ELSE account location           END

User · Answer

Please try this query  Answer To above post   select  msgID  account id     from viewMailAccountsHeirachy     where      CASE  smartLocationType         WHEN  store  THEN account location         WHEN  area  THEN xxx location area          WHEN  division  THEN xxx location division          WHEN  company  THEN xxx location company      END     smartLocation

User · Answer

The problem with this is that when the SQL engine goes to evaluate the expression, it checks the FROM portion to pull the proper tables, and then the WHERE portion to provide some base criteria, so it cannot properly evaluate a dynamic condition on which column to check against.

You can use a WHERE clause when you're checking the WHERE criteria in the predicate, such as

WHERE account_location = CASE @locationType
                              WHEN 'business' THEN 45
                              WHEN 'area' THEN 52
                         END

so in your particular case, you're going to need put the query into a stored procedure or create three separate queries.

User · Answer

declare  locationType varchar 50   declare  locationID int   SELECT column1  column2 FROM viewWhatever WHERE  locationID      CASE  locationType       WHEN  location  THEN account location       WHEN  area  THEN xxx location area        WHEN  division  THEN xxx location division    END

User · Answer

Here you go   SELECT    column1      column2 FROM    viewWhatever WHERE CASE      WHEN  locationType    location  AND account location    locationID THEN 1     WHEN  locationType    area  AND xxx location area    locationID THEN 1     WHEN  locationType    division  AND xxx location division    locationID THEN 1     ELSE 0 END   1

User · Answer

Here you go   SELECT    column1      column2 FROM    viewWhatever WHERE CASE      WHEN  locationType    location  AND account location    locationID THEN 1     WHEN  locationType    area  AND xxx location area    locationID THEN 1     WHEN  locationType    division  AND xxx location division    locationID THEN 1     ELSE 0 END   1

User · Answer

Here you go   SELECT    column1      column2 FROM    viewWhatever WHERE CASE      WHEN  locationType    location  AND account location    locationID THEN 1     WHEN  locationType    area  AND xxx location area    locationID THEN 1     WHEN  locationType    division  AND xxx location division    locationID THEN 1     ELSE 0 END   1

User · Answer

I d say this is an indicator of a flawed table structure  Perhaps the different location types should be separated in different tables  enabling you to do much richer querying and also avoid having superfluous columns around   If you re unable to change the structure  something like the below might work   SELECT       FROM     Test WHERE     Account Location             CASE LocationType           WHEN  location  THEN  locationID           ELSE Account Location         END           AND     Account Location Area             CASE LocationType           WHEN  area  THEN  locationID           ELSE Account Location Area         END         And so forth    We can t change the structure of the query on the fly  but we can override it by making the predicates equal themselves out   EDIT  The above suggestions are of course much better  just ignore mine

User · Answer

Here you go   SELECT    column1      column2 FROM    viewWhatever WHERE CASE      WHEN  locationType    location  AND account location    locationID THEN 1     WHEN  locationType    area  AND xxx location area    locationID THEN 1     WHEN  locationType    division  AND xxx location division    locationID THEN 1     ELSE 0 END   1

User · Answer

CREATE PROCEDURE  dbo   Temp Proc Select City       StateId INT AS           BEGIN                    SELECT   FROM tbl City                  WHERE                   StateID   CASE WHEN ISNULL  StateId 0    0 THEN 0 ELSE StateId END ORDER BY CityName         END

User · Answer

Try this query  it s very easy and useful  Its ready to execute  USE tempdb GO  IF NOT OBJECT ID  Tempdb  Contacts   IS NULL     DROP TABLE Contacts  CREATE TABLE Contacts ID INT  FirstName VARCHAR 100   LastName VARCHAR 100   INSERT INTO Contacts  ID  FirstName  LastName  SELECT 1   Omid    Karami  UNION ALL SELECT 2   Alen    Fars  UNION ALL SELECT 3   Sharon    b  UNION ALL SELECT 4   Poja    Kar  UNION ALL SELECT 5   Ryan    Lasr  GO   DECLARE  FirstName VARCHAR 100  SET  FirstName    Omid    DECLARE  LastName VARCHAR 100  SET  LastName         SELECT FirstName  LastName FROM Contacts WHERE       FirstName   CASE     WHEN LEN  FirstName   gt  0 THEN   FirstName      ELSE FirstName      END AND     LastName   CASE     WHEN LEN  LastName   gt  0 THEN   LastName      ELSE LastName     END GO

User · Answer

Case Statement in SQL Server Example  Syntax  CASE   expression       WHEN condition 1 THEN result 1    WHEN condition 2 THEN result 2           WHEN condition n THEN result n     ELSE result  END  Example  SELECT contact id  CASE website id   WHEN 1 THEN  TechOnTheNet com    WHEN 2 THEN  CheckYourMath com    ELSE  BigActivities com  END FROM contacts   OR  SELECT contact id  CASE   WHEN website id   1 THEN  TechOnTheNet com    WHEN website id   2 THEN  CheckYourMath com    ELSE  BigActivities com  END FROM contacts

[sql] SQL Switch/Case in 'where' clause

Examples related to sql

Examples related to switch-statement

Examples related to case