Hi Friends,
I thought of sharing some of the nuances I learn in teradata as a part of my project with you.
Hope this initiative helps :)
While exploring on how to convert hexadecimal value to interger and viceversa, my team was not able to find a direct function on Teradata.
This is the alternative we used (a hack :)) -
SELECT
1234567890 AS x
,SUBSTRING('0123456789ABCDEF' FROM x / (16**7) MOD 16 + 1 FOR 1) ||
SUBSTRING('0123456789ABCDEF' FROM x / (16**6) MOD 16 + 1 FOR 1) ||
SUBSTRING('0123456789ABCDEF' FROM x / (16**5) MOD 16 + 1 FOR 1) ||
SUBSTRING('0123456789ABCDEF' FROM x / (16**4) MOD 16 + 1 FOR 1) ||
SUBSTRING('0123456789ABCDEF' FROM x / (16**3) MOD 16 + 1 FOR 1) ||
SUBSTRING('0123456789ABCDEF' FROM x / (16**2) MOD 16 + 1 FOR 1) ||
SUBSTRING('0123456789ABCDEF' FROM x / (16**1) MOD 16 + 1 FOR 1) ||
SUBSTRING('0123456789ABCDEF' FROM x / (16**0) MOD 16 + 1 FOR 1) AS Int2HexString
,Int2HexString as s
,POSITION(SUBSTRING(s FROM 8 FOR 1) IN '123456789ABCDEF') * ((16**0) (INT)) +
POSITION(SUBSTRING(s FROM 7 FOR 1) IN '123456789ABCDEF') * ((16**1) (INT)) +
POSITION(SUBSTRING(s FROM 6 FOR 1) IN '123456789ABCDEF') * ((16**2) (INT)) +
POSITION(SUBSTRING(s FROM 5 FOR 1) IN '123456789ABCDEF') * ((16**3) (INT)) +
POSITION(SUBSTRING(s FROM 4 FOR 1) IN '123456789ABCDEF') * ((16**4) (INT)) +
POSITION(SUBSTRING(s FROM 3 FOR 1) IN '123456789ABCDEF') * ((16**5) (INT)) +
POSITION(SUBSTRING(s FROM 2 FOR 1) IN '123456789ABCDEF') * ((16**6) (INT)) +
POSITION(SUBSTRING(s FROM 1 FOR 1) IN '123456789ABCDEF') * ((16**7) (INT)) AS HexString2Int
;
Try it out. Also, if you guys know any direct method, let me know.
I keep this initative to a closed group of friends working in DW, so we can share each other stuff we know and broaden our knowledge on TD :)
This chain can be used for learning any new technical info.
~Meena
Quite good. Thanks a lot.
ReplyDeleteThanks for sharing the info.
ReplyDeleteHi Meena
ReplyDeleteI need to load current date through fast load. pls need your help at the earliest.