Hi Sapna,
It's a case of simple concatenation, replacing your hard coded values with the name of the data item containing the value. You would use the Concatenate (&) to string the SQL query and the data items together.
Take a look at the 'Blue Prism Guide to OLEDB' which is available on the Blue Prism University. Just perform a search to locate the training course. On page 4, there is an example of using data items in an SQL statement.
Here is an example using one of your scenarios.
"2. Select [Borrower Fullname] from [ACBS_SET_IDSA_CVS20200407$] where [Loan Number]='123456'
In this query, I want to replace the loan no. 123456 with some data type– Data1, which will store dynamic values. How do we do that."
This is assuming that the Loan Number field is a text value:
Hope this helps!
------------------------------
Carl Carter
Developer Program Manager
Blue Prism
Europe/London
------------------------------
It's a case of simple concatenation, replacing your hard coded values with the name of the data item containing the value. You would use the Concatenate (&) to string the SQL query and the data items together.
Take a look at the 'Blue Prism Guide to OLEDB' which is available on the Blue Prism University. Just perform a search to locate the training course. On page 4, there is an example of using data items in an SQL statement.
Here is an example using one of your scenarios.
"2. Select [Borrower Fullname] from [ACBS_SET_IDSA_CVS20200407$] where [Loan Number]='123456'
In this query, I want to replace the loan no. 123456 with some data type– Data1, which will store dynamic values. How do we do that."
This is assuming that the Loan Number field is a text value:
Hope this helps!
------------------------------
Carl Carter
Developer Program Manager
Blue Prism
Europe/London
------------------------------
Hi Sapna,
That is exactly what the Calculation Stage image I included is doing. I've expanded the screenshot so you can see this:
My SQL query is stored in the [SQL Query] Data Item, and this Data Item is used as the SQL input for the 'Data - OLEDB - Execute' Action.
If you want to store your SQL query as a template in a Data Item and then update another Data item with the query to execute using another value in the process, you would use the Replace function which would replace some placeholder text. For example
[SQL Query Template] Data Item value is "SELECT [Borrower Fullname] FROM [ACBS_SET_IDSA_CVS20200407$] WHERE [Loan Number]='{{Placeholder:LoanNumber}}'"
[Loan Number] Data Item value is 123456
You calculation stage to set the [SQL Query] Data Item would look like:
[SQL Query] is then used as your OLEDB query input.
------------------------------
Carl Carter
Developer Program Manager
Blue Prism
Europe/London
------------------------------
That is exactly what the Calculation Stage image I included is doing. I've expanded the screenshot so you can see this:
My SQL query is stored in the [SQL Query] Data Item, and this Data Item is used as the SQL input for the 'Data - OLEDB - Execute' Action.
If you want to store your SQL query as a template in a Data Item and then update another Data item with the query to execute using another value in the process, you would use the Replace function which would replace some placeholder text. For example
[SQL Query Template] Data Item value is "SELECT [Borrower Fullname] FROM [ACBS_SET_IDSA_CVS20200407$] WHERE [Loan Number]='{{Placeholder:LoanNumber}}'"
[Loan Number] Data Item value is 123456
You calculation stage to set the [SQL Query] Data Item would look like:
[SQL Query] is then used as your OLEDB query input.
------------------------------
Carl Carter
Developer Program Manager
Blue Prism
Europe/London
------------------------------
