You are not logged in.

#1 2017-02-16 21:12:42

arjunchndr
Guest

arjunchndr said:

How to convert this script to a Talend Job

Tags: [oracle]

How would I convert the PL-SQL Script below to a Talend Job? I tried several approaches and failed to implement the logic for:

  • cast(coalesce( (lag(cast(effdt as date),1) over (PARTITION BY setid,a.product ORDER BY effdt desc)), '12/31/2050') as date)-1 AS rec_through_dt
    case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
    [font=Verdana, Arial, Helvetica, sans-serif][/font]
    [font=Verdana, Arial, Helvetica, sans-serif][/font]
             ,POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
    [font=Verdana, Arial, Helvetica, sans-serif][/font]
    [font=Verdana, Arial, Helvetica, sans-serif][/font]
             ,99999),'HoWmUcH')
    [font=Verdana, Arial, Helvetica, sans-serif][/font]
    [font=Verdana, Arial, Helvetica, sans-serif][/font]
             when 12 then cis_level1_name
    [font=Verdana, Arial, Helvetica, sans-serif][/font]
    [font=Verdana, Arial, Helvetica, sans-serif][/font]
             when 11 then cis_level1_name
    [font=Verdana, Arial, Helvetica, sans-serif][/font]
    [font=Verdana, Arial, Helvetica, sans-serif][/font]
             else cis_level2_name
    [font=Verdana, Arial, Helvetica, sans-serif][/font]
    [font=Verdana, Arial, Helvetica, sans-serif][/font]
        end
    [font=Verdana, Arial, Helvetica, sans-serif][/font]
    [font=Verdana, Arial, Helvetica, sans-serif][/font]
    as cis_level2_name[font=Verdana, Arial, Helvetica, sans-serif][/font]

Really need help with this! 


SOURCE CODE:

[font=Verdana, Arial, Helvetica, sans-serif][b]select
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]cast(effdt as date) as rec_from_dt ,
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]cast(coalesce( (lag(cast(effdt as date),1) over (PARTITION BY setid,a.product ORDER BY effdt desc)), '12/31/2050') as date)-1 AS rec_through_dt,
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]a.*
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b],cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b] 
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]   ,case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]         ,POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]         ,99999),'HoWmUcH')
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]         when 12 then cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]         when 11 then cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]         else cis_level2_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]    end
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]as cis_level2_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b] 
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]   ,case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b],POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b],99999),'HoWmUcH')
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 12 then cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 11 then cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 10 then cis_level2_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]else cis_level3_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]end
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]as cis_level3_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b] 
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b],case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]   ,POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b],99999),'HoWmUcH')
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 12 then cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 11 then cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 10 then cis_level2_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 9 then cis_level3_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]else cis_level4_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]end
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]as cis_level4_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b] 
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]   ,case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b],POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b],99999),'HoWmUcH')
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 12 then cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 11 then cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 10 then cis_level2_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 9 then cis_level3_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 8 then cis_level4_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]else cis_level5_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]end
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]as cis_level5_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b] 
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b] 
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b] 
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b], case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b],POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b],99999),'HoWmUcH')
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 12 then cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 11 then cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 10 then cis_level2_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 9 then cis_level3_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 8 then cis_level4_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 7 then cis_level5_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]else cis_level6_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]end
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]as cis_level6_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b] 
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]   ,case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b],POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b],99999),'HoWmUcH')
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 12 then cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 11 then cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 10 then cis_level2_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 9 then cis_level3_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 8 then cis_level4_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 7 then cis_level5_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 6 then cis_level6_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]else cis_level7_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]end
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]as cis_level7_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b] 
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b] 
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]   ,case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b],POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b],99999),'HoWmUcH')
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 12 then cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 11 then cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 10 then cis_level2_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 9 then cis_level3_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 8 then cis_level4_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 7 then cis_level5_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 6 then cis_level6_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 5 then cis_level7_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 4  then cis_level8_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]else cis_level8_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]end
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]as cis_level8_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b] 
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]   ,case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b],POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b],99999),'HoWmUcH')
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 12 then cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 11 then cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 10 then cis_level2_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 9 then cis_level3_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 8 then cis_level4_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 7 then cis_level5_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 6 then cis_level6_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 5 then cis_level7_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 4  then cis_level8_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]else cis_level10_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]end
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]as cis_level9_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b] 
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]   ,case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b],POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b],99999),'HoWmUcH')
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 12 then cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 11 then cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 10 then cis_level2_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 9 then cis_level3_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 8 then cis_level4_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 7 then cis_level5_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 6 then cis_level6_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 5 then cis_level7_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 4  then cis_level8_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 3 then cis_level9_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]else cis_level10_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]end
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]as cis_level10_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b] 
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]   ,case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b],POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b],99999),'HoWmUcH')
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 12 then cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 11 then cis_level1_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 10 then cis_level2_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 9 then cis_level3_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 8 then cis_level4_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 7 then cis_level5_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 6 then cis_level6_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 5 then cis_level7_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 4  then cis_level8_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 3 then cis_level9_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]when 2 then cis_level10_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]else cis_level11_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]end
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]as cis_level11_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b] 
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]   ,b.product as cis_level_12_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]   ,cis_level13_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]   ,cis_level14_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]   ,cis_level15_name
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]from
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]stg_ps_product_tbl a left  join stg_ps_cis_prdtree_lvl b on a.product =[/b]

[b].product
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b]--where a.eff_status = 'A'
[/b][/font]
[font=Verdana, Arial, Helvetica, sans-serif][/font]
[font=Verdana, Arial, Helvetica, sans-serif][/font]
[font=Verdana, Arial, Helvetica, sans-serif][b];[/b][/font]

#2 2017-02-17 04:14:22

shong
Talend Team


shong said:

Re: How to convert this script to a Talend Job

Just using a tMssqlInput to execute the query does not work?


Email:shong@talend.com
Choose Talend, Enjoy Talend!
New & Event: Talend Help Center
Talend-->the global leader of open source data management and application integration solutions!

Offline

#3 2017-02-17 15:46:45

arjunchndr
Guest

arjunchndr said:

Re: How to convert this script to a Talend Job

@Shong I wanted to know as to how I should go about this if I wanted to achieve this using a T-Map.

#4 2017-02-17 15:50:44

arjunchndr
Guest

arjunchndr said:

Re: How to convert this script to a Talend Job

@Shong Also I'm pulling data from tRedshiftInputs and trying to populate a tRedshiftOutput

#5 2017-02-18 00:21:34

vapukov
Member
438 posts

vapukov said:

Re: How to convert this script to a Talend Job

what You try to achieve? and what Your error? (please edit text fro readable format) smile

Offline

#6 2017-02-21 17:59:15

arjunchndr
Guest

arjunchndr said:

Re: How to convert this script to a Talend Job

@vapukov Hi! So basically I'm trying to populate a dimension called the Product Dimension with the help of two stage tables containing information relevant to the dimension. In the dimension I'm supposed to implement tree flattening for a list of products from the product table. Both the source as well as destination DBs are being hosted on Amazon Redshift. In my case I have a PL-SQL script that I'm trying to convert to a Talend Job. 
Here is the entire old PL-SQL Script:

\c :PGDATABASE - :PGHOST :PGPORT
set search_path=psadm,public;

BEGIN TRANSACTION;

--All data from staging table
create TEMPORARY table revc_product_timephase_dim_data
(
rec_from_dt	,
rec_through_dt	,
setid	,
product	,
effdt	,
eff_status	,
descr	,
descrshort	,
accounting_owner	,
budgetary_only	,
syncid	,
syncdttm	,
descrlong	,
level1	,
level2	,
level3	,
level4	,
level5	,
level6	,
level7	,
level8	,
level9	,
level10	,
level11	,
level12	,
level13	,
level14	,
level15
)
as
select
cast(effdt as date) as rec_from_dt ,
cast(coalesce( (lag(cast(effdt as date),1) over (PARTITION BY setid,a.product ORDER BY effdt desc)), '12/31/2050') as date)-1 AS rec_through_dt,
a.*
,cis_level1_name,cis_level2_name,cis_level3_name,cis_level4_name,cis_level5_name,cis_level6_name,cis_level7_name,cis_level8_name
,cis_level9_name,cis_level10_name,cis_level11_name,cis_level12_name,cis_level13_name,cis_level14_name,cis_level15_name
from
stg_ps_product_tbl a left  join stg_ps_cis_prdtree_lvl b on a.product = b.product
--where a.eff_status = 'A'
;


--Identify NEW rows
create TEMPORARY table revc_product_timephase_dim_new
as
select
setid	,
product	,
rec_from_dt
 from revc_product_timephase_dim_data
except
select
setid	,
product	,
rec_from_dt
 from revc_product_timephase_dim;


--Identify CHANGED rows
create TEMPORARY table revc_product_timephase_dim_changed
as
	(SELECT
rec_from_dt	,
rec_through_dt	,
setid	,
product	,
effdt	,
eff_status	,
descr	,
descrshort	,
accounting_owner	,
budgetary_only	,
syncid	,
syncdttm	,
descrlong	,
level1	,
level2	,
level3	,
level4	,
level5	,
level6	,
level7	,
level8	,
level9	,
level10	,
level11	,
level12	,
level13	,
level14	,
level15
	FROM revc_product_timephase_dim_data a where setid||product||rec_from_dt not in (select setid||product||rec_from_dt from revc_product_timephase_dim_new))
	except
	(SELECT rec_from_dt	,
rec_through_dt	,
setid	,
product	,
effdt	,
eff_status	,
descr	,
descrshort	,
accounting_owner	,
budgetary_only	,
syncid	,
syncdttm	,
descrlong	,
level1	,
level2	,
level3	,
level4	,
level5	,
level6	,
level7	,
level8	,
level9	,
level10	,
level11	,
level12	,
level13	,
level14	,
level15
	FROM revc_product_timephase_dim);



insert into revc_product_timephase_dim
(
rec_from_dt	,
rec_through_dt	,
setid	,
product	,
effdt	,
eff_status	,
descr	,
descrshort	,
accounting_owner	,
budgetary_only	,
syncid	,
syncdttm	,
descrlong	,
level1	,
level2	,
level3	,
level4	,
level5	,
level6	,
level7	,
level8	,
level9	,
level10	,
level11	,
level12	,
level13	,
level14	,
level15
)
select a.*
 from revc_product_timephase_dim_new n, revc_product_timephase_dim_data a
 where n.setid = a.setid
 and n.product = a.product
 and n.rec_from_dt = a.rec_from_dt;



update  revc_product_timephase_dim
set
rec_from_dt	=	B.	rec_from_dt	,
rec_through_dt	=	B.	rec_through_dt	,
setid	=	B.	setid	,
product	=	B.	product	,
effdt	=	B.	effdt	,
eff_status	=	B.	eff_status	,
descr	=	B.	descr	,
descrshort	=	B.	descrshort	,
accounting_owner	=	B.	accounting_owner	,
budgetary_only	=	B.	budgetary_only	,
syncid	=	B.	syncid	,
syncdttm	=	B.	syncdttm	,
descrlong	=	B.	descrlong	,
level1	=	B.	level1	,
level2	=	B.	level2	,
level3	=	B.	level3	,
level4	=	B.	level4	,
level5	=	B.	level5	,
level6	=	B.	level6	,
level7	=	B.	level7	,
level8	=	B.	level8	,
level9	=	B.	level9	,
level10	=	B.	level10	,
level11	=	B.	level11	,
level12	=	B.	level12	,
level13	=	B.	level13	,
level14	=	B.	level14	,
level15	=	B.	level15
from revc_product_timephase_dim_changed  b
where revc_product_timephase_dim.setid = b.setid
 and b.product = revc_product_timephase_dim.product
 and b.rec_from_dt = revc_product_timephase_dim.rec_from_dt;

--Delete rows no longer that were deleted in Correct History Mode
 Insert into revc_product_timephase_dim_deleted
 (select *, sysdate from psadm.revc_product_timephase_dim
 where
 setid ||
 product||
 rec_from_dt
 not in (select setid
 || product
 || rec_from_dt
 from revc_product_timephase_dim_data)
 and setid || '~'
 || product <> 'NA~NA');

 Delete revc_product_timephase_dim
 where
 setid ||
 product ||
 rec_from_dt
 not in (select setid
 || product
 || rec_from_dt
 from revc_product_timephase_dim_data)
 and setid || '~'
|| product <> 'NA~NA';





drop table revc_product_timephase_dim_data;
drop table revc_product_timephase_dim_new;
drop table revc_product_timephase_dim_changed;


--Make the Product tree a balanced heirarchy
------
update  revc_product_timephase_dim set  level2=level1, level3=level1, level4=level1, level5=level1, level6=level1, level7=level1, level8=level1, level9=level1, level10=level1, level11=level1, level12=product
where
 level2 = product;

update  revc_product_timephase_dim set  level3=level2, level4=level2, level5=level2, level6=level2, level7=level2, level8=level2, level9=level2, level10=level2, level11=level2, level12=product
where
 level3 = product;

update  revc_product_timephase_dim set  level4=level3, level5=level3, level6=level3, level7=level3, level8=level3, level9=level3, level10=level3, level11=level3, level12=product
where
 level4 = product;

update  revc_product_timephase_dim set  level5=level4, level6=level4, level7=level4, level8=level4, level9=level4, level10=level4, level11=level4, level12=product
where
 level5 = product;

update  revc_product_timephase_dim set  level6=level5, level7=level5, level8=level5, level9=level5, level10=level5, level11=level5, level12=product
where
 level6 = product;

update  revc_product_timephase_dim set  level7=level6, level8=level6, level9=level6, level10=level6, level11=level6, level12=product
where
 level7 = product;

update  revc_product_timephase_dim set  level8=level7, level9=level7, level10=level7, level11=level7, level12=product
where
 level8 = product;

update  revc_product_timephase_dim set  level9=level8, level10=level8, level11=level8, level12=product
where
 level9 = product;

update  revc_product_timephase_dim set  level10=level9, level11=level9, level12=product
where
 level10 = product;

update  revc_product_timephase_dim set  level11=level10, level12=product
where
 level11 = product;

 END TRANSACTION;

 COMMIT;

Here is the new PL-SQL Script: 

create TEMPORARY table revc_product_timephase_dim_data
(
rec_from_dt       ,
rec_through_dt  ,
setid      ,
product ,
effdt      ,
eff_status            ,
descr     ,
descrshort           ,
accounting_owner           ,
budgetary_only  ,
syncid    ,
syncdttm             ,
descrlong            ,
level1    ,
level2    ,
level3    ,
level4    ,
level5    ,
level6    ,
level7    ,
level8    ,
level9    ,
level10  ,
level11  ,
level12  ,
level13  ,
level14  ,
level15
)
as
select
cast(effdt as date) as rec_from_dt ,
cast(coalesce( (lag(cast(effdt as date),1) over (PARTITION BY setid,a.product ORDER BY effdt desc)), '12/31/2050') as date)-1 AS rec_through_dt,
a.*
,cis_level1_name

   ,case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
         ,POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
         ,99999),'HoWmUcH')
         when 12 then cis_level1_name
         when 11 then cis_level1_name
         else cis_level2_name
    end
as cis_level2_name

   ,case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
,POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
,99999),'HoWmUcH')
when 12 then cis_level1_name
when 11 then cis_level1_name
when 10 then cis_level2_name
else cis_level3_name
end
as cis_level3_name

,case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
   ,POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
,99999),'HoWmUcH')
when 12 then cis_level1_name
when 11 then cis_level1_name
when 10 then cis_level2_name
when 9 then cis_level3_name
else cis_level4_name
end
as cis_level4_name

   ,case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
,POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
,99999),'HoWmUcH')
when 12 then cis_level1_name
when 11 then cis_level1_name
when 10 then cis_level2_name
when 9 then cis_level3_name
when 8 then cis_level4_name
else cis_level5_name
end
as cis_level5_name



, case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
,POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
,99999),'HoWmUcH')
when 12 then cis_level1_name
when 11 then cis_level1_name
when 10 then cis_level2_name
when 9 then cis_level3_name
when 8 then cis_level4_name
when 7 then cis_level5_name
else cis_level6_name
end
as cis_level6_name

   ,case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
,POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
,99999),'HoWmUcH')
when 12 then cis_level1_name
when 11 then cis_level1_name
when 10 then cis_level2_name
when 9 then cis_level3_name
when 8 then cis_level4_name
when 7 then cis_level5_name
when 6 then cis_level6_name
else cis_level7_name
end
as cis_level7_name


   ,case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
,POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
,99999),'HoWmUcH')
when 12 then cis_level1_name
when 11 then cis_level1_name
when 10 then cis_level2_name
when 9 then cis_level3_name
when 8 then cis_level4_name
when 7 then cis_level5_name
when 6 then cis_level6_name
when 5 then cis_level7_name
when 4  then cis_level8_name
else cis_level8_name
end
as cis_level8_name

   ,case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
,POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
,99999),'HoWmUcH')
when 12 then cis_level1_name
when 11 then cis_level1_name
when 10 then cis_level2_name
when 9 then cis_level3_name
when 8 then cis_level4_name
when 7 then cis_level5_name
when 6 then cis_level6_name
when 5 then cis_level7_name
when 4  then cis_level8_name
else cis_level10_name
end
as cis_level9_name

   ,case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
,POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
,99999),'HoWmUcH')
when 12 then cis_level1_name
when 11 then cis_level1_name
when 10 then cis_level2_name
when 9 then cis_level3_name
when 8 then cis_level4_name
when 7 then cis_level5_name
when 6 then cis_level6_name
when 5 then cis_level7_name
when 4  then cis_level8_name
when 3 then cis_level9_name
else cis_level10_name
end
as cis_level10_name

   ,case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
,POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
,99999),'HoWmUcH')
when 12 then cis_level1_name
when 11 then cis_level1_name
when 10 then cis_level2_name
when 9 then cis_level3_name
when 8 then cis_level4_name
when 7 then cis_level5_name
when 6 then cis_level6_name
when 5 then cis_level7_name
when 4  then cis_level8_name
when 3 then cis_level9_name
when 2 then cis_level10_name
else cis_level11_name
end
as cis_level11_name

   ,b.product as cis_level_12_name
   ,cis_level13_name
   ,cis_level14_name
   ,cis_level15_name
from
stg_ps_product_tbl a left  join stg_ps_cis_prdtree_lvl b on a.product = b.product
--where a.eff_status = 'A'
;

#7 2017-02-21 19:41:01

arjunchndr
Guest

arjunchndr said:

Re: How to convert this script to a Talend Job

This is the product tree balanced hierarchy code snippet from the new script:

case regexp_count(substring(cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH'
         ,POSITION(b.product IN cis_level1_name || 'HoWmUcH' || cis_level2_name || 'HoWmUcH' || cis_level3_name || 'HoWmUcH' ||cis_level4_name || 'HoWmUcH' ||cis_level5_name || 'HoWmUcH' ||cis_level6_name || 'HoWmUcH' ||cis_level7_name || 'HoWmUcH' ||cis_level8_name || 'HoWmUcH' ||cis_level9_name || 'HoWmUcH' ||cis_level10_name || 'HoWmUcH' ||cis_level11_name || 'HoWmUcH' ||cis_level12_name || 'HoWmUcH' )
         ,99999),'HoWmUcH')
         when 12 then cis_level1_name
         when 11 then cis_level1_name
         else cis_level2_name
    end
as cis_level2_name

This is the product tree balanced hierarchy code snippet from the old script:

update  revc_product_timephase_dim set  level2=level1, level3=level1, level4=level1, level5=level1, level6=level1, level7=level1, level8=level1, level9=level1, level10=level1, level11=level1, level12=product
where
 level2 = product;

update  revc_product_timephase_dim set  level3=level2, level4=level2, level5=level2, level6=level2, level7=level2, level8=level2, level9=level2, level10=level2, level11=level2, level12=product
where
 level3 = product;

update  revc_product_timephase_dim set  level4=level3, level5=level3, level6=level3, level7=level3, level8=level3, level9=level3, level10=level3, level11=level3, level12=product
where
 level4 = product;

update  revc_product_timephase_dim set  level5=level4, level6=level4, level7=level4, level8=level4, level9=level4, level10=level4, level11=level4, level12=product
where
 level5 = product;

update  revc_product_timephase_dim set  level6=level5, level7=level5, level8=level5, level9=level5, level10=level5, level11=level5, level12=product
where
 level6 = product;

update  revc_product_timephase_dim set  level7=level6, level8=level6, level9=level6, level10=level6, level11=level6, level12=product
where
 level7 = product;

update  revc_product_timephase_dim set  level8=level7, level9=level7, level10=level7, level11=level7, level12=product
where
 level8 = product;

update  revc_product_timephase_dim set  level9=level8, level10=level8, level11=level8, level12=product
where
 level9 = product;

update  revc_product_timephase_dim set  level10=level9, level11=level9, level12=product
where
 level10 = product;

update  revc_product_timephase_dim set  level11=level10, level12=product
where
 level11 = product;

Board footer

Talend Contributor Agreement - Talend Website Privacy Policy