Syntax for Dynamic SQL query with Excel

[sapna_soni0290]

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

[RajathGopal · Accepted solution]

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

[sapna_soni0290]

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

[RajathGopal]

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