How to build a Talend Job for this query

Highlighted
Four Stars

How to build a Talend Job for this query

select
A.setid ,
A.cust_id             ,
A.cust_status     ,
A.cust_status_dt              ,
A.stcust_days    ,
A.expiration_date           ,
A.exp_pending ,
A.template_addr             ,
A.customer_type             ,
A.since_dt          ,
A.add_dt             ,
A.name1              ,
A.nameshort     ,
A.name2              ,
A.name3              ,
A.name1_ac       ,
A.name2_ac       ,
A.name3_ac       ,
COALESCE (SUBSET.address_seq_num,0)      ,
SUBSET.address1 ,
SUBSET.address2 ,
SUBSET.address3 ,
SUBSET.address4 ,
SUBSET.city ,
SUBSET.state ,
SUBSET.postal ,
SUBSET.country ,
A.corporate_setid           ,
A.corporate_cust_id       ,
A.parent_setid ,
A.parent_cust_id             ,
A.remit_from_setid       ,
A.remit_from_cust_id   ,
A.crspd_setid    ,
A.crspd_cust_id                ,
A.consol_bus_unit          ,
A.vendor_setid                ,
A.vendor_id       ,
A.refer_to_setid              ,
A.refer_to_cust_id          ,
A.review_days  ,
A.former_name_1           ,
A.former_name_2           ,
A.cntct_seq_num            ,
A.subcust_use  ,
A.subcust_qual1              ,
A.subcust_qual2              ,
A.cur_rt_type    ,
A.currency_cd   ,
A.ship_to_flg     ,
A.bill_to_flg       ,
A.sold_to_flg     ,
A.broker_flg      ,
A.indirect_cust_flg         ,
A.cust_level       ,
A.address_seq_sold       ,
A.cntct_seq_sold             ,
A.address_seq_ship       ,
A.cntct_seq_ship             ,
A.address_seq_brk         ,
A.address_seq_ind         ,
A.hold_update_sw         ,
A.roleuser          ,
A.taxpayer_id   ,
A.web_url           ,
A.stock_symbol                ,
A.reports_to_sponsor   ,
A.spnsr_level    ,
A.spnsr_type     ,
A.spnsr_program             ,
A.cost_liv_incr_pct         ,
A.cfda_nbr          ,
A.allow_copi     ,
A.multi_pi          ,
A.foreign_flag   ,
A.fed_sp_type  ,
A.ofac_status    ,
A.reason_cd       ,
A.fss_offset_days            ,
A.gm_federal_flag          ,
A.us_flag_carrier             ,
A.loc_indicator ,
A.gm_loc_sponsor_id    ,
A.ggov_flg          ,
A.fa_spnsr_base_id        ,
A.gm_del_loc_refs_sw  ,
A.grants_sponsor_flg     ,
A.spnsr_salary_cap         ,
A.federal_indicator        ,
A.trading_partner           ,
A.ipac_interface              ,
A.cntct_seq_ipac             ,
A.customer_alc                ,
A.customer_do ,
A.symbol_req   ,
A.source_system             ,
A.fed_share_pct              ,
A.cont_allow_pct            ,
A.cont_no_allw_pct       ,
A.cust_field_c1_a            ,
A.cust_field_c1_b            ,
A.cust_field_c1_c            ,
A.cust_field_c1_d            ,
A.cust_field_c2 ,
A.cust_field_c4 ,
A.cust_field_c6 ,
A.cust_field_c8 ,
A.cust_field_c10_a          ,
A.cust_field_c10_b         ,
A.cust_field_c10_c          ,
A.cust_field_c10_d         ,
A.cust_field_c30_a          ,
A.cust_field_c30_b         ,
A.cust_field_c30_c          ,
A.cust_field_c30_d         ,
A.cust_field_n12_a         ,
A.cust_field_n12_b         ,
A.cust_field_n12_c         ,
A.cust_field_n12_d         ,
A.cust_field_n15_a         ,
A.cust_field_n15_b         ,
A.cust_field_n15_c         ,
A.cust_field_n15_d         ,
A.last_maint_oprid         ,
A.date_last_maint ,
phone,
D.Country_Name		     ,
D.DUNS			     ,
D.Customer_ID_Number	     ,
D.Customer_Defined_Field_1     ,
D.Customer_Defined_Field_2     ,
D.Customer_Defined_Field_3     ,
D.Customer_Defined_Field_4     ,
D.Sequence_Number		     ,
D.Verified_DUNS		     ,
D.DUNS_DB			     ,
D.Business_Name		     ,
D.Primary_Address_Line_1	     ,
D.Primary_Address_Line_2	     ,
D.City_Name		     ,
D.State		     ,
D.Zip_Code		     ,
D.State_Province_Name	     ,
D.County_Name			    ,
D.Global_Ultimate_DUNS_Number	    ,
D.Global_Ultimate_Business_Name	    ,
D.Global_Ultimate_Country_Code	    ,
D.Global_Ultimate_State_Abbreviation  ,
D.Domestic_Ultimate_DUNS_Number	    ,
D.Domestic_Ultimate_Business_Name	    ,
D.Domestic_Ultimate_Country_Code	    ,
D.Domestic_Ultimate_State_Abbreviation,
D.Parent_DUNS_Number		    ,
D.Headquarters_DUNS_Number	    ,
D.Parent_Business_Name		    ,
D.Parent_Headquarters_Country_Code       ,
D.Parent_Headquarters_State_Abbreviation ,
D.Major_Industry_Category		          ,
D.Line_of_Business		          ,
D.Marketing_PreScreen		          ,
D.BEMFAB	                ,
D.Primary_NAICS_1_1_Code		          ,
D.Small_Business_Indicator	          ,
D.Minority_Owned_and_Operatored_Indicator	  ,
D.Public_Private_Indicator		  ,
D.Year_Started				  ,
D.Sales_Volume				  ,
D.Currency_Code				  ,
D.Latitude				  ,
D.Longitude

from
psadm.stg_ps_customer A
left outer join
(select B.*
FROM psadm.stg_ps_customer A
  left outer join psadm.stg_ps_cust_address B
  on A.SETID = B.SETID
     AND A.CUST_ID = B.CUST_ID
     and a.address_seq_num = b.address_seq_num
     and B.eff_status = 'A'
                inner join  (SELECT B_ED.SETID,B_ED.CUST_ID,B_ED.ADDRESS_SEQ_NUM, MAX(B_ED.EFFDT) MAX_EFFDT
  FROM psadm.stg_ps_cust_address B_ED
                    WHERE B_ED.EFFDT <= current_date
          GROUP BY B_ED.SETID,B_ED.CUST_ID,B_ED.ADDRESS_SEQ_NUM) corelated_innq1
                                  on B.setid=corelated_innq1.setid
      and B.CUST_ID=corelated_innq1.CUST_ID
      and B.ADDRESS_SEQ_NUM=corelated_innq1.ADDRESS_SEQ_NUM
      and B.EFFDT=corelated_innq1.MAX_EFFDT
      order by a.setid, a.cust_id, a.address_seq_num, b.effdt) subset
                  on A.SETID = subset.SETID
     AND A.CUST_ID = subset.CUST_ID
     and A.address_seq_num = subset.address_seq_num
left outer join stg_dnbi_optimizer_crm_cust_full D on
D.customer_defined_field_1 = A.cust_id and D.customer_id_number = A.setid
;

I'm primarily facing an issue with respect to building a Talend Job for this part of the above code:

(select B.*
FROM psadm.stg_ps_customer A
  left outer join psadm.stg_ps_cust_address B
  on A.SETID = B.SETID
     AND A.CUST_ID = B.CUST_ID
     and a.address_seq_num = b.address_seq_num
     and B.eff_status = 'A'
                inner join  (SELECT B_ED.SETID,B_ED.CUST_ID,B_ED.ADDRESS_SEQ_NUM, MAX(B_ED.EFFDT) MAX_EFFDT
  FROM psadm.stg_ps_cust_address B_ED
                    WHERE B_ED.EFFDT <= current_date
          GROUP BY B_ED.SETID,B_ED.CUST_ID,B_ED.ADDRESS_SEQ_NUM) corelated_innq1
                                  on B.setid=corelated_innq1.setid
      and B.CUST_ID=corelated_innq1.CUST_ID
      and B.ADDRESS_SEQ_NUM=corelated_innq1.ADDRESS_SEQ_NUM
      and B.EFFDT=corelated_innq1.MAX_EFFDT
      order by a.setid, a.cust_id, a.address_seq_num, b.effdt) subset

Accepted Solutions
Highlighted
Sixteen Stars
Sixteen Stars

Re: How to build a Talend Job for this query

Try to copy/pass the whole select into a t<DB>Input (<DB> depends on yours).

The select must be enclosed between " and remove the final ";".

Click on the Guess schema to get the schema according to your database.


TRF

View solution in original post


All Replies
Highlighted
Sixteen Stars
Sixteen Stars

Re: How to build a Talend Job for this query

Try to copy/pass the whole select into a t<DB>Input (<DB> depends on yours).

The select must be enclosed between " and remove the final ";".

Click on the Guess schema to get the schema according to your database.


TRF

View solution in original post

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog