Archives of the TeradataForum

Message Posted: Wed, 09 May 2007 @ 15:23:55 GMT

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

Problem: There is a string column that has data like

     A_B_C_D_E_F_G_H_I_J_K
     A
     A_B
     A_B_C

Here the delimiter is underscore '_'

We mean "a part" as that part of string which is delimited by '_'. (In first string A is a part, B is a part, C is a part and so on)

Requirement is to split the string into separate parts (Maximum of 11 parts and Minimum of 1 part) depending on the available string.

The code that I have is not working since it errors saying "3712: Insufficient memory for Plastic Steps for this request"

For this error DBAs are saying stepssegmentsize field is already set at maximum value.

Does anyone have already worked on similar requirement earlier?? I am just looking whether if anyone has already resolved such a thing, I can reuse.

CODE that I tried:

     sel
      rt_dsc
     ,POSITION('_' IN rt_dsc) FIRST_POS
     ,CASE
      WHEN FIRST_POS > 0
      THEN
               TRIM(TRAILING '_' FROM SUBSTR(rt_dsc, 1, FIRST_POS))
      ELSE
               rt_dsc
      END AS FIRST_PART
     ,SUBSTR(rt_dsc, FIRST_POS+1) EXCPT_1_RT_DSC
     ,POSITION('_' IN EXCPT_1_RT_DSC) SECOND_POS
     ,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
     ,SUBSTR(EXCPT_1_RT_DSC, SECOND_POS+1) EXCPT_2_RT_DSC
     ,POSITION('_' IN EXCPT_2_RT_DSC) THIRD_POS
     ,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
     ,SUBSTR(EXCPT_2_RT_DSC, THIRD_POS+1) EXCPT_3_RT_DSC
     ,POSITION('_' IN EXCPT_3_RT_DSC) FOURTH_POS
     ,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
     ,SUBSTR(EXCPT_3_RT_DSC, FOURTH_POS+1) EXCPT_4_RT_DSC
     ,POSITION('_' IN EXCPT_4_RT_DSC) FIFTH_POS
     ,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
     ,SUBSTR(EXCPT_4_RT_DSC, FIFTH_POS+1) EXCPT_5_RT_DSC
     ,POSITION('_' IN EXCPT_5_RT_DSC) SIXTH_POS
     ,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
     ,SUBSTR(EXCPT_5_RT_DSC, SIXTH_POS+1) EXCPT_6_RT_DSC
     ,POSITION('_' IN EXCPT_6_RT_DSC) SEVENTH_POS
     ,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
     ,SUBSTR(EXCPT_6_RT_DSC, SEVENTH_POS+1) EXCPT_7_RT_DSC
     ,POSITION('_' IN EXCPT_7_RT_DSC) EIGHTH_POS
     ,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
     ,SUBSTR(EXCPT_7_RT_DSC, EIGHTH_POS+1) EXCPT_8_RT_DSC
     ,POSITION('_' IN EXCPT_8_RT_DSC) NINTH_POS
     ,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
     ,SUBSTR(EXCPT_8_RT_DSC, NINTH_POS+1) EXCPT_9_RT_DSC
     ,POSITION('_' IN EXCPT_9_RT_DSC) TENTH_POS
     ,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
     ,SUBSTR(EXCPT_9_RT_DSC, TENTH_POS+1) EXCPT_10_RT_DSC
     ,POSITION('_' IN EXCPT_10_RT_DSC) ELEVENTH_POS
     ,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
     cdp_sandbox_.v_dim_rt_dtl;

Thanks & Regards,

Vivek

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