Home > Forum > Data sources > Initialize Item List with SQL Query from Oracle DB

Initialize Item List with SQL Query from Oracle DB
0

Hello all,


Does anyone configured an Item List to be initialized with a SQL query that will query from another database? In this case I want to query from an Oracle DB and initialize the columns with the columns from the query:

select milestone as {DCNCOL:185},
milestone_date as {DCNCOL:186},
amount as {DCNCOL:187},
currency_code as {DCNCOL:188},
amount_euro as {DCNCOL:189}
from PL_Invoicing


I'm trying to this but it's not working.

Hope anyone has ideas for this!

Thank you!!!!

MVP

Hello Andreia,

from what I can tell this should work.
I also did a small test:
1. Data Source definition, using a new connection to the Config DB
2. Setup of the item list initialization
3. Testing the expression editor result
4. Starting the process

Which step doesn't work in your case?

Off topic:
I really like the new diagnostic mode, where I can see that my query has been executed :)

Kind regards,
Daniel

In reply to: Daniel Krüger (Cosmo Consult)

Hello Andreia,

from what I can tell this should work.
I also did a small test:
1. Data Source definition, using a new connection to the Config DB
2. Setup of the item list initialization
3. Testing the expression editor result
4. Starting the process

Which step doesn't work in your case?

Off topic:
I really like the new diagnostic mode, where I can see that my query has been executed :)

Kind regards,
Daniel

Hi Daniel,

It worked, I was using the alias from the table to initialize the item list but when I switched to the names of the columns it worked.

But I have question, did you ever initialize the item list using a where condition set on a field from the form?
In my case there's this project id which also exists in my table and I want to initialize it with the value that is in the form but it's not working.

SELECT DESCRIPTION as {DCNCOL:185},
due_date as {DCNCOL:186},
ROUND(bill_trans_bill_amount,4) as {DCNCOL:187},
bill_trans_currency_code as {DCNCOL:188},
ROUND(bill_trans_bill_amount_eur,4) as {DCNCOL:189}
FROM bi_obiee_dw.fact_pl_invoicing
WHERE invoice_number IS NOT NULL
and '{WFCON:2289}'=project_id

When I try to test it it returns an empty window.
I changed the field to be integer (previous was text) but still not working.

Hope you got any ideas on this!

Andreia

MVP

Hi Andreia,


I'm adding to Sebastians answer. The preview should contain the value from the field for example 'P-1000' and not just '' without a value.

If you want to initialize the item list when the value of a field changes you could add a form rule to "On value change" (1) in the "Style and behavior" tab. You can use the "Item list -> Initialization" function.

I haven't test it but you should probably see the executed SQL query if you enable diagnostics like in this example:
https://daniels-notes.de/posts/2021/series-expert-guide-part-3#with-webcon-bps-2021

My personal preference though would be to create an additional step. For example "Initialization - Part 1" and "Initialization - Part 2" and check the Wizard mode for the path. During path transition I would initialize the item list with an action.

Best regards,
Daniel