0

I have made a view in which i use function to get a column something like that

dbo.GetFinalLetter(Id, CASE 
        WHEN Id IN (
                ---------
                )
            THEN 1
        WHEN Id IN (
                --------
                )
            THEN 0
        END) as LetterText

so i want to put an other condition that only those results been shown that have text in that column for that purpose i used

where LetterText <> null

where LetterText not null

but it shows error

1
  • Select clause column aliases are cannot be used in where clauses. You'll need to either repeat the function in the where clause or encapsulate the select statement in a CTE. Also, use IS NOT NULL to check for NULL values.
    – Dan Guzman
    Commented Nov 6, 2015 at 12:04

1 Answer 1

1

You can try to move your function call to an OUTER/CROSS APPLY and then use the APPLY result to check your value:

SELECT <your columns>
FROM <your tables>
    CROSS APPLY 
    (
        SELECT dbo.GetFinalLetter(Id, CASE 
        WHEN Id IN (
                ---------
                )
            THEN 1
        WHEN Id IN (
                --------
                )
            THEN 0
        END) as LetterText
    ) AS x
WHERE x.LetterText IS NOT NULL ;

OR you can also use a subquery of the full query:

SELECT <your columns>
FROM 
(
    SELECT <your columns>,
        dbo.GetFinalLetter(Id, CASE 
        WHEN Id IN (
                ---------
                )
            THEN 1
        WHEN Id IN (
                --------
                )
            THEN 0
        END) as LetterText
    FROM <your tables>
) AS t
WHERE LetterText IS NOT NULL ;

The way to do that may depend on the data volume of the tables.

Hope this helps.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.