topic Re: How do I iterate a sql from the same db in Talend Studio

How do I iterate a sql from the same db

Anonymous — Sat, 16 Nov 2024 09:28:19 GMT

Hi community

I have the following scenario:

Select id from db1

then iterate id to select from the same db1 source and table

I have placed the following in my job

tmysqlinput -> iterateflow -> tfixedflowinput -> tmysqlrow-> tmap->tmysqloutput

Does this look correct ?

Re: How do I iterate a sql from the same db

vapukov — Mon, 31 Jul 2017 14:03:22 GMT

There are many variant to achieve the same goal

is it work as expected?

alternative:

tMySQLInput -> tFlowToIterate -> tJavaFlex (just as dummy) -> OnComponentOk -> tMySQLInput ->tMap -> tMySQLOutput

Re: How do I iterate a sql from the same db

Anonymous — Mon, 31 Jul 2017 17:04:16 GMT

Thank you, but I am trying to resolve the flow without using a conditional flow.

The tpostgresrow is not providing results, all results from the select return with null

Re: How do I iterate a sql from the same db

vapukov — Tue, 01 Aug 2017 02:30:49 GMT

1) tMySQLInput in Your original post 🙂

2) but this is not important, could You attach tPostgreqlRow settings?

Re: How do I iterate a sql from the same db

Anonymous — Tue, 01 Aug 2017 07:25:04 GMT

🙂

Settings for tPostgreqlRow:

sql:

select distinct (account_sk) ,
service_manager as Service_Manager,
account_id as Account_ID,
name as master_account_name,
parent_account_id as parent_account_id,
_class as account_class,
--distinct(service_manager),
master_account_sk,
a12.Contact_Account_Role AS Contact_Account_Role,
a12.CONTACT_CELL_NUMBER AS CONTACT_CELL_NUMBER,
a12.CONTACT_EMAIL AS CONTACT_EMAIL,
a12.Contact_Employee_Flag AS Contact_Employee_Flag,
a12.Contact_Role AS Contact_Role,
a12.Contact_Type AS Contact_Type,
a12.Contact_Telephone_Number AS Contact_Telephone_Number,
a12.CONTACT_ID AS CONTACT_ID,
max(a12.CONTACT_FIRST_NAME || ' ' || a12.CONTACT_LAST_NAME) AS customer_contact,
max(a12.CONTACT_FIRST_NAME) AS CONTACT_FIRST_NAME,
max(a12.CONTACT_LAST_NAME) AS CONTACT_LAST_NAME,
a12.Contact_Status AS Contact_Status
from edw_dev.d_customer_account
join edw_dev.mdm_siebel_contacts_vw a12
on (account_id = a12.Contact_Account_ID)
where account_id = '"+((String)globalMap.get("row1.account_id"))+"'
and a12.Contact_Status not like 'Inactive'
--and a11.scd_end is null
and a12.Contact_Type in ('Technical Contact', '|Technical Contact', 'Buying Centre')
group by
-- account_sk,
account_id,
name,
a12.Contact_Account_Role,
a12.CONTACT_CELL_NUMBER,
a12.CONTACT_EMAIL,
a12.Contact_Employee_Flag,
a12.Contact_Role,
a12.Contact_Type,
a12.Contact_Telephone_Number,
service_manager,
account_sk,
a12.CONTACT_ID,
a12.Contact_Status,
parent_account_id,
_class,
master_account_sk ;

Re: How do I iterate a sql from the same db

Anonymous — Tue, 01 Aug 2017 08:16:00 GMT

I have tried the suggested approach:

but as you can see no rows on the second input.

I think it is due to the select. when I hardcode the account_id the job works.

So the problem lies either in my select parameter: where account_id = '"+((String)globalMap.get("row1.account_id"))+"'

or where the conditional line needs to pass the account_id parameter.

Re: How do I iterate a sql from the same db

vapukov — Tue, 01 Aug 2017 14:07:31 GMT

first of all - make select same with schema

- or reduce number and order of columns

- or add columns to the schema

in schema - 3 column

in query - much more

construction is work, but this is could be (could be) a reason

Re: How do I iterate a sql from the same db

Anonymous — Tue, 01 Aug 2017 15:16:46 GMT

I get the same result regardless of the number of columns

Re: How do I iterate a sql from the same db

vapukov — Tue, 01 Aug 2017 15:31:44 GMT

do You have space between:

where account_id = '"+((String)globalMap.get("row1.account_id"))+"'
and a12.Contact_Status not like 'Inactive'

Re: How do I iterate a sql from the same db

Anonymous — Tue, 01 Aug 2017 15:39:49 GMT

Sorry I am not sure I understand

This is my last used code exactly with spacing:

select  distinct (account_sk) ,
  service_manager as Service_Manager,
  account_id as Account_ID,    
  name as master_account_name,
 	parent_account_id as parent_account_id,
	_class as account_class,
  --distinct(service_manager),
  master_account_sk,
  a12.Contact_Account_Role AS Contact_Account_Role,
	a12.CONTACT_CELL_NUMBER AS CONTACT_CELL_NUMBER,
	a12.CONTACT_EMAIL AS CONTACT_EMAIL,
	a12.Contact_Employee_Flag AS Contact_Employee_Flag,
	a12.Contact_Role AS Contact_Role,
	a12.Contact_Type AS Contact_Type,
	a12.Contact_Telephone_Number AS Contact_Telephone_Number,
	a12.CONTACT_ID AS CONTACT_ID,
	max(a12.CONTACT_FIRST_NAME || ' ' || a12.CONTACT_LAST_NAME) AS customer_contact,
	max(a12.CONTACT_FIRST_NAME) AS CONTACT_FIRST_NAME,
	max(a12.CONTACT_LAST_NAME) AS CONTACT_LAST_NAME,
	a12.Contact_Status AS Contact_Status
	 from edw_dev.d_customer_account
 	join	edw_dev.mdm_siebel_contacts_vw	a12
	  on 	(account_id = a12.Contact_Account_ID)
	  where account_id in ( '"+((String)globalMap.get("row1.account_id"))+"' )
	  and a12.Contact_Status not like 'Inactive'
	  --and a11.scd_end is null
    and a12.Contact_Type in ('Technical Contact', '|Technical Contact', 'Buying Centre')
	  group by	
	 -- account_sk,
	  account_id,
	  name,
	a12.Contact_Account_Role,
	a12.CONTACT_CELL_NUMBER,
	a12.CONTACT_EMAIL,
	a12.Contact_Employee_Flag,
	a12.Contact_Role,
	a12.Contact_Type,
	a12.Contact_Telephone_Number,
	service_manager,
	account_sk,
	a12.CONTACT_ID,
	a12.Contact_Status,
	parent_account_id,
	_class,
	master_account_sk  ;

Re: How do I iterate a sql from the same db

vapukov — Wed, 02 Aug 2017 01:33:34 GMT

check the answer for Your other question

Re: How do I iterate a sql from the same db

Anonymous — Wed, 02 Aug 2017 07:12:12 GMT

Not the same question, the other question relates to tPostgresqlRow usage and failure.

I managed to get the job to work - thanks for your input and assistance, it did help solve the problem