Solved

Numeric fixed length field with bcp out

Posted on 2003-12-05
8
2,141 Views
Last Modified: 2007-12-19
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!!!!


0
Comment
Question by:bigdaddy21
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9886757
#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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9888706
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
 

Author Comment

by:bigdaddy21
ID: 9898187
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:bigdaddy21
ID: 9898215
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9898336
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
 

Author Comment

by:bigdaddy21
ID: 9898698
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
 
LVL 6

Accepted Solution

by:
ChrisKing earned 250 total points
ID: 9903181
> 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
 
LVL 6

Expert Comment

by:ChrisKing
ID: 9903193
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Configuring Remote Assistance for use with SCCM
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question