How to decypher this?

Question

Hi everybody,

I'm trying to figure out what this code may mean

CASE
                          WHEN NULLIF(dbo.RemoveSpecialChars(LTRIM(RTRIM(mcpsl.facility_state_cd))), LEFT(dbo.RemoveSpecialChars(
                                                                                                                            LTRIM(
                                                                                                                                 RTRIM(
                                                                                                                                      mcpsl.facility_zip_code_nbr
                                                                                                                                      )
                                                                                                                                 )
                                                                                                                            ), 5)) IS NOT NULL THEN

Am I correct in my assumption that this could be re-written as

case when facility_state_cd is not null and facility_zip_code_nbr is not null then ...

Probably also need to test for both being blank. I don't see why do we need such complex expressions.

Thanks in advance.

Answer 1

Hi @Naomi
In my opinion，it's not correct.
First， dbo.RemoveSpecialChars may be a UDF which is uesd to remove specified strings as the name suggests.

Second，NULLIF function returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.

So the code can be re-written as :

CASE WHEN dbo.RemoveSpecialChars(LTRIM(RTRIM(mcpsl.facility_state_cd)))  
          <>          
          LEFT(dbo.RemoveSpecialChars(LTRIM(RTRIM(mcpsl.facility_zip_code_nbr))), 5)		   
     THEN ...  

Best regards,
LiHong

Answer 2

You proposed change won't return the same result (depending, of course, on what dbo.RemoveSpecialCharacters does). To see this, run

Create Table #mcpsl(ID int, facility_state_cd varchar(10), facility_zip_code_nbr varchar(10));
Insert #mcpsl Values(1, 'abc', null), (2, 'xyz', 'xyz');
Select
CASE WHEN NULLIF(LTRIM(RTRIM(#mcpsl.facility_state_cd)), LEFT(LTRIM(RTRIM(#mcpsl.facility_zip_code_nbr)), 5)) IS NOT NULL THEN 'True' Else 'False' End As Original,
CASE WHEN #mcpsl.facility_state_cd Is Not Null And #mcpsl.facility_zip_code_nbr IS NOT NULL THEN 'True' Else 'False' End As New
From #mcpsl;

go
Drop Table #mcpsl;

Basically your original code is testing if after the manipulations of the dbo.RemoveSpecialCharacters function and the TRIM functions to see if facility_state_cd <> facility_zip_code_nbr which seems to be an unusual test to be making. Are your sure it shouldn't be using the ISNULL function instead of the NULLIF function?

If you meant to use the ISNULL function, then depending on what dbo.RemoveSpecialCharacters does, then I believe your change would be equivalent. It should work as long as the function returns NULL if and only if the original input was NULL.

Tom

Answer 3

If you want to check that each of the fields (after the manipulation) are not null and not the empty string, you could do

CASE WHEN 
  NULLIF(dbo.RemoveSpecialChars(LTRIM(RTRIM(#mcpsl.facility_state_cd))), '') IS NOT NULL AND

 NULLIF(dbo.RemoveSpecialChars(LEFT(LTRIM(RTRIM(#mcpsl.facility_zip_code_nbr)), 5)), '') IS NOT NULL THEN

For example (without the function call)

Create Table #mcpsl(ID int, facility_state_cd varchar(10), facility_zip_code_nbr varchar(10));
Insert #mcpsl Values(1, 'abc', null), (2, 'xyz', '123'), (3, 'def', ''), (4, NULL, 'qrs'), (5, '', '876');
Select ID, #mcpsl.facility_state_cd, #mcpsl.facility_zip_code_nbr,
CASE WHEN NULLIF(LTRIM(RTRIM(#mcpsl.facility_state_cd)), '') IS NOT NULL AND
  NULLIF(LEFT(LTRIM(RTRIM(#mcpsl.facility_zip_code_nbr)), 5), '') IS NOT NULL THEN 'True' Else 'False' End As Result
From #mcpsl;

go
Drop Table #mcpsl;

Tom

Answer 4

Hi Tom,

I'm thinking that this expression makes little sense as it's written now. The state code is Char(2) and zipcode is varchar(10). RemoveSpecialChars leaves only letters and numbers and removes all other characters (even though I tested the current table and I didn't find anything wrong in the table so it may be an overkill). My assumption is that the real intent of the code is to make sure that both are not null and not blank and then concatenate them together. At least that would make sense based on the whole case statement.

Answer 5

This is the whole case expression, BTW:
case
when nullif(dbo.RemoveSpecialChars(ltrim(rtrim(mcpsl.facility_state_cd))), left(dbo.RemoveSpecialChars(ltrim(rtrim(mcpsl.facility_zip_code_nbr))), 5)) is not null
then concat(upper(dbo.RemoveSpecialChars(ltrim(rtrim(mcpsl.facility_state_cd)))), '-', left(dbo.RemoveSpecialChars(ltrim(rtrim(mcpsl.facility_zip_code_nbr))), 5))
when nullif(dbo.RemoveSpecialChars(ltrim(rtrim(mcpci.service_facility_state_cd))), left(dbo.RemoveSpecialChars(ltrim(rtrim(mcpci.service_facility_zip_code_nbr))), 5)) is not null
then concat(upper(dbo.RemoveSpecialChars(ltrim(rtrim(mcpci.service_facility_state_cd)))), '-', left(dbo.RemoveSpecialChars(ltrim(rtrim(mcpci.service_facility_zip_code_nbr))), 5))
else concat(upper(dbo.RemoveSpecialChars(ltrim(rtrim(mcpbp.billing_provider_state_cd)))), '-', left(dbo.RemoveSpecialChars(ltrim(rtrim(mcpbp.billing_provider_zip_code_nbr))), 5))
end -- AS RNDR_PROV_REG_CD

I think the developer tried to be smart and used 1 function to test for both NULL and blank because obviously state code cannot be equal zipcode (although come to think of it, I did find about 20 bad data rows, but I forgot right now which column I tested - may be I need to re-test one more time). May be it was indeed a double check against bad rows where state accidentally got written into zip code column?

BTW, I tested 2 of the tables used in that case statement and there are no rows currently in production where zipcode is like '%[^0-9-]. So that test doesn't make a lot of sense, it only double checks that the state is not null (and normally if state is not null zipcode is not null too).