topic RE: Syntax for Dynamic SQL query with Excel in Product 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

RajathGopal — Wed, 06 May 2020 13:41:00 GMT

Hi Sapna,
You could try the below syntax:

1. "Select [Loan Number],[Borrower Fullname] from ["&[Data1]&"$]"
If the Loan Number is of Text:
2. "Select [Borrower Fullname] from [ACBS_SET_IDSA_CVS20200407$] where [Loan Number]='"&[Data1]&"'"
If the Loan Number is of number datatype
2. "Select [Borrower Fullname] from [ACBS_SET_IDSA_CVS20200407$] where [Loan Number]="&[Data1]
3. "Select distinct [Borrower Fullname] from [ACBS_SET_IDSA_CVS20200407$] where [Borrower Fullname] like '%"&[Data1]&"%'"

Thank You and have a nice day

------------------------------
Rajath Gopal
Associate IT Consultant
ITC Infotech
Asia/Kolkata
8904867411
------------------------------

RE: Syntax for Dynamic SQL query with Excel

sapna_soni0290 — Wed, 06 May 2020 15:10:00 GMT

Hi Rajath,

Thanks for the reply.

These queries are working fine when i am directly passing it to input parameter of get collection action of oledb object. But I need to store these queries into data item & then pass that data item to oledb page to make the component reusable.

Is there any way to do so with these queires?

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

RE: Syntax for Dynamic SQL query with Excel

RajathGopal — Wed, 06 May 2020 15:50:00 GMT

Yep it is possible, but you will have to work with a few data items as you are also getting dynamic values.
For instance for your first query:
1. "Select [Loan Number],[Borrower Fullname] from ["&[Data1]&"$]"

You will need 3 data items:
DataItem1: Select [Loan Number],[Borrower Fullname] from [
DataItem2: $]
This should be passed in you expression editor of get collection. So your expression for the query param would look something like below:
[DataItem1]&[Data1]&[DataItem2].
So you could repeat the same for the other queries as well.

Hope this helps!!

Thank You and Have a Nice Day,

------------------------------
Rajath Gopal
Associate IT Consultant
ITC Infotech
Asia/Kolkata
8904867411
------------------------------