Thursday, May 21, 2009

Teradata tip of the day

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

3 comments:

  1. Quite good. Thanks a lot.

    ReplyDelete
  2. Thanks for sharing the info.

    ReplyDelete
  3. Hi Meena
    I need to load current date through fast load. pls need your help at the earliest.

    ReplyDelete