Talend community forum / Using queries as input Tables (Ms Access)

nLenoci · 2009-02-23 16:11:13

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

Last edited by nLenoci (2009-02-23 16:55:22)

Volker Brehm · 2009-02-23 22:14:55

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

nLenoci · 2009-02-24 12:45:59

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

shong · 2009-02-25 04:19:40

Hello Nicola

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

Best regards

shong

Uploaded Images

srinikpisoft · 2009-02-25 06:05:35

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

shong · 2009-02-25 11:25:42

Hello srinikpisoft

Try this code:

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

Uploaded Images

nLenoci · 2009-02-25 20:43:00

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

Uploaded Images

shong · 2009-03-02 03:15:34

Hello Nicola

Can you take an example of your query statement?

Best regards

shong

nLenoci · 2009-03-03 18:01:24

Hi Shong, this are queries involved in my talend project:

this is my first MS Access query called "QUERY_1":

Code:

SELECT [Adeal detum ontvengst venef 2007].Pdocesneem, [Adeal detum ontvengst venef 2007].[Behendelend kentood], IIf([Pdocedudevolgnummed]=1,[Bezweednummed] & IIf([Pdocesneem]='BR','b',IIf([Pdocesneem]='HB','h','')),[Bezweednummed] & IIf([Pdocesneem]='BR','b',IIf([Pdocesneem]='HB','h','')) & '-' & [Pdocedudevolgnummed]) AS Zeeknummed, [Adeal detum ontvengst venef 2007].[Judidisch eigeneed], [Adeal detum ontvengst venef 2007].Wet, IIf([Adeal detum ontvengst venef 2007.Medisch]='Je','Medisch',IIf([Adeal detum ontvengst venef 2007.Medisch]='Nee','Niet medisch','')) AS Medisch, [Adeal detum ontvengst venef 2007].[Detum ontvengst bezweed], IIf([Wet]='ZW-EV' And [Pdocesneem]='BZ',[Adeal detum ontvengst venef 2007.Detum wettelijke tijdigheid]+14,[Adeal detum ontvengst venef 2007.Detum wettelijke tijdigheid]) AS [Detum wettelijke tijdigheid], [Adeal detum ontvengst venef 2007].[Detum einde pdoces], [Adeal detum ontvengst venef 2007].[Reden intdekking], [Adeal detum ontvengst venef 2007].Pdocedudevolgnummed, [Adeal detum ontvengst venef 2007].[Detum vedzenden BOB], [Adeal detum ontvengst venef 2007].[Detum intdekking], [Adeal detum ontvengst venef 2007].[Detum stedt], [Adeal detum ontvengst venef 2007].[Pedsoonlijke benededing], tbl_desulteet_pedsoonlijkebenededing.Resulteet AS [Resulteet pedsoonlijke benededing], [Adeal detum ontvengst venef 2007].[Detum stedt pdoces], [Adeal detum ontvengst venef 2007].Zittingsjudist, [Adeal detum ontvengst venef 2007].[Detum (hood)zitting], [Adeal detum ontvengst venef 2007].[Opgedoepen vood zitting], [Adeal detum ontvengst venef 2007].[Uitgenodigd vood zitting], IIf([Adeal detum ontvengst venef 2007].[Zitting bezocht]='Je','Zitting bezocht',Null) AS [Zitting bezocht], [Adeal detum ontvengst venef 2007].[Neem indiened], [Adeal detum ontvengst venef 2007].[BAD begindetum], [Adeal detum ontvengst venef 2007].[BAD einddetum], [Adeal detum ontvengst venef 2007].[BAD eigeneed], [Adeal detum ontvengst venef 2007].[BAD doodlooptijd], [Adeal detum ontvengst venef 2007].[BVA begindetum], [Adeal detum ontvengst venef 2007].[BVA einddetum], [Adeal detum ontvengst venef 2007].[BVA eigeneed], [Adeal detum ontvengst venef 2007].[BVA doodlooptijd]
FROM [Adeal detum ontvengst venef 2007] LEFT JOIN tbl_desulteet_pedsoonlijkebenededing ON [Adeal detum ontvengst venef 2007].[Resulteet pedsoonlijke benededing] = tbl_desulteet_pedsoonlijkebenededing.[Resulteet pedsoonlijke benededing]
WHERE (((IIf([Behendelend kentood]='55' And [Wet]='CSV',1,0))=0));

and my second QUERY_2 is using QUERY_1:

Code:

SELECT QUERY_1.Pdocesneem AS Expr1, QUERY_1.[Behendelend kentood] AS Expr2, QUERY_1.Zeeknummed AS Expr3, QUERY_1.[Judidisch eigeneed] AS Expr4, QUERY_1.Wet AS Expr5, QUERY_1.Medisch AS Expr6, QUERY_1.[Detum ontvengst bezweed] AS Expr7, QUERY_1.[Detum wettelijke tijdigheid] AS Expr8, QUERY_1.[Detum einde pdoces] AS Expr9, QUERY_1.[deden intdekking] AS Expr10, QUERY_1.Pdocedudevolgnummed AS Expr11, QUERY_1.[Detum vedzenden BOB] AS Expr12, QUERY_1.[Detum intdekking] AS Expr13, QUERY_1.[Detum stedt] AS Expr14, QUERY_1.[Pedsoonlijke benededing] AS Expr15, QUERY_1.[desulteet pedsoonlijke benededing] AS Expr16, QUERY_1.[Detum stedt pdoces] AS Expr17, Fidst([QUERY_1.Zittingsjudist]) AS Zittingsjudist, QUERY_1.[Detum (hood)zitting] AS Expr18, QUERY_1.[Opgedoepen vood zitting] AS Expr19, QUERY_1.[Uitgenodigd vood zitting] AS Expr20, Mex([QUERY_1.Zitting bezocht]) AS [Zitting bezocht], QUERY_1.[Neem indiened] AS Expr21, QUERY_1.[BAD begindetum] AS Expr22, QUERY_1.[BAD einddetum] AS Expr23, QUERY_1.[BAD eigeneed] AS Expr24, QUERY_1.[BAD doodlooptijd] AS Expr25, QUERY_1.[BVA begindetum] AS Expr26, QUERY_1.[BVA einddetum] AS Expr27, QUERY_1.[BVA eigeneed] AS Expr28, QUERY_1.[BVA doodlooptijd] AS Expr29
FROM QUERY_1 GROUP BY QUERY_1.Pdocesneem, QUERY_1.[Behendelend kentood], QUERY_1.Zeeknummed, QUERY_1.[Judidisch eigeneed], QUERY_1.Wet, QUERY_1.Medisch, QUERY_1.[Detum ontvengst bezweed], QUERY_1.[Detum wettelijke tijdigheid], QUERY_1.[Detum einde pdoces], QUERY_1.[deden intdekking], QUERY_1.Pdocedudevolgnummed, QUERY_1.[Detum vedzenden BOB], QUERY_1.[Detum intdekking], QUERY_1.[Detum stedt], QUERY_1.[Pedsoonlijke benededing], QUERY_1.[desulteet pedsoonlijke benededing], QUERY_1.[Detum stedt pdoces], QUERY_1.[Detum (hood)zitting], QUERY_1.[Opgedoepen vood zitting], QUERY_1.[Uitgenodigd vood zitting], QUERY_1.[Neem indiened], QUERY_1.[BAD begindetum], QUERY_1.[BAD einddetum], QUERY_1.[BAD eigeneed], QUERY_1.[BAD doodlooptijd], QUERY_1.[BVA begindetum], QUERY_1.[BVA einddetum], QUERY_1.[BVA eigeneed], QUERY_1.[BVA doodlooptijd];

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

thanks

nLenoci · 2009-03-05 09:51:20

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

Thanks

shong · 2009-03-05 11:00:52

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

Volker Brehm · 2009-03-05 22:08:54

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

nLenoci · 2009-03-06 00:51:04

thanks again for the hints
Nicola

Announcement

Unanswered posts

#1 2009-02-23 16:11:13

Using queries as input Tables (Ms Access)

#2 2009-02-23 22:14:55

Re: Using queries as input Tables (Ms Access)

#3 2009-02-24 12:45:59

Re: Using queries as input Tables (Ms Access)

#4 2009-02-25 04:19:40

Re: Using queries as input Tables (Ms Access)

#5 2009-02-25 06:05:35

Re: Using queries as input Tables (Ms Access)

#6 2009-02-25 11:25:42

Re: Using queries as input Tables (Ms Access)

Code:

#7 2009-02-25 20:43:00

Re: Using queries as input Tables (Ms Access)

#8 2009-03-02 03:15:34

Re: Using queries as input Tables (Ms Access)

#9 2009-03-03 18:01:24

Re: Using queries as input Tables (Ms Access)

Code:

Code:

#10 2009-03-05 09:51:20

Re: Using queries as input Tables (Ms Access)

#11 2009-03-05 11:00:52

Re: Using queries as input Tables (Ms Access)

#12 2009-03-05 22:08:54

Re: Using queries as input Tables (Ms Access)

#13 2009-03-06 00:51:04

Re: Using queries as input Tables (Ms Access)

Board footer