Subtlety of a SQL Select vs "Find"

[Miguel Garcia]

Hi Das,

You have now all the information to buid a correct FIND NO EXACT MATCH.

Just keep in mind that SQL SELECT returns the result rows without order 
if you don't use the order clause. In your case >= selects all the rows 
greater than 101043434343 in the table and $fetch returns the first, 
without ORDER BY clause the order of the rows selected is aleatory.

Also note that if the columns do not generate a unique key in the ORDER 
BY, the order between the rows that have the same value in the sort 
columns is also aleatory. For this reason Gary recomend :

SELECT * FROM tblname WHERE fieldname >= 1010458390 ORDER BY fieldname, seq LIMIT

Best Regards


El 19/5/20 a las 0:20, Das Goravani escribió:
> Hello List,
>
> Greetings on my 60th birthday. And I use desktop I admit..
>
> So here’s the thing.. I have in my app one instance of the Omnis Find command which has been replaced like all others with a call to a central method that is now SQL.
>
> This Find isn’t working, in this one place.. and I’m researching it, and it’s brought up an odd index/find/select reality..
>
> Consider numbers like this are in a column
>
> 7010000490
> 1010458390
>
> In Omnis, when I Find without exact match on 101043434343 say.. I get the 1010… number as found
>
> In SQL to mimic the not exact match I do >= as the operator in the where clause
>
> This is working for most of my app.. to mimic what Omnis did..
>
> But in this one case it’s different
>
> SQL is finding the first record, the 701 number.. because it’s greater than or equal to my query value
>
> (My find on value) not sure what to call that..
>
> How do I “find on” 101 and get the 101 record?
>
> Is it a matter of dealing with it as character instead of as numeral?
>
> I’m about to test if I do the where clause as if the number is text.. and it’s a text field column that I’m finding on… I wonder if it will work then?
>
> Any ideas about this?
>
>
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
-- 
______________________________________________________________
Cuidemos del medio ambiente. Por favor no imprima este e-mail si no es necesario.
Please consider the environment before printing.