Weird Behavior on a Select and Fetch with WHERE clause

Das Goravani goravanis at
Sun Oct 17 21:09:04 UTC 2021

I am doing a $select with a WHERE clause. 

The WHERE Clause looks like this:

WHERE chcateg=0 AND nrsn>=@[#1] AND nrsn<=@[#2]

NRSN is the primary key, it’s lower case, I can’t type lower case in this letter, it corrects it to nurse

CHCATEG is a short integer column, it is indexed, and it is not working

I’m getting back records with CHCATEG = all values present, anything

It’s not doing the SELECT on CHCATEG

This is SQLite and the code looks like this

Calculate #S5 as con(pwhereclause,' AND nrsn>=@[#1] AND nrsn<=@[#2]')
Do lLoadList.$select(#S5) Returns #F
Do lLoadList.$fetch(500) Returns lFetchStatus

I am concatenating the WHERE clause passed in with the #1 and #2 aspects… the whole of #S5 in my test case looks like this:

WHERE chcateg=0 AND nrsn>=@[#1] AND nrsn<=@[#2]

Why would this behavior happen?

Oh I had #S5 is square brackets, I’m about to test it without brackets hence why it is without them herein above in the $select line of code

I was under the impression that

If your search, your select, returned less than 500 records, that even is you say $fetch(500) you will only get what matched the search, ie, perhaps 300 or whatever.. 

I am getting 500 lines when I should be getting none or way less than 500. 

It is as if my WHERE clause is being ignored

I’m had good luck with this type of thing so far, many cases, always.. I’m baffled as to what is messing up this simple scenario

More information about the omnisdev-en mailing list