topic Re: Query based on variable input in Talend Studio

Query based on variable input

Anonymous — Wed, 07 Feb 2018 16:45:47 GMT

Hi,

I need to run a query multiple times (loop) against a database.

The query has one variable called HOLDER and in the example below it is set to 0000.

The variables are stored on a different csvinputfile, (one column, and one per line around 40,000).

So the query will need to run around 40,000 times base don the line count.

I need the output data in One CSV file. It must add another column called QueriedVariable and within this column there needs to be the variable from HOLDER which was used in the query for the results (every row of the result must contain the HOLDER variable which was used to generate the results).

I would be greatful for any help provided.

SELECT ISET.ENTRY.TRANS_ID, ISET.ENTRY.ACCOUNT_ID, ISET.ENTRY.SECURITY_ID,
  ((ISET.ENTRY.AMOUNT * -1)/100) AS Amount, ISET.ENTRY.VAL_DATE, ISET.ENTRY.NARRATIVE,
  ISET.ENTRY.RECONCILED, ISET.ENTRY.TICK_COLOUR, ISET.ENTRY.\"REF\",ISET.TRANS.TRANS_ID,
  ISET.TRANS.PHYSICAL_DATE, ISET.TRANS.STATEMENT_DATE, ISET.TRANS.PROCESS, ISET.TRANS.LOGGED_USER,
  ISET.TRANS.\"TYPE\", ISET.TRANS.HOLDER, ISET.TRANS.TRANS_NARRATIVE, ISET.ACCOUNT.HOLDER,
  (ABS(ISET.ENTRY.AMOUNT)/100) AS Amount2
FROM ISET.ENTRY
INNER JOIN ISET.TRANS ON  ISET.ENTRY.TRANS_ID= ISET.TRANS.TRANS_ID
INNER JOIN ISET.ACCOUNT ON ISET.ENTRY.ACCOUNT_ID = ISET.ACCOUNT.ACCOUNT_ID
WHERE ISET.ENTRY.ACCOUNT_ID IN (SELECT ACCOUNT_ID FROM ACCOUNT START WITH HOLDER = '0000' CONNECT BY NOCYCLE PRIOR ACCOUNT_ID = PARENT);

Re: Query based on variable input

TRF — Wed, 07 Feb 2018 17:54:06 GMT

Hi,

Use the following pattern:

tFileInputDelimited-->tFlowToIterate-->tMySQLInput-->tFileOutputDelimited

- tFileInputDelimited to read your input file

- tFlowToIterate to generate a set of global variables (1 per field for the current record - see documentation for details)

- tMySQLInput (replace by your DB connector) to query your database + arrange the schema as expected for your select

in the query field, replace the HOLDER value in the query by the corresponding global variable and add the new pseudo-column here called MyHolder

assuming the global is called "HOLDER" you can do it like this:

"select '" + (String)globalMap.get("HOLDER") + "' AS MyHolder, ISET.ENTRY.TRANS_ID, ISET.ENTRY.ACCOUNT_ID, blablabla 
 from blablabla 
 where blablabla 
 START WITH HOLDER = '" + (String)globalMap.get("HOLDER") + "' CONNECT BY..."

-tFilleOutputDelimited to get the result (tick the Appen option)

Hope this is enough to let go with the solution.

Re: Query based on variable input

Anonymous — Thu, 08 Feb 2018 11:08:37 GMT

Hi,

Thanks for the reply.

This doesnt seem to work, i am trying with a simpler query:

Original Query brings results :

SELECT TRANS_ID, ACCOUNT_ID, BANK_ACCOUNT_ID, SECURITY_ID, AMOUNT, VAL_DATE, NARRATIVE
FROM ISET.CASH_ENTRY
WHERE ACCOUNT_ID IN (SELECT ACCOUNT_ID
FROM ISET.ACCOUNT
WHERE HOLDER = '12345')

Query based on your suggestion:

select  TRANS_ID, ACCOUNT_ID, BANK_ACCOUNT_ID, SECURITY_ID, AMOUNT, VAL_DATE, NARRATIVE
FROM ISET.CASH_ENTRY
WHERE ACCOUNT_ID IN (SELECT ACCOUNT_ID
FROM ISET.ACCOUNT
WHERE HOLDER ='" + (String)globalMap.get("HOLDER") + "' )

Also there seems to be a difference in how the query is presented in the component screen :

"select  TRANS_ID, ACCOUNT_ID, BANK_ACCOUNT_ID, SECURITY_ID, AMOUNT, VAL_DATE, NARRATIVE
FROM ISET.CASH_ENTRY
WHERE ACCOUNT_ID IN (SELECT ACCOUNT_ID
FROM ISET.ACCOUNT
WHERE HOLDER ='\" + (String)globalMap.get(\"HOLDER\") + \"' )"

I am only using a simple input file with three rows (which when manually tested bring up results),

CLIENT
1234
4321
1551

In the tFlowToIterate i have unticked the "Use the Default (Key.......)" and customised so the key is "HOLDER" and value is set to "CLIENT" which is the only field/column in the input file.

Any further help would be greatly appreciated.

Re: Query based on variable input

TRF — Thu, 08 Feb 2018 11:19:03 GMT

do you have an error or just no result?

if you have an error try with the following for the query:

"select  TRANS_ID, ACCOUNT_ID, BANK_ACCOUNT_ID, SECURITY_ID, AMOUNT, VAL_DATE, NARRATIVE
FROM ISET.CASH_ENTRY
WHERE ACCOUNT_ID IN (SELECT ACCOUNT_ID
FROM ISET.ACCOUNT
WHERE HOLDER ='" + (String)globalMap.get("HOLDER") + "')"

if you have no result, share your tFlowToIterate settings with the corresponding schema

Re: Query based on variable input

Anonymous — Thu, 08 Feb 2018 11:46:40 GMT

Thanks.

That seems to work with the small query, but if I try and edit the larger one it doesn't work:

SELECT ISET.ENTRY.TRANS_ID, ISET.ENTRY.ACCOUNT_ID, ISET.ENTRY.SECURITY_ID,
((ISET.ENTRY.AMOUNT * -1)/100) AS Amount, ISET.ENTRY.VAL_DATE, ISET.ENTRY.NARRATIVE,
ISET.ENTRY.RECONCILED, ISET.ENTRY.TICK_COLOUR, ISET.ENTRY."REF",ISET.TRANS.TRANS_ID,
ISET.TRANS.PHYSICAL_DATE, ISET.TRANS.STATEMENT_DATE, ISET.TRANS.PROCESS, ISET.TRANS.LOGGED_USER,
ISET.TRANS."TYPE", ISET.TRANS.HOLDER, ISET.TRANS.TRANS_NARRATIVE, ISET.ACCOUNT.HOLDER,
(ABS(ISET.ENTRY.AMOUNT)/100) AS Amount2
FROM ISET.ENTRY
INNER JOIN ISET.TRANS ON  ISET.ENTRY.TRANS_ID= ISET.TRANS.TRANS_ID
INNER JOIN ISET.ACCOUNT ON ISET.ENTRY.ACCOUNT_ID = ISET.ACCOUNT.ACCOUNT_ID
WHERE ISET.ENTRY.ACCOUNT_ID IN (SELECT ACCOUNT_ID FROM ACCOUNT START WITH HOLDER = '" + (String)globalMap.get("HOLDER") + "' CONNECT BY NOCYCLE PRIOR ACCOUNT_ID = PARENT)

Also if I enclose this in double quotes then i get an error - ORA-00972 identifier is too long.

Do you have skype by any chance?

Re: Query based on variable input

TRF — Thu, 08 Feb 2018 11:58:04 GMT

The rule you MUST respect is that the query is a string so you MUST enclose it between "".

So, add " at the beginning and at the end.

Then remove "" around REF and TYPE fields.

If it doesn't work try \"REF\" and \"TYPE\".

If it doesn't work anymore try the query under SQL*Plus (replace the global by its value for this test).

Re: Query based on variable input

Anonymous — Thu, 08 Feb 2018 13:32:03 GMT

Thank you very much for this, it has worked.

Even though on the test run query within the query window it spat out an error it works when actually running.