CODEWITHSUNDEEP
sqlsql-servercase
xandi1987
xandi1987

Reputation: 519

Better use of redundant subquery in sql- case

I have a question for SQL- Statements: Is it possible to "define a sub query" for multiple use in case. It sounds a bit confusing but with the following example I think it is clear what I have in mind:

select 
 Column1,
 Column2,
 Case
    WHEN <BigSubquery> > 0 THEN <BigSubquery>
    ELSE 0
 END
from ...

How can I do this, or what can I use. I have such a query which works wonderful, but it is a huge code and not useable for maintenance.

Upvotes: 0

Views: 248

Answers (2)

Phil
Phil

Reputation: 42991

If you rewrite your query as 

select 
   Column1,
   Column2,
   Case
       WHEN Column3 > 0 THEN Column3
       ELSE 0
   END
from 
(
    select 
        Column1,
        Column2,
        BigSubquery as Column3
    from ...
)
t

Then you avoid duplicating "BigSubquery", but you do duplicate the select list.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If you are using a subquery, you should put the condition in the subquery. For instance, if you have:

(select sum(x) from . . . )

Then do:

(select (case when sum(x) > 0 then sum(x) else 0 end) from . . .

Upvotes: 1

Related Questions