JMS49 JMS49 - 1 month ago 21
SQL Question

TSQL CASE LTRIM (RTRIM NULL

SQL Syntax is still something I am learning. I am getting the error noted below the this snippet of code.

SELECT
CASE WHEN LTRIM(RTRIM(cLehmanNo)) =' ' THEN NULL
WHEN cLehmanNo IS NOT NULL THEN REPLACE ( cLehmanNo,SUBSTRING (cLehmanNo,PATINDEX( '%[^a-zA-Z0-9 '''''']%',cLehmanNo),1), ' ' )
END asLOAN_NUMBER
,CASE WHEN LTRIM(RTRIM(cMERS)) =' ' THEN NULL
WHEN cMERS IS NOT NULL THEN REPLACE ( cMERS,SUBSTRING (cMERS,PATINDEX( '%[^a-zA-Z0-9 '''''']%',cMERS),1), ' ' )
END asMERS_ID


and 100+ more of same.

Msg 8133, Level 16, State 1, Line 1
None of the result expressions in a CASE specification can be NULL.


What am I doing wrong? How do I keep the gist of the statement and not get this crazy error?

Answer

This happens when it can't infer the type.

e.g.

SELECT CASE WHEN 1 = 2 THEN NULL ELSE NULL END 

But this works

SELECT CASE WHEN 1 = 2 THEN NULL ELSE replace(NULL,'','') END

so I doubt the error is from the code you have shown us (You are using string functions and the following quick test shows that it will assume that to be varchar(8000))

SELECT CASE WHEN 1 = 2 THEN NULL ELSE REPLACE(NULL,'','') END a
INTO t /*Creates column of datatype varchar(8000)*/