Joined: 02 Dec 2002 Posts: 619 Topics: 172 Location: Stockholm, Sweden
Posted: Wed Aug 03, 2022 2:03 am Post subject: Cast (?) packed field in VARCHAR column to number
We have a VARCHAR column in a table that contains data from a copybook. This copybook in turn contains various fields, including packed ones.
I am trying to perform an (IBM) SQL select so the fields look "normal".
For example, if I run the following query in SPUFI on the mainframe,
Code:
SELECT LEFT(TRANSDATA, 6)
etc etc
I see the following results (with hex on)
Quote:
/`-.Ã<
656164
11056C
so we can see that the value is a packed field containing 61516015664.
How do I perform an SQL query such that I see that value rather than the (character) packed field ?
I have tried with examples such as
Code:
CAST(LEFT(TRANSDATA, 6) AS INTEGER)
but all the variations I've tried keep giving me a -420.
Also, please remember that any solution should take into consideration that the packed field in other places could be negative.
Joined: 02 Dec 2002 Posts: 619 Topics: 172 Location: Stockholm, Sweden
Posted: Thu Aug 04, 2022 1:30 am Post subject:
Thank you for the suggestion. This is where I am at the moment. As you can see, it's rather clunky at the moment since a lot of it assumes that I know the length of the relevant column.
In this example, it's all well and good since I know the relevant field is packed, signed and 6 bytes. Without this knowledge, I'm not sure how I would arrive (easily) at the correct values instead of the 11 below.
Code:
CAST(LEFT(HEX(SUBSTR(TRANSDATA, 1, 6)),11) AS DECIMAL(11,0))
, HEX(SUBSTR(TRANSDATA, 1, 6))
, SUBSTR(TRANSDATA, 1, 6)
FROM mytable
WHERE TRANSTYP = 36
;
---------+---------+---------+---------+---------+---------+---------+
Joined: 26 Nov 2002 Posts: 12367 Topics: 75 Location: San Jose
Posted: Thu Aug 04, 2022 10:08 am Post subject:
misi01,
What is the intent of extracting the data ? Do you need as a Packed decimal field or readable numeric?
If you need it as packed decimal , you can simply get the 6 bytes as is. If you want it as readable numeric data then you need to get the 11 bytes from hex value.
If you insist on having the data as you show you can use the following SQL
Code:
SELECT SUBSTR(HEX(TRANSDATA),1,11) AS DEC_VALUE
,CHAR(HEX(TRANSDATA),12) AS HEX_VALUE
,SUBSTR(TRANSDATA,1,6) AS RAW_DATA
FROM mytable
WHERE TRANSTYP = 36
;
Edit: Realized that you are essentially ignoring the sign, but if you have negative data, then it needs to be handled. So negotiate the sign bit and put the positive and negative signs
Code:
SELECT CASE(SUBSTR(HEX(TRANSDATA),12,1))
WHEN 'B' THEN '-'
WHEN 'D' THEN '-'
ELSE '+'
END ||
SUBSTR(HEX(TRANSDATA),1,11) AS DEC_VALUE
,CHAR(HEX(TRANSDATA),12) AS HEX_VALUE
,SUBSTR(TRANSDATA,1,6) AS RAW_DATA
FROM mytable
WHERE TRANSTYP = 36
;
_________________ Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
