What is the best approach to convert an oracle Procedure that uses SYS_REFCURSOR

[Former Member]

I have an Oracle procedure that uses SYS_REFCURSOR to return the output. Oracle SQL:

CREATE OR REPLACE PROCEDURE p_purchase_sale_amounts
( adt                           IN  date,
  cursor_x                     OUT  SYS_REFCURSOR
)
AS
   ld_offsetdate               date;

BEGIN
   ld_offsetdate               := adt;

   OPEN cursor_x FOR
   SELECT type,
          FLOOR(SUM(amount1)),
          currency_code,
          currency_description,
          CASE WHEN type = 'A/P'
                   THEN CAST('TotalPurchases' AS varchar2(50))
                   ELSE CAST('TotalSales' AS varchar2(50))
          END                                  AS xlm_tag
     FROM (SELECT 'A/R'                        AS type,
                  COALESCE(SUM((ROUND(s1_prepayment.extended_amount,2) + ROUND(s1_prepayment.tax_1_amount, 2) + ROUND(s1_prepayment.tax_2_amount, 2) + ROUND(s1_prepayment.tax_3_amount,2))),0)
                                               AS amount1,
                  s1_prepayment.currency_code  AS currency_code,
                  s1_currency.description      AS currency_description
             FROM s1_prepayment,
                  s1_grainsmart_option,
                  s1_currency
            WHERE s1_prepayment.invoice_date >= ld_offsetdate
              AND s1_grainsmart_option.go_key = 1
              AND s1_grainsmart_option.ar_software_interface <> 'GS'
              AND s1_prepayment.currency_code = s1_currency.currency_code
            GROUP BY s1_prepayment.currency_code,
                     s1_currency.currency_code,
                     s1_currency.description) a
            GROUP BY currency_code,
                     description
          ) xxx
    GROUP BY xxx.type,
             xxx.currency_code,
             xxx.currency_description;

END;
/

Is there a similar methodology on SQL Anywhere?

Thanks Murray

[justin_willey]

I don't know anything about Oracle, but if you want a SQLA procedure to return a result set, you don't need to declare a cursor, just put the sql query in the body of the procedure and include a result clause eg:

ALTER PROCEDURE "pears"."UserPayrollRuns" 
(IN @EmployeeCodeList char(255), IN @startdate date, IN @enddate date)
RESULT(EmployeeCode char(6),CompanyCode char(2),PaymentDate date,Period char(21),GrossPay double,Deductions double, 
NetPay double,PeriodNumber char(2),payslipfullpath long varchar) 
begin
    select 
       s.EmployeeCode as "EMPLOYEECODE",
       s.CompanyCode as "COMPANYCODE", 
       date(PaymentDate) as "PAYMENTDATE",
    etc etc
    group by whatever

 end

[Former Member]

Thanks, people - I now have a better understanding re: getting result sets from a procedure.

I was able to correct my errors. Murray