STill getting NULL in data pull

Becky Edwards
Becky Edwards used Ask the Experts™
on
I am using Sql Server Management Studio V17.4 to write a query.  Not Oracle db.

The code I am using should take into account if there is a NULL value in the field, and return either a three digit code from one of two places, and if not then '001'.

For some reason I am still getting a NULL value in that field for one account from 2019.
Can you tell anything by just reading the SQL?  

What information would you need in order to tell me where I am missing something?

Code:
,CASE WHEN ACCOUNT.ACCOUNT_TYPE_C IS NULL THEN  RIGHT('000' + CAST(PAT_ACCT_CVG.ACCOUNT_TYPE_C AS VARCHAR(18)),3)
WHEN ACCOUNT.ACCOUNT_TYPE_C IS NOT NULL THEN RIGHT('000' + CAST(ACCOUNT.ACCOUNT_TYPE_C AS VARCHAR(18)),3)
 ELSE '001' END
 AS 'GuarantorType'
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
It's all about "code flow" in the CASE expression. When one condition is met, then the result is calculated and the ELSE is irrelevant.

Thus when ACCOUNT.ACCOUNT_TYPE_C IS NULL the expression evaluates PAT_ACCT_CVG.ACCOUNT_TYPE_C, which can be NULL also. In this case your result is NULL.

If I understand your intention correctly, then you need COALESCE() instead of CASE:

RIGHT(
    '000' + CAST(
        COALESCE( ACCOUNT.ACCOUNT_TYPE_C, PAT_ACCT_CVG.ACCOUNT_TYPE_C, 1 ) AS NVARCHAR(255)
    ), 
    3 
)  AS 'GuarantorType'

Open in new window

As you're on SQL Server 2012+ you can "simplify" it using FORMAT():

FORMAT(
    COALESCE( ACCOUNT.ACCOUNT_TYPE_C, PAT_ACCT_CVG.ACCOUNT_TYPE_C, 1 ), 
    '000'
)  AS 'GuarantorType' 

Open in new window

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
You also need to make sure you are dealing with NULL values and not an empty string.
Mark EdwardsChief Technology Officer

Commented:
Your query is not lying to you.  If it's not giving you what you are expecting for what YOU think is a null, then it's not really a null you're encountering - it's something else that LOOKS LIKE an empty record field, but it really contains an invisible character such as an empty string, a carriage return/line feed, space, Asc(0) character, etc.  This is a common issue with text data types.

Investigate and find out what's really there.  It may be a case of needing data cleanup.

One trick is to SELECT DISTINCT and filter for non-null values and sort ascending and seeing what the first few and last few values are.  You may be surprised at what you find.
Partha MandayamTechnical Director

Commented:
PAT_ACCT_CVG.ACCOUNT_TYPE_C may be null hence you're getting null. You need to use coalesce(<expr>, ' ') to avoid the nulls
Also your case expression is wrongly written
It should be
,CASE WHEN ACCOUNT.ACCOUNT_TYPE_C IS NULL THEN  coalesce(RIGHT('000' + CAST(PAT_ACCT_CVG.ACCOUNT_TYPE_C AS VARCHAR(18)),3), ' ')

 ELSE '001' END
 AS 'GuarantorType'

Either it is null or the 'else' condition applies. You can't have null, not null and else.
Becky EdwardsEpic Clarity Developer

Author

Commented:
Thank you everyone.  Ste5an your code worked perfectly.  I will do further testing but I think this is going to be my solution going forward.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial