SQL Select statement returning NULL

Question

I have the following CASE statement in my SELECT clause:

       CASE HHHCRIN
       WHEN 'Y' THEN HHHINVN ELSE 'N/A'
       END AS "Credit Memo Document Number",

Can someone tell me why I get a NULL rather than N/A?

HHHCRIN is 'Y' or 'N', HHHINVN is defined as S 7,0 it is an invoice number. — Dan B., Apr 14 '22 at 13:19
Ok, what i'm getting at is, a) could HHHCRIN be null? or b) could HHHCRIN = Y, but HHHINVN is null, so your case would be working? — Nathan_Sav, Apr 14 '22 at 13:21
HHHINVN always has a value, if HHHCRIN is 'Y' I want to show a blank rather than the invoice number. — Dan B., Apr 14 '22 at 13:23
Well you have the case the wrong way round then `when 'y' then '' else HHINVN` — Nathan_Sav, Apr 14 '22 at 13:25
To clarify, I tried a blank at first and when that didn't work I tried setting the value to N/A. — Dan B., Apr 14 '22 at 13:26
look at https://www.w3schools.com/sql/func_sqlserver_isnull.asp — Nathan_Sav, Apr 14 '22 at 13:58
https://stackoverflow.com/questions/65071/isnull-function-in-db2-sql — Nathan_Sav, Apr 14 '22 at 14:10

score 0 · Accepted Answer · answered Apr 14 '22 at 18:58

0

from the comments

HHHCRIN is 'Y' or 'N', HHHINVN is defined as S 7,0 it is an invoice number.

You can't return a string, be it blanks or 'N/A' when the return column is numeric.

Since Db2 can't implicitly convert 'N/A' to a number, you get null.

Try returning all strings...

   CASE HHHCRIN
   WHEN 'Y' THEN char(HHHINVN) ELSE 'N/A'
   END AS "Credit Memo Document Number",

answered Apr 14 '22 at 18:58

Charles

Thank you Charles! That worked, I really appreciate your help. – Dan B. Apr 15 '22 at 12:39
Sorry Charles, I am relatively new to this, what is an up vote and how would I do that? – Dan B. Apr 15 '22 at 14:15
@DanB. click the up arrow next to the answer. Have you taken the [tour](https://stackoverflow.com/tour)? – Charles Apr 15 '22 at 14:17
1

I did that but I get a message about not having enough reputation to cast a vote. – Dan B. Apr 15 '22 at 14:19

1 Answers1