bigdaddy21
asked on
Numeric fixed length field with bcp out
I am in the process of bcp out a file to be converted into IBM Mainframe. I need all fields within the table to be fixed length output with the bcp out. I have created a view to convert varchar to char and used isnull function to ensure dates and char fields that contain null values adhere to fixed length. Problem or confession: I have several numeric fields that contain null values. IE) Col3 numeric(12,2) null
Two scenerios:
#1 Field contains all null values and I need to represent fixed length...What can I do?
select convert(char(13),isnull(co
TestNumeric
This would fill the numeric field with 000000000.00 filling the numeric(12,2) precision,scale
Any other ideas or better solutions for issue #1 would be greatly appreciated!!!
#2
Table has field numeric(12,2) null
Column contains both null values and data
--I have used this syntax, but it does not represent the 2 scale. If there was some numeric
data in this column it would look like this 10.00
select right(replicate("000000000
I am missing something because here is my output....
rows 1-4 contain data, row 5 is null
111111111.11
000000011.11
000000011.00
000000111.00
000000000000
There should always be 10 bytes represented in the precision and 2 in scale
Correct ME IF I AM WRONG
ie)Fixed Length for numeric(12,2)----> 0000000000.00
Any help is greatly appreciated!!!!
Two scenerios:
#1 Field contains all null values and I need to represent fixed length...What can I do?
select convert(char(13),isnull(co
TestNumeric
This would fill the numeric field with 000000000.00 filling the numeric(12,2) precision,scale
Any other ideas or better solutions for issue #1 would be greatly appreciated!!!
#2
Table has field numeric(12,2) null
Column contains both null values and data
--I have used this syntax, but it does not represent the 2 scale. If there was some numeric
data in this column it would look like this 10.00
select right(replicate("000000000
I am missing something because here is my output....
rows 1-4 contain data, row 5 is null
111111111.11
000000011.11
000000011.00
000000111.00
000000000000
There should always be 10 bytes represented in the precision and 2 in scale
Correct ME IF I AM WRONG
ie)Fixed Length for numeric(12,2)----> 0000000000.00
Any help is greatly appreciated!!!!
what is the mainframe going to do with your data?
is it going to be loaded into a DB2 UDB database?
(if so then get the db2 dba to specify how his load file should look....
null are handled by additional indicator columns... which isn't a bad way of
handling them anyway then there is no confusion over what the data content is )
should the Business Analyst for your project have decided on the
data format that is required?
and the mainframe will probably appreciate a variable length output format..
e.g. csv, or XML
is it going to be loaded into a DB2 UDB database?
(if so then get the db2 dba to specify how his load file should look....
null are handled by additional indicator columns... which isn't a bad way of
handling them anyway then there is no confusion over what the data content is )
should the Business Analyst for your project have decided on the
data format that is required?
and the mainframe will probably appreciate a variable length output format..
e.g. csv, or XML
ASKER
ChrisKing,
1# I came to the solution of just using the convert function when column contains all nulls...
Same as above this would probably be the best solution.
select convert(char(13),isnull(co
TestNumeric
2# Good point on the negative numbers.
******How could I replicate with spaces instead of zeros?
select right(replicate("0",13) + convert(varchar, isnull(Col2, 0)), 13) from TestNumeric
Would give the following output where table's col2 has first 4 rows with data and 5th null...
1111111111.11
0000000011.11
0000000011.00
0000000111.00
0000000000.00
With the numeric(12,2) precision,scale...I see the 13 digits, but I don't see where you get the 14. When I try to insert a value of 11 prescise digits and 2 scale I get arithmic overflow as expected. Thus 10 prescise, 1 decimal, and 2 scale= 13 digits ....Are you saying that sybase utilizes the negative sign as a digit as well. If so would -9999999999.99 give an arithmic overflow and -999999999.99 not?
Thanks for the insight!!!
1# I came to the solution of just using the convert function when column contains all nulls...
Same as above this would probably be the best solution.
select convert(char(13),isnull(co
TestNumeric
2# Good point on the negative numbers.
******How could I replicate with spaces instead of zeros?
select right(replicate("0",13) + convert(varchar, isnull(Col2, 0)), 13) from TestNumeric
Would give the following output where table's col2 has first 4 rows with data and 5th null...
1111111111.11
0000000011.11
0000000011.00
0000000111.00
0000000000.00
With the numeric(12,2) precision,scale...I see the 13 digits, but I don't see where you get the 14. When I try to insert a value of 11 prescise digits and 2 scale I get arithmic overflow as expected. Thus 10 prescise, 1 decimal, and 2 scale= 13 digits ....Are you saying that sybase utilizes the negative sign as a digit as well. If so would -9999999999.99 give an arithmic overflow and -999999999.99 not?
Thanks for the insight!!!
ASKER
Lowfatspread,
This a billing systems conversion that will be converted from Sybase to mainframe. Since the data models are not the same the basis of the conversion is fixed length..fixed format. I will be writing conversion scripts for sybase database to provide the mainframe with readable data. IBM seems to be data sensitive...I haven't had much experience with mainframes.
This a billing systems conversion that will be converted from Sybase to mainframe. Since the data models are not the same the basis of the conversion is fixed length..fixed format. I will be writing conversion scripts for sybase database to provide the mainframe with readable data. IBM seems to be data sensitive...I haven't had much experience with mainframes.
exactly my point...
what file/database system do you want to load on the mainframe....
what format best suits it...
could the mainframe not read the data directly from the sybase source...?
what file/database system do you want to load on the mainframe....
what format best suits it...
could the mainframe not read the data directly from the sybase source...?
ASKER
Lowfatsspread,
Not with a direct bcp out...I had to create a view with convert,replicate,isnull,e
Do you have any ideas using direct bcp out to provide a flat file in fixed length format for mainframes?
Not with a direct bcp out...I had to create a view with convert,replicate,isnull,e
Do you have any ideas using direct bcp out to provide a flat file in fixed length format for mainframes?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
just to add a little more confusion (sorry)
if the scale = the precision, then you will also have to allow for a "0" before the decimal point
numeric(5,5) requires 8 chars (eg "-0.12345")
if the scale = the precision, then you will also have to allow for a "0" before the decimal point
numeric(5,5) requires 8 chars (eg "-0.12345")
#2 thre points here:
1. your method will not handle negative numbers, you will get values like "0000-1234.56", use leading spaces, not zeros (if you can)
2. replicate("0000000000",10)
3. numeric(12,2) is 12 digits of precision (not 10), 2 are decimal places therefore 10 are integer places, total char length is 14 (1 doe the decimal point and the other to indicate negative numbers)