topic RE: Error in Query using OLE DB in Product Forum

Error in Query using OLE DB

MohiniShelke — Mon, 16 Sep 2019 05:35:00 GMT

Hello All,
I am using OLE DB connection in BluePrism.
I am trying to execute following Query:

"SELECT * DISTINCT [User ID ] FROM [Sheet1$]"

But it is throwing following error message:

Internal : Could not execute code stage because exception thrown by code stage: Syntax error (missing operator) in query expression '* DISTINCT [User ID ]'.

Please help me to solve this error.

Thanks

------------------------------
Mohini Shelke
RPA Developer
vElement It
------------------------------

RE: Error in Query using OLE DB

SaumitraSharma — Mon, 16 Sep 2019 10:17:00 GMT

Mohini, SQL Query is wrong it should be SELECT DISTINCT [User ID ] FROM [Sheet1$]...U can use either Distinct or * both not together

------------------------------
Saumitra Sharma
Consultant
EY
Indian/Christmas
------------------------------

RE: Error in Query using OLE DB

MohiniShelke — Mon, 16 Sep 2019 10:39:00 GMT

Hello

You are right but if we user [User ID] , it will return only USER ID values in output

I want all row data.

------Original Message------

Mohini, SQL Query is wrong it should be SELECT DISTINCT [User ID ] FROM [Sheet1$]...U can use either Distinct or * both not together

------------------------------
Saumitra Sharma
Consultant
EY
Indian/Christmas
------------------------------

RE: Error in Query using OLE DB

SaumitraSharma — Mon, 16 Sep 2019 10:47:00 GMT

U can use this but this will delete the duplicate data permanently only unique data will be there...delete dups from (select *, row_number() OVER ( partition by [User ID] order by [User ID]) as rn from [Sheet1$]) dups WHERE rn > 1
Select * from [Sheet1$]
------------------------------

RE: Error in Query using OLE DB

MohiniShelke — Mon, 16 Sep 2019 12:31:00 GMT

Hi,

I tried this query ,

But getting following error:

Internal : Could not execute code stage because exception thrown by code stage: Syntax error in query expression 'rn > 1Select * from [Sheet1$]'.

Thanks & Regards,

Mohini Shelke

RPA Developer, vElement IT,

Cell : (+91)9503221751

https://velement.io/

------Original Message------

U can use this but this will delete the duplicate data permanently only unique data will be there...delete dups from (select *, row_number() OVER ( partition by [User ID] order by [User ID]) as rn from [Sheet1$]) dups WHERE rn > 1
Select * from [Sheet1$]
------------------------------

RE: Error in Query using OLE DB

SaumitraSharma — Mon, 16 Sep 2019 14:03:00 GMT

Hi,
There should be space between 1 and Select you have missed the space it should be rn > 1 Select * from [Sheet1$]

------------------------------
Saumitra Sharma
Consultant
EY
Indian/Christmas
------------------------------

RE: Error in Query using OLE DB

MohiniShelke — Mon, 16 Sep 2019 17:42:00 GMT

Hi ,
I tried with space also,still getting same error.

------------------------------
Mohini Shelke
RPA Developer
vElement It
------------------------------

RE: Error in Query using OLE DB

MohiniShelke — Tue, 17 Sep 2019 05:19:00 GMT

Hi,

I tried this also but still getting same error.

------Original Message------

Hi,
There should be space between 1 and Select you have missed the space it should be rn > 1 Select * from [Sheet1$]

------------------------------
Saumitra Sharma
Consultant
EY
Indian/Christmas
------------------------------

RE: Error in Query using OLE DB

shashank.kumar280 — Wed, 18 Sep 2019 04:57:00 GMT

Hi Mohini,

Can you post your vbo here. Let me check.

------------------------------
Shashank Kumar
DX Integrations Partner Consultant
Blue Prism
Singapore
+6581326707
------------------------------

RE: Error in Query using OLE DB

MohiniShelke — Wed, 18 Sep 2019 09:51:00 GMT

Hello

I have attache My Process and also the input file.

Please check and let me know what is the error?

Thanks & Regards,

Mohini Shelke

RPA Developer, vElement IT,

Cell : (+91)9503221751

https://velement.io/

------Original Message------

Hi Mohini,

Can you post your vbo here. Let me check.

------------------------------
Shashank Kumar
DX Integrations Partner Consultant
Blue Prism
Singapore
+6581326707
------------------------------

RE: Error in Query using OLE DB

SaumitraSharma — Thu, 19 Sep 2019 11:59:00 GMT

Hi mohini,

That SQL query is not working for OLEDB....i have updated the distinct query now it's storing the unique value only however i have not incuded all the columns in the query because all the column does not contain the duplicate value for example if you see user id 121477 some of the column like( Training Topics, Training Title, Training vendor and so on) these column does not have duplicate value hence i have excluded those column name from the select query and kept only those column name who have the duplicate value also as a result you will not get those excluded column value in the collection......I have attached the XML please find

------------------------------
Saumitra Sharma
Consultant
EY
Indian/Christmas
------------------------------

RE: Error in Query using OLE DB

NagaM — Thu, 19 Sep 2019 12:47:00 GMT

Hi Mohini,

Please try the below query

"SELECT MAX([UserID]) as [UserID] ,[User First Name],[Employee Preferred Name],[User E-mail],[Location]FROM [Sheet1$] GROUP BY[User First Name],[Employee Preferred Name],[User E-mail],[Location]"

You have to define your required columns individually in both select clause and and group by clause.

Regards,
Naga.

------------------------------
Naga Ratna Mareddy
Associate
cognizant
Asia/Kolkata
------------------------------