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
Fifteen Stars TRF
Fifteen 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

All Replies
Fifteen Stars TRF
Fifteen 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch