Not recognising NULL in formula

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Not recognising NULL in formula

Author

Topic

robtop
Starting Member

9 Posts

Posted - 2011-08-17 : 10:06:32

Afternoon,

I have the following formula:

CASE WHEN LedgerTransactions.PolicyEndDate IS NULL THEN 'Not CXD Mid Term' ELSE 'CXD Mid Term' END

I though this was working as expected but followign some checking it seems to be ignoring all the values which have a PolicyEndDate and putting everything as 'Not CXD Mid Term'. I've tried a couple of variations using Not Null and when PolicyEndDate > 0 but these don't seem to function correctly either.

Has anyone got any what I'm doing wrong as no one in my company seems to have a clue?

Thanks very much

Rob

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-17 : 11:07:52

Seems to work just fine. Can you provide sample data that doesn't work?

Here is a quick sample that shows it works as I expect it to:

DECLARE @LedgerTransactions TABLE (ID INT IDENTITY(1,1), PolicyEndDate DATE)

INSERT @LedgerTransactions (PolicyEndDate)
VALUES 
    (CURRENT_TIMESTAMP), 
    (DATEADD(DAY, -1, CURRENT_TIMESTAMP)),
    (DATEADD(DAY, -2, CURRENT_TIMESTAMP)),
    (DATEADD(DAY, -3, CURRENT_TIMESTAMP)),
    (NULL),
    (DATEADD(DAY, -4, CURRENT_TIMESTAMP)),
    (DATEADD(DAY, -5, CURRENT_TIMESTAMP)),
    (DATEADD(DAY, -6, CURRENT_TIMESTAMP)),
    (NULL),
    (DATEADD(DAY, -7, CURRENT_TIMESTAMP))
    
SELECT
    *,
    CASE WHEN LedgerTransactions.PolicyEndDate IS NULL THEN 'Not CXD Mid Term' ELSE 'CXD Mid Term' END
FROM
    @LedgerTransactions AS   LedgerTransactions

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources