Avatar of TBSupport
TBSupport
 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
Microsoft SQL Server 2008SSRS

Avatar of undefined
Last Comment
TBSupport

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Dulton

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.