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
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
thanks for the post, helped me solve a very similar issue that I`ve encountered.
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