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

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

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
Jronash
Impactful Individual
Impactful Individual

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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (1,895)