![]() |
|
![]() |
![]() |
Archives of the TeradataForumMessage Posted: Wed, 09 May 2007 @ 15:23:55 GMT
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
| ||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||
![]() | ||||||||||||||||||||||||||||||||||||||||||||||||
Copyright 2016 - All Rights Reserved | ||||||||||||||||||||||||||||||||||||||||||||||||
Last Modified: 15 Jun 2023 | ||||||||||||||||||||||||||||||||||||||||||||||||