topic RE: SQL query not working in OLEDB in Product Forum

SQL query not working in OLEDB

HongJooChoi — Thu, 24 Feb 2022 02:19:00 GMT

Hi, all.

We're trying to use OLEDB to read data from the excel.
The intention is to selectively query data items based on the datetime as below.

The current query we're using is: (but failed)

'select [Order ID], [Status], [Date Created], [Supplier Name] from [sheet1$] where (select CONVERT([Date Created], GETDATE(), 1)) >='&[Data2]
// Data2 is a BP's datetime variable.

Could you correct us with the right query syntax? or Is there anything else to consider?

Best regards

------------------------------
HongJoo Choi
------------------------------

RE: SQL query not working in OLEDB

PabloSarabia — Thu, 24 Feb 2022 06:24:00 GMT

Hi @HongJooChoi

I think the main problem is with the CONVERT function that yo are using in the WHERE.

Just to clarify, the CONVERT function is another way to cast a value into a specific data type. In this case, the correct syntax for this is: CONVERT(date type, field or expression, style). In your case: CONVERT(datetime, [Date Created], 1)

Try with this query:

select [Order ID]
, [Status]
, [Date Created]
, [Supplier Name]
from [sheet1$]
where CONVERT(datetime, [Date Created], 1) >="&[Data2]

You can also try with a CAST function, but you will problably have some problems with the date format.

Hope this helps you!

See you in the community, bye 🙂

------------------------------
Pablo Sarabia
Architect
Altamira Assets Management
Madrid
634726270
------------------------------

RE: SQL query not working in OLEDB

MichealCharron — Thu, 24 Feb 2022 14:07:00 GMT

@HongJooChoi,

There are a lot of Transact-SQL functions that the Jet or ACE engines don't support and Convert (and Cast) is not supported. You have to look more to the VBA functions when converting data through the SQL query.

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------

RE: SQL query not working in OLEDB

HongJooChoi — Fri, 25 Feb 2022 04:11:00 GMT

Dear Micheal

As you pointed out, it seems the Convert and Cast are not supported by the OLEDB objects downloaded from the DX.
Do you mean that I need to find another way to convert the data by using such as BP's embedded functions (as shown in calculation stage) or other business objects?

Regards

------------------------------
HongJoo Choi
------------------------------

RE: SQL query not working in OLEDB

MichealCharron — Fri, 25 Feb 2022 15:16:00 GMT

@HongJooChoi

The OLEDB VBO is the correct one to use. The functions used in the SQL query are dependent on the provider being used. In this case I assume you are using the Jet or ACE providers.

Given the example Excel worksheet shown above, you would have to convert your value passed from Blue Prism to a date value using a VBA function like the following:

"SELECT * FROM [Sheet1$] WHERE [Date Created] >= DATEVALUE('" & [Data2] & "')"

The DATEVALUE function only returns the date portion of the DateTime passed into it so you are telling the query to give you everything from midnight on, for the day specified in the Data2 data item. In the example below, Data2 contained a DateTime of February 20, 2022 2:13 AM.

While the query itself is not affected, somewhere in the conversion from DataTable to collection Blue Prism will convert DateTimes brought into collections to UTC so you will have to do some post manipulation if the DateTimes are relevant to your process.

------------------------------
Micheal Charron
Senior Manager
RBC
America/Toronto
------------------------------