CODEWITHSUNDEEP
sqlsql-servernullcase
tember
tember

Reputation: 1496

SQL Case statement returns wrong value

I have the following sql statement which is designed to give the field t1.DWPostedDateKey a value if the t2.InventoryStatusCode changes to 'p'. The starting value of t1.DWPostedDateKey is NULL, and if the t2.InventoryStatusCode doesn't change, I want it stay NULL.

select
t1.DateKey,
t1.DWPostedDateKey,
(CASE WHEN t2.InventoryStatusCode = 'p' 
             and (t1.InventoryStatusKey != t2.InventoryStatusKey) THEN 
     CONVERT(int, GetDate(), 112)
ELSE 
     t1.DWPostedDateKey
END)
from table1 t1 inner join
   table2 t2 on t1.key = t2.key

The problem is I don't get NULL ? Here is an example of the results:

DateKey     DWPostedDateKey   (No column name)
20150413    NULL              42106
20150413    NULL              42106
20150413    NULL              42106
20150413    20150414          20150414
20150413    20150414          20150414
20150413    20150414          20150414

What is 42106 doing in there? I want it to remain a NULL value.

Upvotes: 0

Views: 686

Answers (2)

Joeri
Joeri

Reputation: 225

Your SQL code works correctly as far as I can test it. It seems to be in your data that you're getting a int value instead of a NULL value.

It would help if you post the rows from table t1 and t2 here that are the base for this resultset.

However, when I reproduce it, I can only get it to produce an int in the third column when you change the value of 'InventoryStatus' in t2.

declare @table1 table (
    [key] int,
    DateKey nvarchar(8),
    DWPostedDateKey nvarchar(8),
    InventoryStatusKey int
)

declare @table2 table (
    [key] int,
    InventoryStatusKey int,
    InventoryStatusCode nvarchar(1)
)

insert into @table1 values (1, N'20150413', NULL, 1)
insert into @table1 values (2, N'20150413', NULL, 1)
insert into @table1 values (3, N'20150413', NULL, 1)
insert into @table1 values (4, N'20150413', N'20150414', 1)
insert into @table1 values (6, N'20150413', N'20150414', 1)

insert into @table2 values (1, 1, 'a')
insert into @table2 values (1, 2, 'a')
insert into @table2 values (3, 2, 'p') 
insert into @table2 values (4, 2, 'a')
insert into @table2 values (6, 1, 'a')

select
    t1.DateKey,
    t1.DWPostedDateKey,
    (CASE WHEN 
        t2.InventoryStatusCode = 'p' 
        and (t1.InventoryStatusKey != t2.InventoryStatusKey) 
     THEN 
        CONVERT(int, GetDate(), 112)
ELSE 
     t1.DWPostedDateKey
END)
from @table1 t1 inner join
   @table2 t2 on t1.[key] = t2.[key]

I change the value of the InventoryStatusCode from 'a' to 'p' and the result it what you're getting.

Upvotes: 0

CrApHeR
CrApHeR

Reputation: 2655

The problem is this line CONVERT(int, GetDate(), 112), specifically in the int.

It is converting the datetime to int and SQL Server when you do that is returning the number of days since 1/1/1900. (That is the number you see)

This will return what you need
CONVERT(varchar, GetDate(), 112)

if you need an integer try with this
CAST(CONVERT(varchar, GetDate(), 112) AS INTEGER)

Hope this helps

Upvotes: 1

Related Questions