CODEWITHSUNDEEP
sqldb2case
JPK
JPK

Reputation: 1364

DB2 CASE WHEN THEN adding two extra nulls to all values

I got a strange question sent to me today from a junior programmer.

In a DB2 SQL statement he switched a 

select [Value]

The old result was

AAAAAA
CCCCCC
BBBBBB
DDDDDDDDDDD
EE

to 

select 
CASE [Value] 
  WHEN 'AAAAAA'     
    THEN 'AAAA'          
  WHEN 'BBBBBB'                  
    THEN 'BBBBBBBB'          
  ELSE [Value]                
  END

The new result is 

..AAAA............
..CCCCCC
..BBBBBBBB........
..DDDDDDDDDDD
..EE

where . represents a null

I want to understand what caused two extra nulls to appear in the nonselected values?

Upvotes: 0

Views: 423

Answers (1)

Charles
Charles

Reputation: 23783

What DB2 version and platform?
Is this statement being run directly in a SQL client interface? Or is it in a stored procedure whose results is being interpreted by something?

To me, it looks the data type being returned has been changed from fixed length CHAR to VARCHAR and whatever is reading the results is still processing it as CHAR.

String literals are always interpreted as VARCHAR.

If that's the case, either correct the calling app or convert the VARCHAR to CHAR. 

select 
CASE [Value] 
  WHEN 'AAAAAA'     
    THEN CHAR('AAAA', 30)          
  WHEN 'BBBBBB'                  
    THEN CHAR('BBBBBBBB', 30)          
  ELSE [Value]                
END

Upvotes: 1

Related Questions