asked on

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

ghp7000

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?

BrianMack

ASKER

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).

BrianMack

ASKER

If I just do CHAR(W1RFAMT) it returns '123456 '.

BrianMack

ASKER

I found this solution from mglxxx. It works great.

REPEAT(' ', 12 - LENGTH(TRIM(CHAR(W1RFAMT)))) || TRIM(CHAR(W1RFAMT))||

Lowfatspread

can i suggest

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

?

Lowfatspread

sorry
Right(space(12) concat trim(char(w1rgamt)),12)

ghp7000

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

ASKER CERTIFIED SOLUTION

GranMod

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial