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

asked 06 Feb '20, 12:33

murraysobol's gravatar image

murraysobol
(suspended)
accept rate: 16%

edited 07 Feb '20, 07:56

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050


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
permanent link

answered 07 Feb '20, 05:55

Justin%20Willey's gravatar image

Justin Willey
7.6k137179249
accept rate: 20%

1

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

I was able to correct my errors. Murray

(07 Feb '20, 13:55) murraysobol

I'm not familiar with ORACLE and SYS_REFCURSOR, either, but it seems that the SYS_REFCURSOR is meant to give the client calling the ORACLE STP control over how many rows from the result set it wants to fetch. (So I would assume that otherwise the client would be supplied with all rows.)

If this is correct, please have a look at Mark's thorough description of the way SQL Anywhere's procedures handle their result sets:

In a nutshell, SQL Anywhere generally just provides as many rows as the client requests, so that seems quite similar to the SYS_REFCURSOR facility IMVHO.

permanent link

answered 07 Feb '20, 08:21

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

1

That is a really useful reference - thanks

(07 Feb '20, 08:57) Justin Willey
Replies hidden

Yep, the Golden WATCOM rule:)

(07 Feb '20, 09:14) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×105

question asked: 06 Feb '20, 12:33

question was seen: 977 times

last updated: 07 Feb '20, 13:55