Solved

Blank space where value is NULL

Posted on 2014-10-28
10
111 Views
Last Modified: 2014-10-28
I have a report with the following code entered for reporting insurance information.

CASE WHEN AI.InsuranceSeqID = '1' then AI.InsuredPolicyNumber END AS 'PolicyNumber1',
                  CASE WHEN AI.InsuranceSeqID = '2' then AI.InsuredPolicyNumber END AS 'PolicyNumber2',
                        CASE WHEN AI.InsuranceSeqID = '3' then AI.InsuredPolicyNumber END AS 'PolicyNumber3',
                              CASE WHEN AI.InsuranceSeqID = '4' then AI.InsuredPolicyNumber END AS 'PolicyNumber4',

When I run the report, if the patient does not have an AI.InsuranceSeqID of 2,3,or 4, it is returning NULL in the column.  I would like this to be blank if there is no policy number for this SeqID,

It currently prints.  PolicyNumber1   PolicyNumber2 PolicyNumber3  PolicyNumber4
                                  t1234567890        NULL                   NULL                    NULL

I want:                     PolicyNumber1   PolicyNumber2 PolicyNumber3  PolicyNumber4
                                 1234567890
0
Comment
Question by:kvrogers
  • 2
  • 2
  • 2
  • +3
10 Comments
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 500 total points
ID: 40408647
Replace with a blank using isnull()

isnull(CASE WHEN AI.InsuranceSeqID = '1' then AI.InsuredPolicyNumber END, '') AS 'PolicyNumber1',
isnull(CASE WHEN AI.InsuranceSeqID = '2' then AI.InsuredPolicyNumber END, '') AS 'PolicyNumber2',
isnull(CASE WHEN AI.InsuranceSeqID = '3' then AI.InsuredPolicyNumber END, '') AS 'PolicyNumber3',
isnull(CASE WHEN AI.InsuranceSeqID = '4' then AI.InsuredPolicyNumber END, '') AS 'PolicyNumber4',

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40408653
To convert NULL to an empty space '' in T-SQL, use either ISNULL(column_name, '') or COALESCE(column_name, '').

Since you're doing this for reporting, it might be more appropriate to pull this off in the reporting layer, and not the data set.  Your call.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40408655
Untested, but you should be able to do:

isnull(CASE WHEN AI.InsuranceSeqID = '1' then AI.InsuredPolicyNumber END, ' ') AS 'PolicyNumber1',
isnull(CASE WHEN AI.InsuranceSeqID = '2' then AI.InsuredPolicyNumber END, ' ') AS 'PolicyNumber2', 
isnull(CASE WHEN AI.InsuranceSeqID = '3' then AI.InsuredPolicyNumber END, ' ') AS 'PolicyNumber3', 
isnull(CASE WHEN AI.InsuranceSeqID = '4' then AI.InsuredPolicyNumber END, ' ') AS 'PolicyNumber4', 

Open in new window

ISNULL is a valid function for both MySQL and SQL Server (since you tagged both in your question).
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40408793
Insert before each END

ELSE ''
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 31

Expert Comment

by:awking00
ID: 40408869
CASE WHEN AI.InsuranceSeqID = '1' then coalesce(AI.InsuredPolicyNumber,'') END AS 'PolicyNumber1',
CASE WHEN AI.InsuranceSeqID = '2' then coalesce(AI.InsuredPolicyNumber,'') END AS 'PolicyNumber2',
CASE WHEN AI.InsuranceSeqID = '3' then coalesce(AI.InsuredPolicyNumber,'') END AS 'PolicyNumber3',
CASE WHEN AI.InsuranceSeqID = '4' then coalesce(AI.InsuredPolicyNumber,'') END AS 'PolicyNumber4',

Open in new window

0
 

Author Comment

by:kvrogers
ID: 40409175
Thank you for your prompt reply.  This worked perfectly.
0
 
LVL 31

Expert Comment

by:awking00
ID: 40409231
>>This worked perfectly<<
And Lee Savidge's didn't?
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40409263
Points should really have gone to Lee - his was the exact same response as mine.  Filing Request for attention for Moderators to review.
0
 

Author Comment

by:kvrogers
ID: 40409317
I started from the last entry which was in my email.  That one worked.  I am sorry I missed Lee's.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now