CODEWITHSUNDEEP
sql-server
Sumit Gupta
Sumit Gupta

Reputation: 569

How can query be optimized using CASE in SQL

I have a query with Union statements, just to check like case but i want to implement CASE in Where, but i am beginner to SQL and dont know how can i do that.

My Query is like

SELECT TOP 1 EndDate
           FROM   (
                      SELECT 1 AS seq,

                             EndDate,

                      FROM   pipeline_rest_envr_info e
                      WHERE  e.tckt_id = imt.tckt_id
                             AND stat = 'INPR'
                      UNION
                      SELECT TOP 1 2 AS seq,

                             EndDate,

                      FROM   pipeline_rest_envr_info e
                      WHERE  e.tckt_id = imt.tckt_id
                             AND stat = 'CMPL'
                      ORDER BY
                             enddate DESC
                      UNION
                      SELECT TOP 1 3 AS seq,

                             EndDate,

                      FROM   pipeline_rest_envr_info e
                      WHERE  e.tckt_id = imt.tckt_id
                             AND stat = 'PLND'
                      ORDER BY
                             strt_dt
                      UNION 
                      SELECT 4 AS seq,

                             NULL,

                      FROM   pipeline_rest_envr_info e
                      WHERE  e.tckt_id = imt.tckt_id
                  ) aa
           ORDER BY
                  aa.seq

Upvotes: 0

Views: 96

Answers (3)

NG.
NG.

Reputation: 6053

Avoid implementation of CASE in WHERE. Instead apply it in SELECT. Further you have not mentioned the table representing the alias IMT

SELECT TOP 1 EndDate
FROM (
select
(
case when stat = 'INPR' then 1
when stat = 'CMPL' then 2
when stat = 'PLND' then 3
else 4 end
) as seq,EndDate
from pipeline_rest_envr_info e
inner join whateverthetablenameis imt on e.tckt_id = imt.tckt_id
) aa
ORDER BY aa.seq

Upvotes: 0

Vikram Jain
Vikram Jain

Reputation: 5588

SELECT TOP 1 EndDate
FROM (
select (case when stat = 'INPR' then 1
when stat = 'CMPL' then 2
when stat = 'PLND' then 3
else 4 end
) as seq,EndDate
from pipeline_rest_envr_info e
left outer join table2name imt on e.tckt_id = imt.tckt_id
) aa
ORDER BY
aa.seq

Upvotes: 2

Vikram Jain
Vikram Jain

Reputation: 5588

SELECT TOP 1 aa.*
FROM (
select (case when condition1 then result1
when condition2 then result2
...
else
default_result
end ) as result,... from tablename
) aa
ORDER BY
aa.seq

Upvotes: 0

Related Questions