Can you search SQL this way?

[Andy Hilton]

Martin

Typically this is often a matter of ’scope’ - or in other words : does the parameter you are using in your statement, exist at the place and time that it is being rendered for use by the database - and here I would question the use of the pSearchName variable……typically this will only exist within that local method

As an example, try (just as an experiment) changing your line to something like :

Calculate SQLText as con("Where active_status = “,pSearchActiveStatus," AND name LIKE ‘%",pSearchName,”%’")
Sta:[SQLText]

In other words try force creating the text of your statement including pSearchActiveStatus and pSearchName first and then add that as your statement - and see if that works….

Combinations of this will show you where/what is causing your statement not to work !

Note : you may then want to add a breakpoint just to inspect your SQLText local variable immediately prior to creating the statement - just so you can be sure of what it says !

Hope it may help

Andy Hilton
Totally Brilliant Software Inc
Phone (US) : (863) 409 4870 
Phone (UK) : 0207 193 8582
Web : www.totallybrilliant.com <http://www.totallybrilliant.com/> 
Helpdesk : http://totallybrilliant.kayako.com
Email : andyh at totallybrilliant.com

> On Oct 22, 2021, at 11:36 AM, Martin Obongita via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:
> 
> Hi @all,
> I have a similar problem.I am trying to search a postgres database with the statement below in a table class:
> Sta:Where active_status = @[pSearchActiveStatus] AND name LIKE '%[pSearchName]%'
> pSearchActiveStatus and pSearchName are parameters of type int and char respectively.
> This statement does not return the desired results.Is the where clause statement written correctly?
> Rgds,Martin.
> 
> 
> 
> 
>    On Friday, October 22, 2021, 05:42:21 PM GMT+3, Andy Hilton <andyh at totallybrilliant.com> wrote:  
> 
> Das
> 
> I think what Phil is meaning is :
> 
> You would simply do a new sql search for whatever got you your list to start with, plus the extra addition of a like (note that depending on the database, ‘ilike’ can also be a case insensitive search) for your column search within that…..
> 
> Andy Hilton
> Totally Brilliant Software Inc
> Phone (US) : (863) 409 4870 
> Phone (UK) : 0207 193 8582
> Web : www.totallybrilliant.com <http://www.totallybrilliant.com/> 
> Helpdesk : http://totallybrilliant.kayako.com
> Email : andyh at totallybrilliant.com
> 
>> On Oct 22, 2021, at 10:36 AM, Phil (OmnisList) via omnisdev-en <omnisdev-en at lists.omnis-dev.com> wrote:
>> 
>> Hi Das,
>> 
>> the string needs quotes...
>> field like '%string%'
>> 
>> A list?
>> That is not a SQL object.
>> What is that stored as? is it binary? so basically no chance of looking up on that.
>> 
>> regards
>> Phil Potter
>> Based in Chester in the UK.
>> 
>> On 22/10/2021 15:18, Das Goravani wrote:
>>> Hello Listers,
>>> 
>>> I’m wondering about two SQL searches or selects.
>>> 
>>> One is if you have a large field full of text, and you want to search for a string inside it. Do you use LIKE %sring% ?
>>> 
>>> And if you have a list, and you want to select on a value in a column, can you do that?
>>> 
>>> That’s it.
>>> 
>>> Thanks
>>> 
>>> Das Goravani
>>> _____________________________________________________________
>>> Manage your list subscriptions athttp://lists.omnis-dev.com
>>> Start a new message ->mailto:omnisdev-en at lists.omnis-dev.com  
>> _____________________________________________________________
>> Manage your list subscriptions at http://lists.omnis-dev.com
>> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 
> 
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com