February 8, 2005 at 4:53 am
Hi,
I have a table in SQL server. This needs to be moved into mainframes. i'm are planning to BCP out as text file and FTP the same to Mainframe. But, the problem is, we are not able to get the desired format.
I tried using DTS, but it gives spaces between the columns. The requirement is there should not be any spaces between columns. And there are some fields which are integer and are to be converted into short int.
Can anyone let me know if there is any alternate to BCP which can help me in formatting?
Regards
Naresh
February 9, 2005 at 12:14 am
It sounds like you need to generate a fixed length format text file for upload to the mainframe. If so, you need to concatenate all the fields into one, giving the desired length for each field. Put this select statement into a view for use by DTS or BCP.
eg
The following select statement concatentates four fields together into a single string field. Each field is a different data type.
Desired output is :
avarcharfld varchar(50), positions 1 to 50 representing first field,
adecimalfld decimal(19,7), positions 51 to 70 for second field,
anintfld int, positions 71 to 80 for third field
adatefld datetime, positions 81 to 88 for fourth field in format yyyymmdd.
Select
left(avarcharfld + space(50), 50) +
convert(char(20), adecimalfld ) +
str (anintfld) +
convert(char(8), adatefld, 112)
as SingleFld
from yourtable
there are some alternatives, especially when you want implied decimal places
str ( adecimalfld*10000000, 20, 0)
Note that the convert function uses Char(x) and not varchar(x) so the desired length is maintained.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply