cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
Responsive Resident
Responsive Resident

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

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

Responsive Resident
Responsive Resident

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

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.

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

Responsive Resident
Responsive Resident

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

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Microsoft Ignite

Microsoft Power Platform: 2021 Release Wave 1 Plan

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

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (12,979)