Case in select statement 1

[smn198]

I currently have the following case in my select statement:

[COLOR=red]SELECT
    CASE[/color] "Master"."InsFreq"
        [COLOR=red]WHEN[/color] 1 [COLOR=red]THEN[/color] 12
        [COLOR=red]WHEN[/color] 2 [COLOR=red]THEN[/color] 6
        [COLOR=red]WHEN[/color] 3 [COLOR=red]THEN[/color] 3
        [COLOR=red]WHEN[/color] 4 [COLOR=red]THEN[/color] 2
        [COLOR=red]WHEN[/color] 5 [COLOR=red]THEN[/color] "Master"."SchedSpacing"
    [COLOR=red]ELSE[/color] -1 [COLOR=red]END AS[/color] Freq ...

I want to do another more complicated one but I cannot get it working. I have two dates. If date A is greater or equal to date B then I want date B to be null. I cannot seem to get comparasoms with other values to work. How would I write the select to make this work. Not sure if it makes any difference but both of the dates are MAX()s.

TIA

 

[JarlH]

Something like:

SELECT CASE WHEN A >= B THEN NULL
WHEN A = xyz THEN A
ELSE -1 END
FROM sometable

 

[smn198]

Thanks for that but no luck...

This is what I have in my select statement now:

SELECT
    MAX("GiftPayment"."DTE") AS LastPaymentDate,
    CASE 
        WHEN MAX("Reminder1st"."ATTRIBUTEDATE") < MAX("GiftPayment"."DTE") THEN NULL
    ELSE MAX("Reminder1st"."ATTRIBUTEDATE") AS Last1stReminderDate,
    CASE "GiftMaster"."INSTALLMENT_FREQUENCY"
        WHEN 1 THEN 12
        WHEN 2 THEN 6
        WHEN 3 THEN 3
        WHEN 4 THEN 2
        WHEN 5 THEN "GiftMaster"."Schedule_Spacing"
    ELSE -1 END AS Frequency,

I get the error incorrect syntaxs near AS and then it complains about things down in the join which had been working fine.

 

[r937]

you forgot the keyword END

rudy

http://r937.com/

SQL Consulting

 

[smn198]

Thanks, I must be too tired...

 

[smn198]

This has an unexpected side effect. When the Last1stPaymentReminderDate is set to null by the case statement, that row is excluded from the results. How do I keep them?

 

[PHV]

Why not post the WHOLE select instruction with sample input data, expected result and explanation of the unexpected behaviour ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Why not post the WHOLE select instruction with sample input data, expected result and explanation of the unexpected behaviour ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[smn198]

Thanks very much everyone. Have sorted it out. It was actaully being filtered by something in Crystal.