SQL Query Advice

I need some SQL advice. I formatted a field as such:

ISNULL(LEFT(ic3.ListName,25),'                         ') AS [Third Payer] ,

The intent was if NULL then produce 25 blank space characters. I need to have it like this for a specific report. If any of my values is NULL then X number of characters. Am I correct in this?
SELECT 
		LEFT(f.FederalTaxId , 10) AS FacilityId ,
    		RIGHT('00000000000000000' + pp.PatientId, 17) AS PatientControlNumber ,
		RIGHT('00000000000000000' + COALESCE(pp.MedicalRecordNumber, '0'), 17) AS PatientMRN ,
    		CASE WHEN pp.SSN IS NULL THEN '300'
    		     ELSE LEFT(pp.SSN , 9)
    		END AS SSN ,
    		LEFT(pp.Zip , 5) AS Zip ,
    		REPLACE(CONVERT(VARCHAR(10) , pp.birthdate , 101) , '/' , '') AS DOB ,
    		pp.Sex ,
    		CASE  	
			WHEN pvf.HospitalizationFrom IS NULL THEN REPLACE(CONVERT(VARCHAR(10) , pvp.DateOfServiceFrom , 1) , '/' , '') 
			ELSE REPLACE(CONVERT(VARCHAR(10) , pvf.HospitalizationFrom , 1) , '/' , '')
		END AS AdmissionDate ,
		ISNULL(ml.Code,'9') AS [Source of Admission] , 
		ISNULL(mll.Code,'09') AS PatientDischargeStatus ,
		CASE
			WHEN pvf.HospitalizationTo IS NULL THEN REPLACE(CONVERT(VARCHAR(10) , pvp.DateOfServiceTo , 1) , '/' , '')
			ELSE REPLACE(CONVERT(VARCHAR(10) , pvf.HospitalizationTo , 1) , '/' , '') 
		END AS DischargeDate ,
		ISNULL(LEFT(replace(pvd1.ICD9Code , '.' , '') , 5), '     ') AS PrimaryDiag ,
		ISNULL(LEFT(replace(pvd2.ICD9Code , '.' , '') , 5), '     ') AS SecondDiag ,
		ISNULL(LEFT(replace(pvd3.ICD9Code , '.' , '') , 5), '     ') AS ThirdDiag ,
		ISNULL(LEFT(replace(pvd4.ICD9Code , '.' , '') , 5), '     ') AS FourthDiag ,
		ISNULL(LEFT(replace(pvd5.ICD9Code , '.' , '') , 5), '     ') AS FifthDiag ,
		ISNULL(LEFT(replace(pvd6.ICD9Code , '.' , '') , 5), '     ')AS SixthDiag ,
		ISNULL(LEFT(replace(pvd7.ICD9Code , '.' , '') , 5), '     ') AS SeventhDiag ,
		ISNULL(LEFT(replace(pvd8.ICD9Code , '.' , '') , 5), '     ') AS EighthDiag ,
		ISNULL(LEFT(replace(pvd9.ICD9Code , '.' , '') , 5), '     ') AS NinthDiag ,
		ISNULL(LEFT(pvp.CPTCode, 5) + ISNULL(CONVERT(varchar(4), ml4.Code), '  ') + ISNULL(CONVERT(varchar(4), ml5.Code), ' '),'         ') AS PrincipleCode , 
		ISNULL(LEFT(pvp2.CPTCode, 5) + ISNULL(CONVERT(varchar(4), ml6.Code), '  ') + ISNULL(CONVERT(varchar(4), ml7.Code), ' '),'         ') AS SecondCode ,  
		ISNULL(LEFT(pvp3.CPTCode, 5) + ISNULL(CONVERT(varchar(4), ml8.Code), '  ') + ISNULL(CONVERT(varchar(4), ml9.Code), ' '), '         ') AS ThirdCode ,
		ISNULL(LEFT(pvp4.CPTCode, 5) + ISNULL(CONVERT(varchar(4), ml10.Code), '  ') + ISNULL(CONVERT(varchar(4), ml11.Code), ' '), '         ') AS FourthCode ,
		ISNULL(LEFT(pvp5.CPTCode, 5) + ISNULL(CONVERT(varchar(4), ml12.Code), '  ') + ISNULL(CONVERT(varchar(4), ml13.Code), ' '),'         ') AS FifthCode ,
		ISNULL(LEFT(pvp6.CPTCode, 5) + ISNULL(CONVERT(varchar(4), ml14.Code), '  ') + ISNULL(CONVERT(varchar(4), ml15.Code), ' '),'         ') AS SixthCode ,
		REPLACE(CONVERT(VARCHAR(10) , pvp.DateOfServiceFrom , 1) , '/' , '') AS [Primary CPT Date] ,
		'4' as [Coding Method Used] ,
		REPLACE(CONVERT(VARCHAR(10) , pvp.DateOfServiceFrom , 1) , '/' , '') + REPLACE(CONVERT(VARCHAR(10) , pvp.DateOfServiceTo , 1) , '/' , '')  AS [Statement Covers Period] ,
		/*pva.OrigInsAllocation ,
		pva.OrigPatAllocation ,*/
    		pva.OrigInsAllocation + pva.OrigPatAllocation AS [Total Charges] ,
		ISNULL(LEFT(ic.ListName,25),'                         ') AS [Primary Payer] , 
		ISNULL(LEFT(ic2.ListName,25),'                         ') AS [Secondary Payer] ,
		ISNULL(LEFT(ic3.ListName,25),'                         ') AS [Third Payer] ,
		ISNULL(LEFT(od.StateLicenseNo,12),'            ') AS [Performing Physician ID] ,
		ISNULL(LEFT(oth.StateLicenseNo,12),'            ') AS [Other Physician ID] ,
		'999' AS [Type of Bill] ,
		ISNULL(LEFT(replace(pvd1.ICD9Code , '.' , '') , 5), '     ') AS Primary_Diag ,
		ISNULL(LEFT(replace(pvd2.ICD9Code , '.' , '') , 5), '     ') AS Second_Diag ,
		ISNULL(LEFT(replace(pvd3.ICD9Code , '.' , '') , 5), '     ') AS Third_Diag ,
		ISNULL(LEFT(replace(pvd4.ICD9Code , '.' , '') , 5), '     ') AS Fourth_Diag ,
		ISNULL(LEFT(replace(pvd5.ICD9Code , '.' , '') , 5), '     ') AS Fifth_Diag ,
		ISNULL(LEFT(replace(pvd6.ICD9Code , '.' , '') , 5), '     ') AS Sixth_Diag 
 
FROM
    		PatientVisit pv
 
Rest of my query ............. (not important for this question).

Open in new window

LVL 7
Jeff SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
it seems ok
do you have any problem with it?
0
Jeff SAuthor Commented:
momi sabag -
Yes, when i get it into crystal reports it seems to ignore it. I just wanted to double check my SQL.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Why is it like this:

ISNULL(ic3.ListName,25,'                         ') AS [Third Payer] ,

If it can be NULL, then LEFT() is just a overhead.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Meant "Why is it like that" and the modified one below
0
Jeff SAuthor Commented:
rrjegan17 -
confused, how should it be?
0
pssandhuCommented:
Are you writing this as a SQL Command in crystal reports or part of stored procdure that you will be calling?
Either way you should take care of this in crystal because crystal I think won't retain all the blanks. What I would do is in your sql query default the null values to some thing unique: ISNULL(ic3.ListName,'xxxxxxxxxxxxxxxxx') AS [Third Payer] and in the the crystal instead of just pulling the database field create a formula to evaluate the field and then print the results. Something like this:
WhilePritingRecords;
Stringvar val;
If {TableName.ListName} = "xxxxxxxxxxxxxxxx" Then "                         "
Else {TableName.ListName}
This is untested but hopefully this should work. Are you trying to print blanks in Crystal or using crystal to export date?
P.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> confused, how should it be?

It should be like

ISNULL(ic3.ListName,25,'                         ') AS [Third Payer]

ignoring the LEFT() function.
0
quzzieCommented:
I guess what no-one seems to be asking is why you need 25 spaces in your report ?  Is it for some form of padding \ indent in the report ?  if so then I am sure there are probably internal reporting options in the report that you could use to carry out the formatting.  Please could you clarify the need in the report for the spaces ?

0
Jeff SAuthor Commented:
pssandhu -
I like your way of handling this ... I am going to try that.
 
quzzie -
Yes the padding is needed. Its a goofy state required report and this is how they want it. I don't like it but thats government for you!
0
Mark WillsTopic AdvisorCommented:
rrjegan17 left the 25 in there... s/b ISNULL(ic3.ListName,'                         ') AS [Third Payer] ,

reality is, if you really want 25 character lengths, then use Crystal to format, or, have to make it a char (ie trailing spaces)

convert(char(25),isnull(ic3.listname,'')) as [third player],


0
quzzieCommented:
I think in crystal there is a space function which you can use.  you might wanna look into that.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
mark_wills and quzzie,
   I am not that familiar with Crystal Reports Functions.
If you have formatting function for that, then it can be implemented for better performance of the query.

I have one question here..

Does formatting all records (assuming its huge) will be done faster compared to the SQL query approach which is mentioned above.

Kindly confirm
0
Mark WillsTopic AdvisorCommented:
Well, to be perfectly honest, it is all a bit moot. Because the overall size is "drawn" and depends on things like variable font (versus fixed font), "can grow" and other properties for the field once placed on a report. But, one thing is for sure, unless you tell it in SQL it is a fixed length type field like CHAR, crystal will interpret as STRING (same as it will for varchar), except varchar will have variable length unless explicitly padded with spaces. So, unless being exported, the trailing spaces are not entirely important...
0
Mark WillsTopic AdvisorCommented:
Well, one advantage doing it in crystal is that it is only dealing with the data it is reporting on. Ideally it only selects the data that it wants to report on, and at a detail level is probably not much difference - there is obviously extra bandwidth with spaces, but, if the source / origins of the data is always presented the correcct way, then it doesn't matter if it is Crystal or Excel then.


try this quick quiz in a query window...


declare @v1 varchar(20)
declare @v2 char(20)
declare @v3 varchar(20)

set @v1 = '12345'
set @v2 = '12345'
set @v3 = '12345               '

select '|'+@v1+'|' as v1,'|'+@v2+'|' as v2,'|'+@v3+'|' as v3, '|'+convert(char(25),@v1)+'|' as cv1
select datalength(@v1),datalength(@v2),datalength(@v3), datalength(convert(char(25),@v1))
0
Mark WillsTopic AdvisorCommented:
Jeff, you might be making a lot of work for yourself, did you try the CHAR mode ? Takes care of variable lengths as well...

Don't get me wrong though, I am sure pssandhu's solution will work, just seems that if you do not have to double handle, then dont...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.