Weird Behavior on a Select and Fetch with WHERE clause

[Alan Grinberg]

Possible type mismatch?

I believe that #1 is a floating point number.
Maybe use a long integer when creating the where clause.

- Alan

----------------------------------------------------
Alan Grinberg
AG Systems/ZOO-INK
San Francisco, CA 

www.zoo-ink.com
www.perfectfit.net
alan at perfectfit.net
----------------------------------------------------




> On Oct 17, 2021, at 2:09 PM, Das Goravani <goravanis at gmail.com> wrote:
> 
> 
> 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
> 
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com