MVSFORUMS.com

astro · Beginner Joined: 05 Oct 2005 Posts: 31 Topics: 7

Dear Friends,
I have a column (say COL1) declared as CHAR 10 in DB2 table (say TAB1).
In this column, 1st position contains character value
and 2-4th positions contain packed numeric value
and the rest contains character value.
Now I want to see only the number stored in COL1.
I have used a following query in QMF
SELECT SUBSTR(COL1,2,2) FROM TAB1.
But this does not show me the number Embarassed

. It only shows the compressed symbols. Is there any way that I can see the numeric value stored in this column?
Thanks for your responses.

CZerfas · Intermediate Joined: 31 Jan 2003 Posts: 211 Topics: 8

I don't know from where you got this packed numeric value. If it is in fact a DB2 smallint, you can say
SELECT CAST(SUBSTR(COL1,2,2)) AS SMALLINT.

Alternatively you can at least have a look at the hexadecimal expression of this numeric value with
SELECT HEX(SUBSTR(COL1,2,2))

good luck
Christian

astro · Beginner Joined: 05 Oct 2005 Posts: 31 Topics: 7

Dear Christian,
The Packed numeric item is passed to this column from application programs.
Well. Thanks for your speedy reply.
SELECT HEX(SUBSTR(COL1,2,2)) helped me to see the value in this case.
But CAST(SUBSTR(COL1,2,2)) AS SMALLINT does not work & I got SQLError for this stmt.
Once Again Thanks for your reply