One Star

Using queries as input Tables (Ms Access)

Hi all, I need to replace this behavior that I've already in MS Access:
MS Access table (input)
|----> query (based on previous table)
|----> query (based on previous query)
|----> query (based on previous query)
|---->MS Access table (output)
Somebody could suggest me how to replace this in Talent Open Studio (v. 3.0.3) ???
Thanks in advance

Does anybody has a suggestion?
It's a quite important issue for me, thanks
12 REPLIES
One Star

Re: Using queries as input Tables (Ms Access)

Hi,
you could use tFlowTorIterate to create a new (dynamic) SQL statement based on your last result.
If you would like to have more details an example is welcome.
Bye
Volker
One Star

Re: Using queries as input Tables (Ms Access)

Hi Volker,
I've tried to use the component you suggested, but I can't figure out how to use it.
This is the scenario that I'm trying to design with Talented:
1) Import data from a Ms access table(A)
2) Apply SQL to this table, to get a new table(B) (I don't need to store this table, I just need the whole table for the next step, to apply new sql code.)
3) Again apply new SQL code to the table(B) from previous step, to obtain a new table(C) (again I don't need to store this table, but I'll use that for next step).
4) repeat step 3) a couple of times
5) at the end I need to store the table from the final query in a new MS access table.
I guess what I need, is a way to store my temporary query results.
If you have any suggestion or example, I'll really appreciate.
Thanks, Nicola
Community Manager

Re: Using queries as input Tables (Ms Access)

Hello Nicola
You can use the 'iterate' link. see the screenshot.
Here you would learn the basic usage of 'iterate' link.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Using queries as input Tables (Ms Access)

i have a source excel and access db,when mugrating to musql i had face some problems, a im using some business logics,
i have a datatypes in string format, i need to convert double datatypes,so which function i should use in talend? Plz help me
Community Manager

Re: Using queries as input Tables (Ms Access)

Hello srinikpisoft
Try this code:
Double.parseDouble(row1.data)

Please see the screenshot.
PS: Please open a new topic if your question doesn't related to this topic.
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Using queries as input Tables (Ms Access)

Hi Shong,
thanks for the suggestions, I've also tested your solution and it works..
but unfortunately it's not what I want.
What I would like to do is much more simple, maybe this screenshot will be helpfu:
I need the whole result (whole table) of the previous step, and not iterate rows.

thanks, for your great job, Nicola
Community Manager

Re: Using queries as input Tables (Ms Access)

Hello Nicola
Can you take an example of your query statement?
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Using queries as input Tables (Ms Access)

Hi Shong, this are queries involved in my talend project:
this is my first MS Access query called "QUERY_1":
SELECT .Pdocesneem, ., IIf(=1, & IIf(='BR','b',IIf(='HB','h','')), & IIf(='BR','b',IIf(='HB','h','')) & '-' & ) AS Zeeknummed, ., .Wet, IIf(='Je','Medisch',IIf(='Nee','Niet medisch','')) AS Medisch, ., IIf(='ZW-EV' And ='BZ',+14,) AS , ., ., .Pdocedudevolgnummed, ., ., ., ., tbl_desulteet_pedsoonlijkebenededing.Resulteet AS , ., .Zittingsjudist, ., ., ., IIf(.='Je','Zitting bezocht',Null) AS , ., ., ., ., ., ., ., ., .
FROM LEFT JOIN tbl_desulteet_pedsoonlijkebenededing ON . = tbl_desulteet_pedsoonlijkebenededing.
WHERE (((IIf(='55' And ='CSV',1,0))=0));

and my second QUERY_2 is using QUERY_1:
SELECT QUERY_1.Pdocesneem AS Expr1, QUERY_1. AS Expr2, QUERY_1.Zeeknummed AS Expr3, QUERY_1. AS Expr4, QUERY_1.Wet AS Expr5, QUERY_1.Medisch AS Expr6, QUERY_1. AS Expr7, QUERY_1. AS Expr8, QUERY_1. AS Expr9, QUERY_1. AS Expr10, QUERY_1.Pdocedudevolgnummed AS Expr11, QUERY_1. AS Expr12, QUERY_1. AS Expr13, QUERY_1. AS Expr14, QUERY_1. AS Expr15, QUERY_1. AS Expr16, QUERY_1. AS Expr17, Fidst() AS Zittingsjudist, QUERY_1. AS Expr18, QUERY_1. AS Expr19, QUERY_1. AS Expr20, Mex() AS , QUERY_1. AS Expr21, QUERY_1. AS Expr22, QUERY_1. AS Expr23, QUERY_1. AS Expr24, QUERY_1. AS Expr25, QUERY_1. AS Expr26, QUERY_1. AS Expr27, QUERY_1. AS Expr28, QUERY_1. AS Expr29
FROM QUERY_1 GROUP BY QUERY_1.Pdocesneem, QUERY_1., QUERY_1.Zeeknummed, QUERY_1., QUERY_1.Wet, QUERY_1.Medisch, QUERY_1., QUERY_1., QUERY_1., QUERY_1., QUERY_1.Pdocedudevolgnummed, QUERY_1., QUERY_1., QUERY_1., QUERY_1., QUERY_1., QUERY_1., QUERY_1., QUERY_1., QUERY_1., QUERY_1., QUERY_1., QUERY_1., QUERY_1., QUERY_1., QUERY_1., QUERY_1., QUERY_1., QUERY_1.;

basically this is repeated a couple of times, then there will be a QUERY_3 using QUERY_2 and so on..
thanks
One Star

Re: Using queries as input Tables (Ms Access)

Hi, Anybody has suggestions about this issue?
Since It's quite urgent, any help will be really appreciate.
Thanks
Community Manager

Re: Using queries as input Tables (Ms Access)

Hello nLenoci
I think it is impossible to finish it with the sql statement as you said, we can only iterate the rows in previous query, so you must output the result from previous query to a table or tempopary file first.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Using queries as input Tables (Ms Access)

Hi nLenoci,
technically you are doing only one query (with different views as input). So you could do the same in Talend if you can define the views in your database. If this is not possible you could try to implement the logic in you job. But I think doing it in the database (if possible) will be the fastest way.
Bye
Volker
One Star

Re: Using queries as input Tables (Ms Access)

thanks again for the hints
Nicola