Pawluk, Scott wrote:
> Anyone out there with some Interbase and SAP DB experience... here's a
> problem... I'm migrating from an Interbase directory to SAP
> DB. Here's a
> stored procedure on Interbase that I simply don't know how to
> translate over
> to SAP DB.
>
> It would be called as such in an application via SQL:
>
> SELECT RPID, DESCRIPTION, SELECTED
> FROM VIEWPATRONREADINGPREFS(10)
>
>
> I appreciate any help that anyone can offer.
Hi,
ok, as far as I know, noone else answered. So, let's try.
Even if we had dbprocs returning resultsets (we DO not have,
we hope to have them in the near future, we will keep you
informed, don't ask now for time schedule) they would not be
possible in the FROM-clause as given above.
Therefore two statements would be necessary
CALL VIEWPATRONREADINGPREFS(10)
(in this dbproc you had to use a declare <cursorname> CURSOR FOR SELECT...)
and then SELECT ... FROM <cursorname>
with one statement I only see the chance
to put the whole select given in the dbproc into the from-clause
or just say
select rpid, description, 'Y' as selected
from readingpreferenceslist
where rpid in
(select rpid
from patronreadingpreferences
where patronid = :patronid)
union
select rpid, description, 'N'
from readingpreferenceslist
where rpid not in
(select rpid
from patronreadingpreferences
where patronid = :patronid)
But I assume you want to hide the select or reuse it at several places
in the application. Then usually a view would be ok. But with parameter
in the subquery no view can be used.
No, sorry, I do not know a good one-statement-solution.
Elke
SAP Labs Berlin
> ************************************
> CREATE PROCEDURE "VIEWPATRONREADINGPREFS"
> (
> "PATRONID" NUMERIC(9, 0)
> )
> RETURNS
> (
> "RPID" NUMERIC(9, 0),
> "DESCRIPTION" VARCHAR(50),
> "SELECTED" VARCHAR(1)
> )
> AS
> begin
> for select rpid, description, 'Y'
> from readingpreferenceslist
> where rpid in
> (select rpid
> from patronreadingpreferences
> where patronid = :patronid)
> union
> select rpid, description, 'N'
> from readingpreferenceslist
> where rpid not in
> (select rpid
> from patronreadingpreferences
> where patronid = :patronid)
> into :rpid, :description, :selected
> do
> begin
> suspend;
> end
> end
> ************************************
>
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
