Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.