[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1100
  • Last Modified:

How do I convert a numeric to char, right justified

Hi,

I'm trying to create a fixed column width file to be used by a legacy application.  one particular column (W1RFAMT) is  a numeic amount that I need to translate into character right justified.  An example is $1,234,56 would be '      123456'.  How can I convert the field?  

SELECT  CHAR(W1CORP,4)||
        CHAR(W1BRCH,4)||
        CHAR(W1ACCT,12)||
        DIGITS(DECIMAL(W1TRNCD,4,0))||
        CHAR(W1SYS,4)||
        CHAR(W1PRIN,4)||
        CHAR(W1NAME,32)||
        CHAR(W1ADD1,32)||
        CHAR(W1ADD2,32)||
        CHAR(W1CITY,18)||
        CHAR(W1STAT,2)||
        CHAR('0000',16)||
        CHAR(W1GRP#,3)||
        DIGITS(DECIMAL(W1ZIP,9,0))||
        CHAR(W1RFAMT),12)||    <---- needs to be char right justified
        CHAR(W1DLER,16)
FROM    RCVOPSTEST.CBR901
0
BrianMack
Asked:
BrianMack
  • 3
  • 2
  • 2
  • +1
1 Solution
 
ghp7000Commented:
um, your column W1RFAMT, what is its native datatype?
if its integer, it can t have $ and decimal places
if its decimal, it cant have commas
if its decimal, then is it defined with decimal places?
0
 
BrianMackAuthor Commented:
W1RFAMT will always be positive by definition.  It is numeric 9,0 and does not contain any '$' or ',' or '.'. The problem is when I used DIGITS(DECIMAL()) it retruned '000000123456'.  I need it to look like '      123456' (padded with spaces).
0
 
BrianMackAuthor Commented:
If I just do CHAR(W1RFAMT) it returns '123456      '.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
BrianMackAuthor Commented:
I found this solution from mglxxx.  It works great.

        REPEAT(' ', 12 - LENGTH(TRIM(CHAR(W1RFAMT)))) || TRIM(CHAR(W1RFAMT))||
0
 
LowfatspreadCommented:
can i suggest

Right(space(12)+trim(char(w1rgamt)),12)

?
0
 
LowfatspreadCommented:
sorry
Right(space(12) concat trim(char(w1rgamt)),12)
0
 
ghp7000Commented:
if your happy with your own solution post a message in community support to have your points refunded but leave the question in the database for future reference for other users. Thanks
0
 
GranModCommented:
PAQed with points refunded (125)

GranMod
Community Support Moderator
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

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