IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Select a DB2 value in a specific decimal format (packed)


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
jaguyria

New User


Joined: 15 Feb 2022
Posts: 21
Location: Portugal

PostPosted: Tue Aug 23, 2022 8:14 pm
Reply with quote

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
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 1614

PostPosted: Tue Aug 23, 2022 9:10 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2945
Location: NYC,USA

PostPosted: Wed Aug 24, 2022 8:28 am
Reply with quote

Why do you need to do this ? Would just 0 not acceptable for other process ?
Back to top
View user's profile Send private message
jaguyria

New User


Joined: 15 Feb 2022
Posts: 21
Location: Portugal

PostPosted: Wed Aug 24, 2022 2:36 pm
Reply with quote

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
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 1614

PostPosted: Wed Aug 24, 2022 4:43 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
This topic is locked: you cannot edit posts or make replies. Construct new record using specific f... DFSORT/ICETOOL 6
No new posts mail attachment with excel format All Other Mainframe Topics 2
No new posts Need to find a specific STRING COBOL Programming 11
No new posts String has hex character need to conv... COBOL Programming 3
No new posts How can I select certain file dependi... JCL & VSAM 12
Search our Forums:

Back to Top