Hi,
Use
Where (select p.xxx from proc p) ....

Regards,
Karol Bieniaszewski

----- Reply message -----
Od: "sboydlns" <sboydlns@...>
Do: <firebird-support@yahoogroups.com>
Temat: [firebird-support] Using stored procedure results in the WHERE clause
Data: pon., lip 8, 2013 22:11
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]











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