How can I do a rising-column DBConnect query with a table join?

[gf13579]

Using DBConnect 3.1.0 I can't setup an input with a query that uses AND rather than WHERE because it's doing a JOIN. I'm posting here to share a workaround

My query doesn't contain a WHERE clause as it's a JOIN, which uses AND for the [risingcolumn] > ?. I can execute the query when setting up the input, but if I hit Next DBConnect tells me "One or more fields are invalid, please fix them before go next" and marks step 4 ("Update your SQL to accept the checkpoint value and make sure it works correctly.") as incomplete.

This is fine:

select e.* from [events] e where e.eventpk > ? order by e.eventpk asc

This isn't:

select e.*, p.* from [events] e
inner join [params] p on p.eventFk = e.eventPk
and e.eventpk > ?
order by e.eventpk asc

[tomapatan]

thanks for the post, helped me solve a very similar issue that I`ve encountered.

[gf13579 · Accepted solution]

Here's my workaround - to appease DBConnect's validation check for a WHERE clause:

select * from 
(select e.*, p.* from [events] e
inner join [params] p on p.eventFk = e.eventPk
and e.eventpk > ?) a
where 2 > 1
order by a.eventpk asc

Can anyone advise me how to feed this back to the DBConnect guys? The query.search(/\bwhere\b/i)>=0 bit in data_lab.js just needs to be a bit more flexible