cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ericonline
Community Champion
Community Champion

Does the DB2 connector support gt, ge, lt, le?

It appears the DB2 connector does not support gt, ge, lt, le filter operators on string, date NOR number type columns. 

Can someone confirm this? 

The docs don't mention this limitation, but I'm unable to query a DB2 table using these operators. 

 

Example 1: https://github.com/MicrosoftDocs/BusinessApplicationPlatform-Connectors-public/issues/258

Example 2: https://powerusers.microsoft.com/t5/Building-Flows/DB2-GetRows-Filter-By-Date/td-p/646391 

1 ACCEPTED SOLUTION

Accepted Solutions
ericonline
Community Champion
Community Champion

Hm. Alright, well, its working for me now using the Unix timestamp column. I'm not sure what changed, but here it is tested and working:

Tried all variants (lt, le, gt, ge). No quotes around EITHER the columnName nor the value.

LOAD_TS ge 1610030693

View solution in original post

6 REPLIES 6
Jronash
Solution Supplier
Solution Supplier

A lot of the SQL connectors seem to have this issue.  I don't use DB2, but I've used SQL Server and MySQL and have experienced the same thing on both. 

 

You SHOULD be able to filter using this:

columnName ge 'dateTimeStamp'

 

If you search around on Google (which you'll probably have), you'll found people who say they've gotten it to work using a variety of techniques - putting a line break at the end of the line, formatting the date differently, etc.  I've tried a lot of these solutions and have not had luck with any of them.

 

SQL Server allows you to use stored procedures, so I've gotten around this limitation in that way.  With MySQL, I was able to get what I needed with some creative use of views.  Afraid I can't suggest a route with DB2 - though you have my sympathy, for what it's worth!  😀

ericonline
Community Champion
Community Champion

@Jronash , thank you for the thoughtful reply. I definitely tried your suggested code before posting.

 

So far I've tried:

 

columnName ge 'stringValue'

columnName ge 'dateValue'

columnName ge numericValue //Unix timestamp

columnName ge 'numericValue' //Unix timestamp

 

 

Still no go!

Jronash
Solution Supplier
Solution Supplier

To be clear, it doesn't work for me, either.  I've never gotten this functionality to work as intended.

 

But your examples don't look right to me.  You're supposed to have the name of your column on the left side of the expression and a dateTime value on the right.  So if your column was named 'dateModified' and you had a timestamp saved in a variable called 'date', it would look like this:

dateModified.png

ericonline
Community Champion
Community Champion

Thanks for pointing out the typo in my post. You are correct. In the example, I (previously, edited now) showed 'columnName gt dateColumn', etc. Now shows 'columnName gt dateValue'.

 

Still the question remains...

- Does the DB2 connector work with gt, ge, lt, le?

  - If so what is the correct column data type and syntax?

  - If not, this needs to be listed as a (major) limitation in the DB2 connector docs.

ericonline
Community Champion
Community Champion

Hm. Alright, well, its working for me now using the Unix timestamp column. I'm not sure what changed, but here it is tested and working:

Tried all variants (lt, le, gt, ge). No quotes around EITHER the columnName nor the value.

LOAD_TS ge 1610030693

View solution in original post

Yep, using a Unix timestamp is a good workaround because you're comparing integers rather than date objects.  This should work for you until 2038!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Users online (2,207)