Re: Useful SQL tip for handling optional parameters

Another solution is to just use dynamic SQL when you don't know at compile time what the query should look like. It really is pretty darn fast, and with the SQL descriptor support and snippets in RDi, it is very easy to do. All of the alternatives such as yours and the Case option can cause full table scans.

Mark Murphy
Atlas Data Systems
mmurphy@xxxxxxxxxxxxxxx


-----David Gibbs <david@xxxxxxxxxxxx> wrote: -----
To: "rpg400-l@xxxxxxxxxxxx" <rpg400-l@xxxxxxxxxxxx>
From: David Gibbs <david@xxxxxxxxxxxx>
Date: 12/07/2016 10:58AM
Subject: Useful SQL tip for handling optional parameters


Folks:

I just had a situation where I needed to handle an optional parameter on a procedure, where the parameter was being used for record selection in a SQL statement.

I didn't want to deal with multiple SQL statements based on if the parameter was passed or not.

So here's routine I put together ...

dcl-proc myproc export;
dcl-pi *n;
parm1 char (10) const;
parm2 char (10) const options(*nopass);
end-pi;

dcl-s value2 like(parm2);

if %parms > 1;
value2 = parm2;
else;
value2 = '*ALL';
endif;

exec sql
delete from FILE
where FIELD1 = :parm1 and
(FIELD2 = :value2 or :value2 = '*ALL');

end-proc;

I wasn't sure if the ":hostvalue = 'constant'" criteria was going to work, but it does appear to work fine.

david