|
View previous topic :: View next topic
|
| Author |
Message |
jaguyria
New User
Joined: 15 Feb 2022
Posts: 21
Location: Portugal
|
|
|
|
Hello,
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' |
Output in File manager with the copy as template:
| Code: |
MONTANT1 MONTANT2 MONTANT3 montant4
£1 £2 £3 £4
PD 1:8 PD 8:8 PD 16:8 PD 24:8
-1069400.00 1378000.00 -1000.00 ***************** |
-> note that the format of montant4 is not correct.
Thanks in advance for your help! |
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2286
Location: USA
|
|
|
|
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... |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
| Why do you need to do this ? Would just 0 not acceptable for other process ? |
|
| Back to top |
|
|
jaguyria
New User
Joined: 15 Feb 2022
Posts: 21
Location: Portugal
|
|
|
|
| Quote: |
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.
Kind regards. |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2286
Location: USA
|
|
|
|
You refused to clearly explain what you are doing, and how, and why?
My guess: the only thing you should know is as follows:
| Code: |
SELECT MONTANT1,
MONTANT2,
MONTANT3,
CAST( 0.0 AS DEC(14,2)) as montant4
FROM MONT_TABLE
WHERE DATE= '31.05.2022' |
|
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
