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(convert(varchar, Col2),'0000000000.00')) as Col2 from
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("0000000000",10) + convert(varchar, Col2), 12) from TestNumeric
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!!!!


bigdaddy21Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ChrisKingCommented:
#1 how you treat NULLs is really a business rule case, is some columns you would treat it as 0, in other cases 9999999999.99. How the mainframe will interpret these values will be your guide here.

#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) will give you 100 zeros not 10, use: replicate("0",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)
0
LowfatspreadCommented:
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



 

0
bigdaddy21Author Commented:
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(convert(varchar, Col2),'0000000000.00')) as Col2 from
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!!!
0
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

bigdaddy21Author Commented:
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.
0
LowfatspreadCommented:
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...?
0
bigdaddy21Author Commented:
Lowfatsspread,
Not with a direct bcp out...I had to create a view with convert,replicate,isnull,etc functions to provide a readable format for the mainframe.  Native or character bcp will not do the trick for a readable bcp file.  My knowledge is limited with the use a creating a format file through bcp.  So I created the view to give me my desirable fixed length format.  

Do you have any ideas using direct bcp out to provide a flat file in fixed length format for mainframes?
0
ChrisKingCommented:
> How could I replicate with spaces instead of zeros?
replicate " ", not "0"
select right(replicate(" ",13) + convert(varchar, isnull(Col2, 0)), 13) from TestNumeric

> With the numeric(12,2) precision,scale...I see the 13 digits, but I don't see where you get the 14.
12 digits + 1 decimal point + 1 '-' sign = 14 chars  (eg -9999999999.99)

> 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?
not too sure what you what you are getting at, but "10 prescise, 1 decimal, and 2 scale" does note require 13 chars, it only requires 12 chars
that is: numeric(10,2) 's largest string will be "-12345678.90"
ie:   the precision (10)
+ 1 char for a decimal point (only if the scale > 0 ... it does not matter is the scale is 1,2 or 5)
+ 1 char for the minus sign
= 12 character string

a couple more examples to make it clear
numeric(5,0) requires 6 chars (this one will not have a decimal point)
numeric(5,1) requires 7 chars
numeric(5,2) requires 7 chars
numeric(5,3) requires 7 chars
numeric(5,4) requires 7 chars
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ChrisKingCommented:
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")
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.