MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Cast (?) packed field in VARCHAR column to number

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
misi01
Advanced


Joined: 02 Dec 2002
Posts: 615
Topics: 171
Location: Stockholm, Sweden

PostPosted: Wed Aug 03, 2022 2:03 am    Post subject: Cast (?) packed field in VARCHAR column to number Reply with quote

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.

Thank you.
_________________
Michael
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Wed Aug 03, 2022 2:29 pm    Post subject: Reply with quote

misi01,

try HEX function.

Untested sql

Code:

SELECT HEX(CHAR(TRANSDATA,6))
  FROM your_tbl   
  ;     

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
misi01
Advanced


Joined: 02 Dec 2002
Posts: 615
Topics: 171
Location: Stockholm, Sweden

PostPosted: Thu Aug 04, 2022 1:30 am    Post subject: Reply with quote

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                                             
   ;                                                                 
---------+---------+---------+---------+---------+---------+---------+
                                                                     
---------+---------+---------+---------+---------+---------+---------+
 61516015664.  61516015664C  /`-.Ã<                                   



I'll continue working on it and see if I can improve it.
_________________
Michael
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Thu Aug 04, 2022 10:08 am    Post subject: Reply with quote

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

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
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


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group