How do I iterate a sql from the same db

[Anonymous]

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 ?

[vapukov]

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

[Anonymous]

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

[vapukov]

1) tMySQLInput in Your original post 🙂

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

[Anonymous]

🙂

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 ;

[Anonymous]

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.

[vapukov]

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

[Anonymous]

I get the same result regardless of the number of columns

[vapukov]

do You have space between:

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

?

[Anonymous]

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  ;