topic RE: Syntax for Dynamic SQL query with Excel in University Forum

Syntax for Dynamic SQL query with Excel

sapna_soni0290 — Wed, 06 May 2020 12:47:00 GMT

Hi All,

I am looking to syntax for basic SQL queries for making it dynamic. I have my data in excel on which i am using oledb. please help me know how to convert the hard coded values into generic syntax on below queries -

1.Select [Loan Number],[Borrower Fullname] from [ACBS_SET_IDSA_CVS20200407$]

In this query, ACBS_SET_IDSA_CVS20200407 is my sheet name. How to pass a data type if this sheet name is store in Data1

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.

3.Select distinct [Borrower Fullname] from [ACBS_SET_IDSA_CVS20200407$] where [Borrower Fullname] like '%WEST%'

I want to pass WEST into a data type. What will be the syntax.

------------------------------
sapna soni
.
Capgemini Pvt Ltd
Asia/Kolkata
------------------------------

RE: Syntax for Dynamic SQL query with Excel

Carl__Carter — Thu, 07 May 2020 06:47:00 GMT

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

RE: Syntax for Dynamic SQL query with Excel

sapna_soni0290 — Thu, 07 May 2020 07:06:00 GMT

HI Carl,

Thanks for the reply.

These concatenated query works if i am passing the whole query as text (within "") as input parameter directly to the get collection action of oledb. But i want to store these queries into different data items, so i can create a single reusable page for all these queries & just pass the query as input parameter of my page.

Is there a way to achieve so.

------------------------------
sapna soni
.
Capgemini Pvt Ltd
Asia/Kolkata
------------------------------

RE: Syntax for Dynamic SQL query with Excel

Carl__Carter — Thu, 07 May 2020 07:18:00 GMT

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

RE: Syntax for Dynamic SQL query with Excel

sapna_soni0290 — Thu, 07 May 2020 09:57:00 GMT

Hi Carl,

Yes this helps. This helps me to achieve the required task. Thanks a lot for help.

------------------------------
sapna soni
.
Capgemini Pvt Ltd
Asia/Kolkata
------------------------------