2

I have this query (pseudo code) 

SELECT 
    a = 1,
    b = 2,
    c = CASE 
            WHEN ISNULL(
                         (SELECT MONTH(GETDATE()) <---long query
                         ), 0) = 0 THEN 'found'
            ELSE 
              SELECT MONTH(GETDATE())     <--- repeated long query
        END

The problem is that the SELECT MONTH(GETDATE()) which in reality is very long query.

Is there any workaround for this "long expression" not to appear twice in the query ?

p.s.

I have a solution of calculating SELECT MONTH(GETDATE()) into an outside variable... but I'm trying to figure out if there's an inline solution.

Improve this question
4
  • Have heard about stored procedures and functions? Commented May 17, 2012 at 14:57
  • I think you should post code for your long query showing how you are restricting data to one row. The query above will fail because it will return multiple rows. Commented May 17, 2012 at 15:53
  • @rs it wont fail... it return 1 value foreach row. Commented May 17, 2012 at 18:35
  • I would probably use cross apply if your query always return one row and outer apply if it returns zero or one row. Commented May 18, 2012 at 9:57

4 Answers 4

Reset to default
5

You have a number of options available:

  • User-defined function (UDF)
  • Views
  • Common Table Expression (CTE)
  • CROSS/OUTER APPLY

Depending on how the data is being processed will determine which is the best fit. Your question doesn't provide enough detail to definitively offer a suggestion, but these are worth looking at.

Be careful with using UDF as they can quickly hinder performance. My person rule of thumb is to write a UDF for simple data transformations or mathematical calculation. I try to avoid doing complicated set-based operations in a function and will prefer a view or CTE for that.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

how will cte help here ? and UDF is not an options - as you said - bad performance. the only option is view... but i was hoping for some other idea. i guess view is the only option left...( as i was thinking)
What I use are CTE's also, the optimizer can (usually) work with them fine, just double check the plan before releasing it for production.
2

The particular pattern of usage of a subquery in your example could be re-written like this:

…
c = COALESCE(CAST(NULLIF((subquery), 0) AS varchar(10)), 'found')
…

As Mikael Eriksson has correctly pointed out in his comment, you may need to use ISNULL instead of COALESCE here, because one of the arguments contains a subquery and ISNULL may (or, in fact, will) be more efficient in this case, as you can see elaborated in this answer.

so here is the fixed ver : 

…
c = ISNULL(CAST(NULLIF((subquery), 0) AS varchar(10)), 'found')
…

Note, though, that if you end up switching to ISNULL, you should pay attention to the length of the second string ('found') if you ever decide to change it too. The thing is, ISNULL will (attempt to) cast the second argument to the exact type of the first argument, including the maximum length of the string specified (10 in this case). If your second argument becomes something like 'NULL or zero is found' instead of just 'found', ISNULL will not return the entire string but only the first 10 characters instead ('NULL or ze'). So you'll need to remember to change the type of the first argument as well (to varchar(20), for instance).

Improve this answer

3 Comments

we both knew exactly what ive been searching .
I think this answer is applicable here. Probably should use isnull instead of coalesce to avoid executing the sub query twice.
@RoyiNamir: Please have a look at my update, just so you were aware of an option (probably, a better one) here.
1 
SELECT 
    a = 1,
    b = 2,
    c = CASE 
            WHEN ISNULL([l], 0) = 0 THEN 'found'
            ELSE [l]
        END
from tbl
cross apply (
SELECT MONTH(GETDATE()) as [l]
) as [x]

Also, if your original query is representative of what you've actually got (i.e. returning one value if the "long expression" returns null), consider using COALESCE instead of a case statement. Or just use ISNULL([l], 'found')

Improve this answer

Comments

-1

Example using CTE

;WITH CTE AS
(
    SELECT CASE WHEN ISNULL(MONTH(GETDATE()),0) = 0 
    THEN 'FOUND' ELSE  ISNULL(MONTH(GETDATE()),0) END Col1
),
SELECT a = 1,b = 2, C = col1
FROM Table1, CTE

if you have any keys, that can be used to join

SELECT a = 1,b = 2, C = CASE WHEN ISNULL(v.col1,0) = 0 THEN 'Found' ELSE v.col1 END
        FROM Table1 a
OUTER APPLY (SELECT ISNULL(MONTH(DateCol),0) Col1 FROM TABLEName b
                a.id = b.id) v
Improve this answer

6 Comments

did you read my problem ? ( MONTH(GETDATE()) is still calculated twice here)
do you have any id or unique key that you use in your subquery to get month?
what if GetDate() is a date Column in my Table? how will i "send" it to the CTE ?
IMHO it is very bad performance- since the from x,x the cartessian multiplation is not neccesary and it_ gives me a lot of unwanted combinations...._
yes, for same reason i asked you if there are any keys that you use to join on, if yes then we can do this differently.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.