Six Stars

How do I iterate a sql from the same db

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 ?

1 ACCEPTED SOLUTION

Accepted Solutions
Six Stars

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

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

worked.png

11 REPLIES
Ten Stars

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

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

-----------
Six Stars

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

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

 

account.png

Ten Stars

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

1) tMySQLInput in Your original post :-)

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

-----------
Six Stars

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

:-)

 

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 ;

trow.png

 

Six Stars

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

I have tried the suggested approach:

new.png

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.

Ten Stars

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

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

-----------
Six Stars

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

I get the same result regardless of the number of columns

Ten Stars

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

do You have space between:

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

?

 

 

-----------
Six Stars

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

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  ;
Ten Stars

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

check the answer for Your other question

-----------
Six Stars

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

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

worked.png