Norbert Felde wrote:
> Hallo,
>
> I would need a specified part of a result. I wrote the
> following dbproc,
> but it dous not work, and I do not know, how I could solve my problem.
>
> CREATE DBPROC CEG_KH_LIST (
> IN CID Integer,
> IN ORD Varchar(30),
> IN SORT Integer,
> IN SROW Integer,
> IN EROW Integer
> )
> RETURNS CURSOR AS
> $CURSOR = 'LIST_RES';
> DECLARE :$CURSOR CURSOR FOR
> SELECT * FROM (
> SELECT ROWNO AS RN,*
> FROM DBA.table1 AS A, DBA.table2 AS B
> WHERE A.CID = :CID AND A.ID = B.AID(+)
> ORDER BY :ORD )
> WHERE RN >= :SROW AND RN < :EROW;
>
> It should list the result from the row SROW to EROW. The
> order is also
> important.
> The first problem width this proc is that the ORDER BY clause is on
> wrong place, MaxDB said. The other, the value of ROWNO is
> correct only
> where the join is succeded, it is 0 elsewhere.
>
1. SORT-Parameter seems to be superfluous
2. Try something like this:
CREATE DBPROC CEG_KH_LIST (
IN CID Integer,
IN ORD Varchar(30),
IN SORT Integer,
IN SROW Integer,
IN EROW Integer
)
RETURNS CURSOR AS
$CURSOR = 'LIST_RES';
DECLARE HELP_CURSOR CURSOR FOR
SELECT ROWNO AS RN,*
FROM DBA.table1 AS A, DBA.table2 AS B
WHERE A.CID = :CID AND A.ID = B.AID(+)
ORDER BY :ORD;
DECLARE :$CURSOR CURSOR FOR
SELECT * FROM HELP_CURSOR
WHERE RN >= :SROW AND RN < :EROW;
CLOSE HELP_CURSOR;
3. Oops, you want to specify the output-column-no used for ordering as a parameter?
That will not work. Use an unsigned integer or prepare a dynamic sql changing this
value into an unsigned integer.
4. You mean, that all resulting rows which are only there because of the (+)
will have ROWNO = 0? We will have to check. Which version are you using?
Elke
SAP Labs Berlin
> I hope you understand my problem.
>
> Thanks,
>
> Norbert
>
>
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
