T-SQL and SSRS:  Length of Text

TBSupport
TBSupport used Ask the Experts™
on
Hello:

Below is a snippet of my programming.  Please review the final field called "dfpin".

This programming is embedded in a view that is used by my SQL Reporting Services (SSRS) report.  

When I run the report, I generate it to a tab-delimited file.  Since Equifax requires that there be no special tabs, I use Find and Replace to remove said tabs.

In any case, here's the problem.  The attached Equifax file is a zipped Notepad file.  dfpin is to begin at position 392 in Notepad.  Instead, it is starting at position 380.  I'm twelve characters short.  Why is that?

An example of dfpin is in line three and begins with the phrase on that line "92081900".

It is incredibly important for Equifax that I get the positions of these fields correct.  I'm very surprised that the programming is not doing this.

Please help!

Thanks!

TBSupport


SELECT     'MED' AS company, CAST('202EMPLPIM' AS CHAR(15)) AS rectype,
CAST('16860' AS CHAR(16)) AS cocode,
CAST(MED.dbo.UPR00100.SOCSCNUM AS CHAR(11))
AS ssn,
CAST(MED.dbo.UPR00100.EMPLOYID AS CHAR(64)) AS empl_id,
CAST(MED.dbo.UPR00100.SOCSCNUM AS CHAR(64)) AS user_id,
CAST('A868' AS CHAR(4))
AS inhousenum,
CONVERT(VARCHAR(8), MAX(MED.dbo.UPR00900.LSTPCKDT), 112) AS pasofdate,
CAST(MED.dbo.UPR00100.FRSTNAME AS CHAR(64)) AS fn,
                      CAST(MED.dbo.UPR00100.MIDLNAME AS CHAR(64)) AS mn,
                      CAST(MED.dbo.UPR00100.LASTNAME AS CHAR(64)) AS ln,
                      CAST('' AS CHAR(12)) AS suffix,
                      CAST('' AS CHAR(5)) AS title,
                      CAST(RTRIM(CAST(RIGHT(MED.dbo.UPR00100.SOCSCNUM, 10) AS VARCHAR(10))) + CAST(YEAR(MED.dbo.UPR00100.BRTHDATE) AS VARCHAR(4)) AS CHAR(8))
                      AS dfpin
Equifax.zip
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
if concat null yields null is on in your database (which can be checked by running the following:
select [name], is_concat_null_yields_null_on
FROM master.sys.databases
ORDER BY [name]

You may have nulls in the data and concatenating them together in your last field may  result in null.


Also check your other fields for null. I think casting a null to char still results in null.
this can be seen with: Select len(cast(null as char(4)))

use Isnull(MyField,'') to eliminate any null values that could cause you formatting issues.


-also for troubleshooting, why not kick the report out of SSRS in csv, that way you can verify each field's length separated by a visible delimiter.
GanapathiFacets Developer

Commented:
There seems to be a logic error in the SELECT clause while determining "dfpin".

From this,
RTRIM(CAST(RIGHT(MED.dbo.UPR00100.SOCSCNUM, 10) AS VARCHAR(10)))
you are taking the last 10 digits from the SOCSCNUM which is correct.

and

From this,
CAST(YEAR(MED.dbo.UPR00100.BRTHDATE) AS VARCHAR(4))
you are identifying only the YEAR which is of 4 digits. This is also correct.

But you went wrong in "AS CHAR(8))  dfpin". You will get 10+4 chars initially and you are casting to 8 chars. You lost 6 chars here.

Run this query and see the difference.

SELECT CAST(RTRIM(CAST(RIGHT(MED.dbo.UPR00100.SOCSCNUM,10) AS VARCHAR(10))) + 
CAST(YEAR(BDATE) AS VARCHAR(4)) AS CHAR(8)) AS dfpin,
RTRIM(CAST(RIGHT(COL1,10) AS VARCHAR(10))) AS SOCSCNUM,
CAST(YEAR(MED.dbo.UPR00100.BRTHDATE) AS VARCHAR(4)) AS BDATE
FROM <table>

Open in new window

Author

Commented:
Hello and thank you, for the quickly-received comments!

@Dulton:  Equifax wants no delimiters.  So, I cannot have this be a .csv file.

@Ganapahti S:  The field is supposed to be eight characters long.  So, I don't know that there is a concern about losing fix of fourteen characters.  And, the real issue is why is dfpin showing up at position 380 instead of position 392.

TBSupport
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

GanapathiFacets Developer

Commented:
Can you post your table DDL and insert(a few records would be enough) scripts?

Author

Commented:
Actually, I see what the cause is.  The trouble is that I don't know how to fix it.

I took Dulton's advice, for troubleshooting purposes, by having this be a .csv file.  In that file, I see that the 12-character suffix field in my programming is not landing in the report, for some reason.

That's weird.  Indeed, this 12-character field is going to be blank because none of the employees have suffixes.  But, this field should be there, just as other blank fields of data are in my file.

If this field were there, dgpin would be at the correct position of 392 and not at 380, where it is now.

Any ideas as to how to pull the suffix?

Thanks!

TBSupport
GanapathiFacets Developer

Commented:
Can you try SPACE(12) instead of CAST for suffix?

Author

Commented:
Hello:

I figured out the issue.  I forgot to Build and Deploy my updates from my SSRS report after adding new fields such as suffix.  

I apologize for the bother.

Thanks, to Ganapathi S for offering advice and to Dulton for having me troubleshoot in .csv format which led ultimately to figuring out the mistake that I was making by forgetting to build and deploy.

TBSupport

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial