Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 92
  • Last Modified:

What Does ISNULL Mean in this CASE Statement?

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,

Open in new window


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
brucegust
Asked:
brucegust
  • 3
  • 2
  • 2
  • +3
3 Solutions
 
sdstuberCommented:
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
 
Steven KribbeSoftware EngineerCommented:
ISNULL (exp1, exp2) returns expr if exp1 is null
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Pawan KumarDatabase ExpertCommented:
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
 
sdstuberCommented:
Pawan Kumar Khowal, please read the thread prior to posting.

Everything you posted was already in the very first post above and expanded on in followup posts.
0
 
Pawan KumarDatabase ExpertCommented:
This happens if you dont refresh the page before posting. @Author - Ignore my comment.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
@Pawan - Looking at the timestamps it appears you had the question open for almost 40 minutes before posting your comment, which seems unlikely.
0
 
Dustin SaundersDirector of OperationsCommented:
@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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
brucegustPHP DeveloperAuthor Commented:
Thank you!
0
 
Dustin SaundersDirector of OperationsCommented:
I see that!  You could probably speed that up with a pop culture reference generating SP in your test database.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now