asked on

STill getting NULL in data pull

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'

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

You also need to make sure you are dealing with NULL values and not an empty string.

Mark Edwards

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 Mandayam

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 Edwards

ASKER

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.