Query based on variable input

[Anonymous]

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);

[TRF]

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.

[Anonymous]

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.

[TRF]

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

[Anonymous]

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?

[TRF · Accepted solution]

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).

[Anonymous]

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.