Problem with Sign for a numeric field in flat file

[Surya]

In a Cobol program, we have a numeric field with attribute S9(5)V99 which is subfield of a record format XYZ.

The numeric field is populated with some data and it will be written into the flat file with record format XYZ.

When we have the positive value (Eg: 28 here). It is writing it as 0002800.
and when it have the negative value (Eg: -28 here). It is writing it as 000280}.

By looking at the data I came to know this.

If we want to know the exact value in the flat file. (positive or negative) How can we know through SQL query instead of writing the COBOL program again.

Thanks

[arrow483]

Re: Problem with Sign for a numeric field in flat file

Negative sign "overwrites" the last column in EBCDIC files. The 0 becomes }, 1 becomes J, 2 becomes K , .... 9 becomes R.

So 28563- becomes 2856L

I that what you needed ?

[Surya]

Re: Problem with Sign for a numeric field in flat file

Arrow,

Thanks for the reply.

I am looking for a SQL query where it can exactly shows the value (i.e, for 2856L
it should show as -28563)

[arrow483]

Re: Problem with Sign for a numeric field in flat file

If its a true flat file, there's probably just one field for the whole record, say "F1". Lets assume the data in in cols 11-17. You'll probably be stuck with something like this

Select
Case when substr(F1,17,1) = '}' then dec((substr(F1,11,6) || '0'),7,0)
when substr(F1,17,1) = 'J' then dec((substr(F1,11,6) || '1'),7,0)
--- thru R & 9
else dec(substr(F1,11,7)
as AMOUNT,