Case fromula is wrong syntax 1

[Ladyazh]

I have a field that can contain data or NULL value.
So all I need is to say when it is NULL - display 'Non Paid'
and when it is Not Null display 'Paid'

SELECT DISTINCT 'STATUS' = CASE PMTSATAS
WHEN NULL THEN 'NOT PAID'
WHEN Not IsNULL THEN 'PAID'
END
from dbo.ASI_ESP_INCENTIVE_1ST_VW

So far it is showing error at NOT.
Please, check it out for me

 

[gmmastros]

[COLOR=blue]SELECT[/color] [COLOR=#FF00FF]DISTINCT[/color] [COLOR=red]'STATUS'[/color] = [COLOR=blue]CASE[/color] [COLOR=blue]When[/color] PMTSATAS [COLOR=blue]Is[/color] NULL
[COLOR=blue]THEN[/color] [COLOR=red]'NOT PAID'[/color] 
[COLOR=blue]else[/color] [COLOR=red]'PAID'[/color]
[COLOR=blue]END[/color]
[COLOR=blue]from[/color] dbo.ASI_ESP_INCENTIVE_1ST_VW

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Ladyazh]

Hooray! Thanks

 

[SQLBill]

The error was with ISNULL. ISNULL requires an expression. for example: ISNULL(column_name).

You probably meant WHEN NOT NULL......


-SQLBill


Posting advice: FAQ481-4875

 

[gmmastros]

Bill,

There are 2 methods that can be used with the Case statement.

[COLOR=blue]Select[/color] [COLOR=blue]Case[/color] ColumnName [COLOR=blue]When[/color] 1 [COLOR=blue]Then[/color] [COLOR=red]'One'[/color]
                       [COLOR=blue]When[/color] 2 [COLOR=blue]then[/color] [COLOR=red]'Two'[/color]
                       [COLOR=blue]Else[/color] [COLOR=red]'Greater Than 2'[/color]
                       [COLOR=blue]End[/color] [COLOR=blue]As[/color] ColumnName

[COLOR=blue]Select[/color] [COLOR=blue]Case[/color] [COLOR=blue]When[/color] ColumnName = 1 [COLOR=blue]Then[/color] [COLOR=red]'One'[/color]
            [COLOR=blue]When[/color] ColumnName = 2 [COLOR=blue]Then[/color] [COLOR=red]'Two'[/color]
            [COLOR=blue]Else[/color] [COLOR=red]'Not 1 or two'[/color]
            [COLOR=blue]End[/color]

When comparing to null, you MUST use the 2nd method.

[COLOR=green]-- This Works
[/color][COLOR=blue]Declare[/color] @i [COLOR=blue]int[/color]
[COLOR=blue]Set[/color] @i = NULL

[COLOR=blue]Select[/color] [COLOR=blue]Case[/color] [COLOR=blue]When[/color] @i [COLOR=blue]Is[/color] NULL [COLOR=blue]Then[/color] [COLOR=red]'Value is NULL'[/color]
            [COLOR=blue]Else[/color] [COLOR=red]'Value Is not NULL'[/color]
            [COLOR=blue]End[/color]

[COLOR=green]-- This does not work
[/color][COLOR=blue]Declare[/color] @i [COLOR=blue]int[/color]
[COLOR=blue]Set[/color] @i = NULL

[COLOR=blue]Select[/color] [COLOR=blue]Case[/color] @i [COLOR=blue]When[/color] [COLOR=blue]Is[/color] NULL [COLOR=blue]Then[/color] [COLOR=red]'Value Is NULL'[/color]
            [COLOR=blue]Else[/color] [COLOR=red]'Value Is not NULL'[/color]
            [COLOR=blue]End[/color]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom