Archives of the TeradataForum

Message Posted: Mon, 11 Jul 2005 @ 16:05:44 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I joined the table to itself - which is fast because obviously it's on the same Amp - so as to avoid performing SUBSTRING on a column character more than once, which otherwise I think is necessary and slows the query down more than a self-join.

Anyway, the inner query splits the column into bytes, named "1F1_C1" (One For One, Column 1), etc. For each additional column that needs 'Crunching' another set of Substrings will be performed. In our case, the whole thing turned out to be quite long!

I found that using the hexadecimal conversion useful although I'm struggling to remember exactly why; I think it was to avoid getting the 'translate' error message for the unwanted characters.

The Index function, if it's not obvious, either accepts the character or concatenates '' to the string i.e. excludes the character.

     SELECT
        T2.COU_ID, T2.ACC_ID,
        (
        (CASE WHEN INDEX (ALPHANUMERIC_HEX,CHAR2HEXINT("1F1_C1")) > 0 THEN
     "1F1_C1" Else '' End) ||
        (CASE WHEN INDEX (ALPHANUMERIC_HEX,CHAR2HEXINT("2F1_C1")) > 0 THEN
     "2F1_C1" Else '' End) ||
        (CASE WHEN INDEX (ALPHANUMERIC_HEX,CHAR2HEXINT("3F1_C1")) > 0 THEN
     "3F1_C1" Else '' End) ||
        (CASE WHEN INDEX (ALPHANUMERIC_HEX,CHAR2HEXINT("4F1_C1")) > 0 THEN
     "4F1_C1" Else '' End) ||
        (CASE WHEN INDEX (ALPHANUMERIC_HEX,CHAR2HEXINT("5F1_C1")) > 0 THEN
     "5F1_C1" Else '' End) ||
        (CASE WHEN INDEX (ALPHANUMERIC_HEX,CHAR2HEXINT("6F1_C1")) > 0 THEN
     "6F1_C1" Else '' End) ||
        (CASE WHEN INDEX (ALPHANUMERIC_HEX,CHAR2HEXINT("7F1_C1")) > 0 THEN
     "7F1_C1" Else '' End) ||
        (CASE WHEN INDEX (ALPHANUMERIC_HEX,CHAR2HEXINT("8F1_C1")) > 0 THEN
     "8F1_C1" Else '' End) ||
        (CASE WHEN INDEX (ALPHANUMERIC_HEX,CHAR2HEXINT("9F1_C1")) > 0 THEN
     "9F1_C1" Else '' End) ||
        (CASE WHEN INDEX (ALPHANUMERIC_HEX,CHAR2HEXINT("10F1_C1")) > 0 THEN
     "10F1_C1" Else '' End)
        ) AS NAM_NM_CRUNCH
     FROM CUSTOMER AS T2
     INNER JOIN
        ( SELECT
     /* JOINING COLUMNS */
           T1.COU_ID, T1.NAD_ID,
     /* CRUNCH TEST VARIABLIES */
        '30,31,32,33,34,35,36,37,38,39,41,42,43,44,45,46,47,48,49,4
     A,4B,4C,4D,4E,4F,50,51,52,53,54,55,56,57,58,59,5A' AS ALPHANUMERIC_HEX,
        '30,31,32,33,34,35,36,37,38,39' AS NUMERIC_HEX,
     /* NAM_NM BYTE BY BYTE , WHICH IS CHAR(10) */
        SUBSTRING(T1.NAM_NM FROM 1 FOR 1) AS "1F1_C1",
        SUBSTRING(T1.NAM_NM FROM 2 FOR 1) AS "2F1_C1",
        SUBSTRING(T1.NAM_NM FROM 3 FOR 1) AS "3F1_C1",
        SUBSTRING(T1.NAM_NM FROM 4 FOR 1) AS "4F1_C1",
        SUBSTRING(T1.NAM_NM FROM 5 FOR 1) AS "5F1_C1",
        SUBSTRING(T1.NAM_NM FROM 6 FOR 1) AS "6F1_C1",
        SUBSTRING(T1.NAM_NM FROM 7 FOR 1) AS "7F1_C1",
        SUBSTRING(T1.NAM_NM FROM 8 FOR 1) AS "8F1_C1",
        SUBSTRING(T1.NAM_NM FROM 9 FOR 1) AS "9F1_C1",
        SUBSTRING(T1.NAM_NM FROM 10 FOR 1) AS "10F1_C1"
              FROM CUSTOMER T1
            ) as MYTAB
     ON
     MYTAB.COU_ID=T2.COU_ID
     AND MYTAB.NAD_ID=T2.NAD_ID

Anyway, although it looks a little convoluted, it actually works quite well.

Hope it helps.

Regards,

Dave Clough
_______________________
Dave Clough
Database Designer
Express ICS

	<Prev	Next>		<<First	<Prev	Next>	Last>>