I have a set of frequently used search conditions that I find myself
repeating over and over in different places in my application. It would
be nice to be able to encapsulate these conditions into a stored
procedure and just reference the stored procedure in the WHERE clause of
my SELECT statement when I need them. Trouble is, I can't figure out how
to do it or even if it is possible.
Consider the following procedure:
CREATE PROCEDURE IS_MANIFESTED(stop_id bigint) RETURNS (result char(1))
ASBEGIN IF EXISTS (SELECT M.OSM_STOP_ID
FROM OPS_MANIFEST_STOPS M LEFT JOIN
OPS_STOP_REC O ON O.OPS_STOP_ID = M.OSM_STOP_ID
LEFT JOIN OPS_HEADER P ON P.PB_ID = O.OPS_ORDER_ID
WHERE M.OSM_PKUP_DELV_ID = :stop_id AND
COALESCE(P.PB_STATUS, 0) <> 9)) THEN BEGIN result = 'T'; END ELSE BEGIN
result = 'F'; END SUSPEND; EXIT;END
It would be nice to be able to use the IS_MANIFEST procedure in a select
statement like this:
SELECT * FROM OPS_STOP_REC WHERE IS_MANIFESTED(OPS_STOP_ID) <> 'T';
to get all un-manifested loads.
But this doesn't get past the syntax checker. Is there some way I can
accomplish what I want? I suppose I could use the procedure in a JOIN
clause but it doesn't seem as understandable or elegant.






[Non-text portions of this message have been removed]