CODEWITHSUNDEEP
sql-server
tylkonachwile
tylkonachwile

Reputation: 2267

How to avoid CTE in query?

I prepared a sample table, to demonstrate what I want to acheive.

drop table #Temp
create table #Temp
(
    col1 nvarchar(50),
    col2 nvarchar(50),
    col3 nvarchar(50)
)
insert into #temp (col1,col2,col3) values (null, null, 'W')

How to avoid this cte and have the same result?

with cte as (
select COALESCE(col1,col2,col3) as result from #temp
)
select * from cte where result is not null

Upvotes: 1

Views: 393

Answers (2)

Squirrel
Squirrel

Reputation: 24792

For your query, it is equivalent as following 2 query

select  COALESCE(col1,col2,col3) as result
from    #Temp
where   COALESCE(col1,col2,col3) is not null

or

select  * 
from 
(
    select COALESCE(col1,col2,col3) as result from #Temp
) as D
where   result is not null

Upvotes: 1

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

SQL Fiddle

MS SQL Server 2017 Schema Setup:

create table Temp
(
    col1 nvarchar(50),
    col2 nvarchar(50),
    col3 nvarchar(50)
)
insert into temp (col1,col2,col3) values (null, null, 'W')
insert into temp (col1,col2,col3) values (null, null, null)
insert into temp (col1,col2,col3) values ('A', 'B', 'W')

Query 1:

select * from temp
where COALESCE(col1,col2,col3) IS NOT NULL

Query 2:

select CASE WHEN  COALESCE(col1,col2,col3) IS NULL THEN 'Undefined' ELSE
COALESCE(col1,col2,col3) END from temp

Results:

|   col1 |   col2 | col3 |
|--------|--------|------|
| (null) | (null) |    W |
|      A |      B |    W |

Upvotes: 1

Related Questions