• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1053
  • Last Modified:

SQL and 9(9)V9(6) numeric format.

I am writing a vb.net app that interfaces with a sql server 2005 database.I am pulling  data out of it and writing it to  a flat file for delivery to an outside vendor.  Some of the numeric fields require what they call the 9(9)V9(6)  format. Is anyone familar with this and if so is it possible to format the field this way in my sql query?
0
jfsedlar3rd
Asked:
jfsedlar3rd
  • 3
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in order to avoid any confusion, can you explain what that format is supposed to do, with data samples?
is this to be fixed-width, for example...
0
 
LowfatspreadCommented:
Hello jfsedlar3rd,

thats a cobol picture clause format
and implies a unsigned number with 9 numbers before an implied decimal point and 6 places of decimals

so

  LEFT(REPLACE(REPLACE(convert(char(16),convert(decimal(15,6) , yourcolumn) ),'-',' '),'.',''),15)

SHOULD BE THE BASIC REQUIREMENT

however get them to confirm that they do not want :-
a physical decimal point
any sign information along with the numbers
and a character format


and to confirm that


Regards,

Lowfatspread
0
 
jfsedlar3rdAuthor Commented:
You are right on LowFatSpread they do not want a decimal point.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
jfsedlar3rdAuthor Commented:
So if they were asking also for 9(13)V9(2), How would that change the syntax.
0
 
jfsedlar3rdAuthor Commented:
I just tested it and I may be mistaken but I thought the idea for this was to gain the same field length regardless of what the amount was. I ask this because in my output file some values are longer 9, 10 and 11 characters in length. The file this value goes into is of fixed length delimitation.
0
 
LowfatspreadCommented:
Substring(REPLACE(REPLACE(convert(char(16),convert(decimal(15,2) , yourcolumn) ),'-',' '),'.',''),1,15)

for 9(13)V99  it becomes decimal(15,2)     ie total digits  , number of decimals within the number of digits...

use substring to get a fixed length...

 
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now