Solved

Numeric fixed length field with bcp out

Posted on 2003-12-05
8
2,066 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
  • 3
  • 3
  • 2
8 Comments
 
LVL 6

Expert Comment

by:ChrisKing
Comment Utility
#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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:bigdaddy21
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
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
Comment Utility
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
Comment Utility
> 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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SyBase SQL DataTime Format 6 286
SQL Query Syntax 11 142
Sysbase Central connection fails 3 37
I need expert Sybase Sql help with a query 3 46
Marketing can be an uncomfortable undertaking, especially if your material is technology based. Luckily, we’ve compiled some simple and (relatively) painless tips to put an end to your trepidation and start your path to success.
In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now