• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 90
  • 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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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