Does someone knows how can I select in DB2 a value with a specific packed format?
In my case I need to make a unload of a table with 3 montants defined as DECIMAL(14,2) NOT NULL and also the montant 0 (zero) in the same format. In my SYSREC I must have the 3 montants and the zero in the packed format: S9(12)V(2) COMP-3.
The propose of this is to make a file that will be concatenated with others unloads and after they will be used in a program that waits these 4 montants in this packed format.
example of my query:
Code:
SELECT
MONTANT1
,MONTANT2
,MONTANT3
,0 as montant4
FROM MONT_TABLE
WHERE
DATE= '31.05.2022'
Note: The fields MONTANT1, MONTANT2, MONTANT3 are defined as DECIMAL(14,2) NOT NULL
How can I get the value 0 in the same format as the MONTANT1, MONTANT2, MONTANT3?
Some idea using VARCHAR?
I've tried this but the format of the 0 stays different from COMP-3 :
Code:
SELECT
MONTANT1,
MONTANT2,
MONTANT3,
VARCHAR_FORMAT(0, 'S999999999999.99') as montant4
FROM MONT_TABLE
WHERE
DATE= '31.05.2022'
SQL SELECT by itself is not able to transfer the extracted data straight to your output record.
What tool are you using to create the record? Where and how your SELECT is executed?
BTW, your data are defined as DEC(14,2) within DB2, but in your output record (created by unknown tool, likely, by DB2 Unload utility?) all those values are converted to character format. Without conversion to character, neither Packed Decimals, nor Unpacked Decimals would include any visible decimal point, as well as visible minus sign...
SQL SELECT by itself is not able to transfer the extracted
data straight to your output record.
Yes, maybe.
I've changed the technical structure of my job to do what I must do, I'll explain it above:
In my first solution I thought to do:
- 8 unloads of different tables
- concatenation of these 8 files SYSREC (this is the reason I should have the same format on the fields, in order to have the same LRECL in the final file)
- turn the program with the final file
In my current (2nd solution) I'm thinking doing:
- 1 unique unload with the query of the union of the 8 different tables, selecting 0 normally when the field isn't expected in one table (so the 0 will be automatically converted to the format of the other table that has the desirable format)
- turn the program with the file SYSREC
Quote:
What tool are you using to create the record? Where and how your SELECT is executed?
To make the unload I'm submitting the job using the PROC=DSNTISQL
Quote:
your data are defined as DEC(14,2) within DB2, but in your output record (created by unknown tool, likely, by DB2 Unload utility?)
To see the output record I'm using the tool FileManager (FM) > View > I fill the template and the tool should make the conversion to character
Quote:
Why do you need to do this ? Would just 0 not acceptable for other process ?
As I explained under, I must do 8 unloads and generate 8 files with the same LRECL to concatenate them and after use the total file in the INPUT of the programme that expects the packed format. But I've solved my problem by changing the structure of my job.
Thanks for your help, writing this in here made me think in the new solution, that works.
