I want to know if we can generate the Hex condition in the where clause at runtime?
Example:
The below sql query works. Output 10 Rows.
COL1 is a CHAR(10) column, it stores the LOG RBA in HEX format.
Code:
SELECT *
FROM TABLE where COL1 > X'00000000000000000000000000'
;
I want to do something like this: - The below doesnot work as the sub select returns a string value and Not HEX value. Hence the output fetched 0 rows.
Code:
SELECT *
FROM TABLE where COL1 > (SELECT 'X'''||substr('2012-04-16-15.43.13.00000000000000000000000000',27)||'''' FROM SYSIBM.SYSDUMMY1)
;
Please let me know how this can be achieved in the singe SQL query?
We have a table where IBMSNAP_INTENTSEQ is present. This column is populated by DPROP setup. It contains the Hex string in unreadable form. IBMSNAP_INTENTSEQ is CHAR(10) [Not Readable]. When we use HEX function to get the value, It expands to CHAR(20) [Readable format]
We store this hexibm value in a variable along with the concatenated timestamp as shown below:
Code:
2012-03-31-10.10.10.0000000000C96DA597C57C0001
Now we want to read the data from the table dynamically by substr('2012-03-31-10.10.10.0000000000C96DA597C57C0001',27) --> This will extract only the hex number part as starting position is 27 in substr.
If I try the below query
Code:
SELECT * FROM
TABLE01
where HEX(IBMSNAP_INTENTSEQ) > '00000000000000000000'
WITH UR;
This will also not yield the correct results as Now hex is getting compared with CHAR.
In HEX, Sequence is -- 0,1,2,3,4....A,B,C,D,E,F
In CHAR (EBCDIC) Sequence is -- A,B,C,D,E,F....Z,0,1,2,3,....9
This is why I want to make the query dynamic as shown in the above post.
Not sure how this can be achieved in single query.
Here is a UserDefinedFunction which is the opposite from HEX()
just make sure that length(input string) is divisible by 2. ie. Hex characters are paired.
Code:
CREATE FUNCTION
My_HEX2EBCDIC (I_STRING VARCHAR(50))
RETURNS VARCHAR(50)
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
Hello,
Quote:
Not sure if DBA's will agree to have a UDF created in our shop...
If they do not agree, maybe they should be responsible for providing a solution. . .
If this is truly needed and they don't have a way to do this and will not agree on your "own code" (GuyC's), it will be time to talk with your manager to learn how to proceed. . .
with cte0 (char1,char2) as (
select '0',x'000102030405060708090A0B0C0D0E0F' from sysibm.sysdummy1 union all
select '1',x'101112131415161718191A1B1C1D1E1F' from sysibm.sysdummy1 union all
select '2',x'202122232425262728292A2B2C2D2E2F' from sysibm.sysdummy1 union all
select '3',x'303132333435363738393A3B3C3D3E3F' from sysibm.sysdummy1 union all
select '4',x'404142434445464748494A4B4C4D4E4F' from sysibm.sysdummy1 union all
select '5',x'505152535455565758595A5B5C5D5E5F' from sysibm.sysdummy1 union all
select '6',x'606162636465666768696A6B6C6D6E6F' from sysibm.sysdummy1 union all
select '7',x'707172737475767778797A7B7C7D7E7F' from sysibm.sysdummy1 union all
select '8',x'808182838485868788898A8B8C8D8E8F' from sysibm.sysdummy1 union all
select '9',x'909192939495969798999A9B9C9D9E9F' from sysibm.sysdummy1 union all
select 'A',x'A0A1A2A3A4A5A6A7A8A9AAABACADAEAF' from sysibm.sysdummy1 union all
select 'B',x'B0B1B2B3B4B5B6B7B8B9BABBBCBDBEBF' from sysibm.sysdummy1 union all
select 'C',x'C0C1C2C3C4C5C6C7C8C9CACBCCCDCECF' from sysibm.sysdummy1 union all
select 'D',x'D0C1C2C3C4C5C6C7C8C9CACBCCCDCECF' from sysibm.sysdummy1 union all
select 'E',x'E0E1E2E3E4E5E6E7E8E9EAEBECEDEEEF' from sysibm.sysdummy1 union all
select 'F',x'F0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFF' from sysibm.sysdummy1
)
, cte1 (HEXIBM) as (
select substr('2012-03-31-10.10.10.0000000000C96DA597C57C0001',27) from sysibm.sysdummy1
)
, cte2 (pstart,istring,ostring) as (
Select 3,hexibm ,
varchar(substr(cte0.char2, locate(substr(hexibm,2,1),'0123456789ABCDEF') ,1),50)
from cte1, cte0 where cte0.char1 = substr(hexibm,1,1)
union all
Select pstart + 2,istring ,
ostring || varchar(substr(cte0.char2,locate(substr(istring,pstart + 1,1),'0123456789ABCDEF') ,1),50)
from cte2 ,cte0
where pstart < length(istring)
and cte0.char1 = substr(istring,pstart,1)
)
, cte3 (ostring) as (
select ostring from cte2
where pstart > length(istring)
)
SELECT IBMSNAP_INTENTSEQ,HEX(IBMSNAP_INTENTSEQ) AS HEXIBM FROM
TABLE01, cte3 where
table01.IBMSNAP_INTENTSEQ = cte3.ostring
WITH UR;
.jpg)
