How to build a Talend Job for this query

[Anonymous]

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

[TRF · Accepted solution]

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.