Archives of the TeradataForum

Message Posted: Thu, 24 May 2007 @ 14:16:37 GMT

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

<-- Anonymously Posted: Tuesday, May 22, 2007 14:20 -->

Try to re-write the query using derived table. Move the position and substring functions in a derived table then refer the derived table columns in the outer query. You may get a warning that code has been executed in interpretive EVL mode. As the request is very big it executed in evl interpretive mode. You can ignore this warning.

Re-written query:

     sel
      rt_dsc
     ,CASE
      WHEN FIRST_POS > 0
      THEN
               TRIM(TRAILING '_' FROM SUBSTR(rt_dsc, 1, FIRST_POS))  ELSE
               rt_dsc
      END AS FIRST_PART
     ,CASE  WHEN SECOND_POS > 0  THEN
               TRIM(TRAILING '_' FROM SUBSTR(EXCPT_1_RT_DSC, 1, SECOND_POS))
     WHEN FIRST_POS > 0 AND SECOND_POS = 0  THEN
               EXCPT_1_RT_DSC
      ELSE
               ' '
      END AS SECOND_PART
     ,CASE  WHEN THIRD_POS > 0  THEN
               TRIM(TRAILING '_' FROM SUBSTR(EXCPT_2_RT_DSC, 1, THIRD_POS))
     WHEN SECOND_POS > 0 AND THIRD_POS = 0  THEN
               EXCPT_2_RT_DSC
      ELSE
               ' '
      END AS THIRD_PART
     ,CASE  WHEN FOURTH_POS > 0  THEN
               TRIM(TRAILING '_' FROM SUBSTR(EXCPT_3_RT_DSC, 1, FOURTH_POS))
     WHEN THIRD_POS > 0 AND FOURTH_POS = 0  THEN
               EXCPT_3_RT_DSC
      ELSE
               ' '
      END AS FOURTH_PART
     ,CASE  WHEN FIFTH_POS > 0  THEN
               TRIM(TRAILING '_' FROM SUBSTR(EXCPT_4_RT_DSC, 1, FIFTH_POS))
     WHEN FOURTH_POS > 0 AND FIFTH_POS = 0  THEN
               EXCPT_4_RT_DSC
      ELSE
               ' '
      END AS FIFTH_PART
     ,CASE  WHEN SIXTH_POS > 0  THEN
               TRIM(TRAILING '_' FROM SUBSTR(EXCPT_5_RT_DSC, 1, SIXTH_POS))
     WHEN FIFTH_POS > 0 AND SIXTH_POS = 0  THEN
               EXCPT_5_RT_DSC
      ELSE
               ' '
      END AS SIXTH_PART
     ,CASE  WHEN SEVENTH_POS > 0  THEN
               TRIM(TRAILING '_' FROM SUBSTR(EXCPT_6_RT_DSC, 1, SEVENTH_POS))
     WHEN SIXTH_POS > 0 AND SEVENTH_POS = 0  THEN
               EXCPT_6_RT_DSC
      ELSE
               ' '
      END AS SEVENTH_PART
     ,CASE  WHEN EIGHTH_POS > 0  THEN
               TRIM(TRAILING '_' FROM SUBSTR(EXCPT_7_RT_DSC, 1, EIGHTH_POS))
     WHEN SEVENTH_POS > 0 AND EIGHTH_POS = 0  THEN
               EXCPT_7_RT_DSC
      ELSE
               ' '
      END AS EIGHTH_PART
     ,CASE  WHEN NINTH_POS > 0  THEN
               TRIM(TRAILING '_' FROM SUBSTR(EXCPT_8_RT_DSC, 1, NINTH_POS))
     WHEN EIGHTH_POS > 0 AND NINTH_POS = 0  THEN
               EXCPT_8_RT_DSC
      ELSE
               ' '
      END AS NINTH_PART
     ,CASE  WHEN TENTH_POS > 0  THEN
               TRIM(TRAILING '_' FROM SUBSTR(EXCPT_9_RT_DSC, 1, TENTH_POS))
     WHEN NINTH_POS > 0 AND TENTH_POS = 0  THEN
               EXCPT_9_RT_DSC
      ELSE
               ' '
      END AS TENTH_PART
     ,CASE  WHEN ELEVENTH_POS > 0  THEN
               TRIM(TRAILING '_' FROM SUBSTR(EXCPT_10_RT_DSC,
     1,ELEVENTH_POS)) WHEN TENTH_POS > 0 AND ELEVENTH_POS = 0  THEN
               EXCPT_10_RT_DSC
      ELSE
               ' '
      END AS ELEVENTH_PART
     from
     (
     sel
      rt_dsc
     ,POSITION('_' IN rt_dsc) FIRST_POS
     ,SUBSTR(rt_dsc, FIRST_POS+1) EXCPT_1_RT_DSC
     ,POSITION('_' IN EXCPT_1_RT_DSC) SECOND_POS
     ,SUBSTR(EXCPT_1_RT_DSC, SECOND_POS+1) EXCPT_2_RT_DSC
     ,POSITION('_' IN EXCPT_2_RT_DSC) THIRD_POS
     ,SUBSTR(EXCPT_2_RT_DSC, THIRD_POS+1) EXCPT_3_RT_DSC
     ,POSITION('_' IN EXCPT_3_RT_DSC) FOURTH_POS
     ,SUBSTR(EXCPT_3_RT_DSC, FOURTH_POS+1) EXCPT_4_RT_DSC
     ,POSITION('_' IN EXCPT_4_RT_DSC) FIFTH_POS
     ,SUBSTR(EXCPT_4_RT_DSC, FIFTH_POS+1) EXCPT_5_RT_DSC
     ,POSITION('_' IN EXCPT_5_RT_DSC) SIXTH_POS
     ,SUBSTR(EXCPT_5_RT_DSC, SIXTH_POS+1) EXCPT_6_RT_DSC
     ,POSITION('_' IN EXCPT_6_RT_DSC) SEVENTH_POS
     ,SUBSTR(EXCPT_6_RT_DSC, SEVENTH_POS+1) EXCPT_7_RT_DSC
     ,POSITION('_' IN EXCPT_7_RT_DSC) EIGHTH_POS
     ,SUBSTR(EXCPT_7_RT_DSC, EIGHTH_POS+1) EXCPT_8_RT_DSC
     ,POSITION('_' IN EXCPT_8_RT_DSC) NINTH_POS
     ,SUBSTR(EXCPT_8_RT_DSC, NINTH_POS+1) EXCPT_9_RT_DSC
     ,POSITION('_' IN EXCPT_9_RT_DSC) TENTH_POS
     ,SUBSTR(EXCPT_9_RT_DSC, TENTH_POS+1) EXCPT_10_RT_DSC
     ,POSITION('_' IN EXCPT_10_RT_DSC) ELEVENTH_POS
     from
     v_dim_rt_dtl)dt1;

      *** Query completed. 4 rows found. 12 columns returned.
      *** Warning: 3705 Request executed in interpretive EVL mode.
      *** Total elapsed time was 57 seconds.

     rt_dsc                FIRST_PART      SECOND_PART      THIRD_PAR
     --------------------- --------------- ---------------- ---------
     A_B_C_D               A               B                C
     A_B_C_D_E_F_G         A               B                C
     D_E_F                 D               E                F
     A_B_C_D_E_F_G_H_I_J_K A               B                C

Regards

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