psalvini@maytag.waterloo.edu (Paul Salvini) (01/31/91)
Help!
I am trying to get the following code to work in a trigger-
select field
into :screenfield
from table
where table.field like nvl(:screenfield2,'%');
In other words: If the screen input is blank use the Wildcard to
select all records.
Note: Similar coding works in SQL*Plus
The trigger compiles fine
Run-time error of ORA-00920 Invalid relational operator
Running on Unix Symmetry
So: Can anyone offer suggestions to this? I have tried other coding
alternatives such as
where ((table.field = :screenfield2) or (:screenfield2 is null))
BUT
this does not use an index if the input is given! BAD STUFF!!!
Any and all suggestions appreciated..
--
===============================================================================
Paul A. Salvini (519) 884-1973 x2913
Wilfrid Laurier University ** INTERNET: psalvini@maytag.UWaterloo.ca
Department of Computing Servicesmmorris@oracle.com (Martin Morris) (02/01/91)
In article <1991Jan31.135725.6241@maytag.waterloo.edu> psalvini@maytag.waterloo.edu (Paul Salvini) writes: >Help! > >I am trying to get the following code to work in a trigger- > > select field > into :screenfield > from table > where table.field like nvl(:screenfield2,'%'); > >In other words: If the screen input is blank use the Wildcard to >select all records. > >Note: Similar coding works in SQL*Plus > The trigger compiles fine > Run-time error of ORA-00920 Invalid relational operator > Running on Unix Symmetry > >So: Can anyone offer suggestions to this? I have tried other coding >alternatives such as > where ((table.field = :screenfield2) or (:screenfield2 is null)) >BUT > this does not use an index if the input is given! BAD STUFF!!! > >Any and all suggestions appreciated.. Rather than using null to test the screen field I would recommend using '', ie an empty string. Rather than using NVL in the first case it could be rewritten as: select field into :screenfield from table where table.field like DECODE(:screenfield2,'','%',:screenfield2); The second method (which is the method I prefer) would be: where ((table.field = :screenfield2) or (:screenfield2 = '')) This should use an index on table.field if :screenfield2 != ''. Note that if :screenfield2 = '' then a full table scan will take place in both cases - thats fine as you want all the rows back anyway. Another solution would be to test :screenfield2 within your trigger/procedure/whatever and branch to one of two SQL statements - one with the predicate table.field = :screenfield2 and the other with no predicate at all. Hope this helps... Martin Morris >-- >============================================================================== >Paul A. Salvini (519) 884-1973 x2913 >Wilfrid Laurier University ** INTERNET: psalvini@maytag.UWaterloo.ca >Department of Computing Services