SQL & PL/SQL

Hallo All,

I have a block of PL/SQL with an SQL call that I want to filter just sometimes (i.e. during a test run). At other times (normally) there would be no filter applied.

I have tried with the following syntax ...

if p_runtype != 'full' then
myFilter := ' WHERE myfield=' || myString || ' AND ';
else
myFilter := '';
end if;

... which I would then slot into the SQL thus ...

SELECT bla, bla, ...
FROM myTable
myFilter
;

... with the myFilter string (including the filter criteria myString) being either include or not (as a string).

Here I find that, as I am dropping a string (rather than a parameter) into the SQL, I get (understandably) the following error ...

PL/SQL: ORA-00920: invalid relational operator

So here my question is whether I need to switch to EXECUTE IMMEDIATE ?

Or is there a way, using standard syntax, to add / remove a whole filter component such for example ...

WHERE order_region='south'

... rather than just the parameter (south)?

Or maybe I should simply write out the whole SQL twice, once with and once without the filter, and then use an 'if clause' to decide which will be run?

Any tips on this would be a great help.

Regards and thanks,
Alan Searle