DTS left-justifies numbers?

Question

DTS left-justifies numbers?

DonaldW

SSCarpal Tunnel

Points: 4525
More actions
March 1, 2004 at 11:42 am

#161038

Hello, all. I am working on a DTS package to export data to a text file for upload to another system. The issue I am encountering is that numeric data from one field is being left-justified instead of right-justified. I am concatenating data into string fields on either side of it in the query but that should'nt have anything to do with it, should it?
Any suggestions?
TIA
Don

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Can you post some more information about your data to output, how the output now looks and how it should look like?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

DonaldW SSCarpal Tunnel Points: 4525 More actions · Answer 2

Hi, Frank. Here's the deal. In the relevant table I have four data fields I need - Account_K, Profit_Center_K, Amount_M and Description_X. I have to take the data from those fields and concatenate it with constant values to create an output string that is about 110 characters per line, each line representing a different record from the source table. Account_K and Profit_Center_K are both alphanumeric, Amount_M is numeric.

Everything has behaved itself except Amount_M. When concatenated with the rest of the fields it is left-justified, not right-justified. I finally got around it by creating a massive pair of Cast formulas to calculate the length of the absolute value of the number and add leading spaces in front of it. Here is the final SQL statement:

select Acct = ACCOUNT_K + PROFIT_CENTER_K + '0000000000000000' +

(case when [amount_m] >= 0 then '500 ' when [amount_m] < 0 then '400 ' end) +

(space(10-len(rtrim(cast(cast(abs([amount_m]*100) as bigint) as char)))) +

rtrim(cast(cast(abs([amount_m]*100) as bigint) as char))) +

'0000000000000000000100 ' + rtrim(DESCRIPTION_X)

from GL_Cum_Transaction

The resulting string looks quite nice:

11100101000000000000000000400 100000000000000000000000100 test trans

My only problem now is that there must be three or four hundred training spaces in the output, whether run from DTS or QA. The whole thing is supposed to be no more than 110 spaces, based on the actual character count of constants and field lengths.

I have no idea where the extra are coming from. They are there even if I enclose the whole thing in an RTRIM function and they render the output file useless without a lot of work that defeats the purpose of doing the query.

I'm not sure what I'm doing wrong, if anything. Any ideas?

Thanks!

Don

noeld SSC Guru Points: 96590 More actions · Answer 3

That space is usually from the underlying table Field. Have you tried exporting to a file?

* Noel

DonaldW SSCarpal Tunnel Points: 4525 More actions · Answer 4

Exporting to a file was where I was getting all the spaces. When I ran the query in QA it gave me the right length field. What I ended up doing was creating a temp table in my database with one column of type char(150), populating it with the output from my query, then exporting that table via DTS. That gave me an output file with the right records.

I have encountered this kind of thing before but never taken the time to figure it out. Why SQL Server would add all those spaces to the end of the record is beyond me. It's not getting them from the underlying table because none of the fields I'm pulling has that much empty space. If I ever solve it I'll post the results.

Thanks for your attention on this one.

Don

Shalom Dreampeace Compost Newbie Points: 1 More actions · Answer 5

Don, I had the same problem as you, and hoped for a solution in the thread of your posting. Disappointed to not find one. But I came up with a solution that may be of use to you and others

when I created my DTS export (SQL to TEXT) the source was

select [fund],[FundName], [Amount]from ....

I changed it to

select [fund],[FundName], right(replicate('0',12)+ convert(varchar, [Amount]),12) as txtAmount from ...

then I had to create a transformation to copy the txtAmount column to a quotable column. (I am such a newbie that I don't know what a quotable column is or if there was an easier way...)

I also found an example of padding with zeros at http://www.geocities.com/sqlserverexamples/string5.htm , use that technique if you like it better.

My mainframe programming language might not like the explicit decimal point, so I may end up multiplying amount by 100 in my "DTS export source"

Hopefully you found a solution to your problem months ago, and this is no longer relevant to you....

now my data looks like

99812 Blah blah blah blah blah blah blah test 000038027.55

99813 Bloop Bloop shorter name 000004061.45

99820 Gremlin Goof (names changed to protect) 000000611.00

(it looks like I have one space between my fields)