Solved

# What Does ISNULL Mean in this CASE Statement?

Posted on 2016-11-01
65 Views
I'm trying to understand the logic behind this CASE Statement:

`````` CASE
WHEN view_txn.type = 'C'
THEN claimid + ' Charge - ' + ISNULL(cptdesc,'')
WHEN view_txn.type = 'A'
THEN claimid + ' Adjustment - ' + ISNULL(payerdesc,'')
WHEN view_txn.type = 'P' and payer = 'I'
THEN claimid + ' Payment - ' + ISNULL(payerdesc,'')
WHEN paymentmethod = 'DS'
THEN claimid + ' Discount - ' + ISNULL(payerdesc,'')
WHEN paymentmethod = 'RV'
THEN claimid + ' Payment Reversal - ' + ISNULL(payerdesc,'')
WHEN paymentmethod = 'RF'
THEN claimid + ' Refund - ' + ISNULL(payerdesc,'')
WHEN paymentmethod = 'AJ'
THEN claimid + ' Adjustment - ' + ISNULL(payerdesc,'')
ELSE 'Payment - ' + ISNULL(payerdesc,'')
END as description,
``````

It seems like "description" is going to be a conjugated string that will consist of the claim id plus a keyword (Adjustment, Refund, etc) and...

What does ISNULL mean?
0
Question by:brucegust
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2
• 2
• +3

LVL 74

Assisted Solution

sdstuber earned 125 total points
ID: 41868717
if the first parameter is null, then replace it with an empty string.

otherwise, when you did the concatenation, the entire result would be a null.

'x' + null  = null

'x' + '' = 'x'
0

LVL 4

Assisted Solution

Steven Kribbe earned 125 total points
ID: 41868718
ISNULL (exp1, exp2) returns expr if exp1 is null
0

LVL 65

Accepted Solution

Jim Horn earned 250 total points
ID: 41868719
>What does ISNULL mean?
Pick the first value that is a non-NULL.

ISNULL(NULL, 'banana') will return 'banana'.
ISNULL('Ted Nugent', NULL) will return 'Ted Nugent'.
ISNULL(42, 'Purple Dinosaur') will return 42.

It's a quick and dirty way to replace NULL values with something else in a SELECT clause.
To do this with more than two values use COALESCE.
0

LVL 28

Expert Comment

ID: 41868786
In your query if the column value is NULL it is replaced by blank value that is '' <<Single Quotes>>

ISNULL(cptdesc,'')   -- now if the value of cptdesc is NULL then ''.

Now why they are doing is because they have to concat.

NULL + 'a'  Gives NULL thats why. [SQL Server Internally works like this.]

Hope it helps.
0

LVL 74

Expert Comment

ID: 41868821

Everything you posted was already in the very first post above and expanded on in followup posts.
0

LVL 28

Expert Comment

ID: 41868858
This happens if you dont refresh the page before posting. @Author - Ignore my comment.
0

LVL 65

Expert Comment

ID: 41868884
@Pawan - Looking at the timestamps it appears you had the question open for almost 40 minutes before posting your comment, which seems unlikely.
0

LVL 13

Expert Comment

ID: 41868988
@Jim (sometimes I leave the question open while I work on an answer.  I try and refresh periodically so I don't waste my time, but I've accidentally done the same)
0

LVL 65

Expert Comment

ID: 41868997
Dustin - That's also why I type real fast on easy questions, as in this case in the time it took me to come up with my witty Ted Nugent and Purple Dinosaur analogies two experts posted correct answers.
0

Author Closing Comment

ID: 41869006
Thank you!
0

LVL 13

Expert Comment

ID: 41869007
I see that!  You could probably speed that up with a pop culture reference generating SP in your test database.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Moving away from Access 2003 adp files 4 48
Report 8 27
sql server string_split 4 28
t-sql left join 2 34
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in beâ€¦
Why is this different from all of the other step by step guides? Â Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff lâ€¦
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
###### Suggested Courses
Course of the Month5 days, 8 hours left to enroll