asked on

T-SQL and SSRS: Length of Text

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

ASKER CERTIFIED SOLUTION

Dulton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ganapathi

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

TBSupport

ASKER

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

Ganapathi

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

TBSupport

ASKER

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

Ganapathi

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

TBSupport

ASKER

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