SQL Anywhere database - Getting Error Stored Procedure "Returns more than one row"

Author

Getting Error Stored Procedure "Returns more than one row"

Paul

2005-08-20, 8:23 pm

Hello everyone, I am trying to write a stored procedure that has one result
variable.
I want to do an interim step - get 2 values from another table, divide them
and take that result and apply that to the sum of rows of another table.

When I run the procedure, I get "SELECT Returns more than one row"

Below is the code for the procedure. How can I get the 2 interim values
from the Invoice table and apply that against the sum of rows from the
Payments table?

Thank you in advance for your valuable assistance with this.

Sincerely,
Paul Grossman
PAULGRO Consulting LLC

ALTER PROCEDURE "test"."sp_GetAmtPaid"(IN InvoiceNo INT, IN GetConvAmt BIT)
RESULT(SumAmts NUMERIC(19,5))

BEGIN

DECLARE vConvFactor NUMERIC(19,5);
DECLARE vExtended NUMERIC(19,5);
DECLARE vExtended_Conv NUMERIC(19,5);

IF InvoiceNo IS NULL THEN
SELECT 0 AS SumAmts FROM Dummy

ELSE

IF GetConvAmt = 0 THEN
SELECT SUM(Amount) AS SumAmts FROM Payment WHERE Invoice =
InvoiceNo

ELSE

SELECT ISNULL(Extended,0), ISNULL(Extended_Conv
,0)
INTO vExtended, vExtended_Conv
FROM Invoice
WHERE InvoiceNo = InvoiceNo;

IF vExtended = 0 OR vExtended_Conv = 0 THEN
SET vConvFactor = 1;
ELSE
SET vConvFactor = vExtended_Conv / vExtended;
END IF;

SELECT SUM(PaymentAmount) * vConvFactor FROM Payment
WHERE Invoice = InvoiceNo;

END IF

END IF

END

Paul Horan[TeamSybase]

2005-08-21, 8:23 pm

This statement:
SELECT ISNULL(Extended,0), ISNULL(Extended_Conv
,0)
INTO vExtended, vExtended_Conv
FROM Invoice
WHERE InvoiceNo = InvoiceNo;

could result in that error, if there's more than one row in Invoice for a given InvoiceNo.

--
Paul Horan[TeamSybase]

"Paul" <paul.grossman@paulgro.com> wrote in message news:43078ac4$1@foru
ms-2-dub...
> Hello everyone, I am trying to write a stored procedure that has one result variable.
> I want to do an interim step - get 2 values from another table, divide them and take that result and apply that to the
> sum of rows of another table.
>
> When I run the procedure, I get "SELECT Returns more than one row"
>
> Below is the code for the procedure. How can I get the 2 interim values from the Invoice table and apply that against
> the sum of rows from the Payments table?
>
> Thank you in advance for your valuable assistance with this.
>
> Sincerely,
> Paul Grossman
> PAULGRO Consulting LLC
>
>
> ALTER PROCEDURE "test"."sp_GetAmtPaid"(IN InvoiceNo INT, IN GetConvAmt BIT)
> RESULT(SumAmts NUMERIC(19,5))
>
> BEGIN
>
> DECLARE vConvFactor NUMERIC(19,5);
> DECLARE vExtended NUMERIC(19,5);
> DECLARE vExtended_Conv NUMERIC(19,5);
>
> IF InvoiceNo IS NULL THEN
> SELECT 0 AS SumAmts FROM Dummy
>
> ELSE
>
> IF GetConvAmt = 0 THEN
> SELECT SUM(Amount) AS SumAmts FROM Payment WHERE Invoice = InvoiceNo
>
> ELSE
>
> SELECT ISNULL(Extended,0), ISNULL(Extended_Conv
,0)
> INTO vExtended, vExtended_Conv
> FROM Invoice
> WHERE InvoiceNo = InvoiceNo;
>
> IF vExtended = 0 OR vExtended_Conv = 0 THEN
> SET vConvFactor = 1;
> ELSE
> SET vConvFactor = vExtended_Conv / vExtended;
> END IF;
>
> SELECT SUM(PaymentAmount) * vConvFactor FROM Payment
> WHERE Invoice = InvoiceNo;
>
> END IF
>
> END IF
>
> END
>

Paul

2005-08-22, 3:23 am

Hi Paul, thanks for the help - I forgot to mention that there is only one
row per invoice (invoiceNo is the primary key).

- Paul Grossman

"Paul Horan[TeamSybase]" < paulhATvcisolutionsD
OTcom> wrote in message
news:4308e25b$1@foru
ms-2-dub...
> This statement:
> SELECT ISNULL(Extended,0), ISNULL(Extended_Conv
,0)
> INTO vExtended, vExtended_Conv
> FROM Invoice
> WHERE InvoiceNo = InvoiceNo;
>
> could result in that error, if there's more than one row in Invoice for a
> given InvoiceNo.
>
> --
> Paul Horan[TeamSybase]
>
> "Paul" <paul.grossman@paulgro.com> wrote in message
> news:43078ac4$1@foru
ms-2-dub...
>
>

Greg Fenton

2005-08-22, 3:23 am

Paul wrote:[color=darkred
]
> Hi Paul, thanks for the help - I forgot to mention that there is only one
> row per invoice (invoiceNo is the primary key).
>
> - Paul Grossman
>
> "Paul Horan[TeamSybase]" < paulhATvcisolutionsD
OTcom> wrote in message
> news:4308e25b$1@foru
ms-2-dub...
>

But how is the engine to determine the difference between "InvoiceNo"
and "InvoiceNo" ?

I strongly suggest you consider a different variable name since it is
the same as a column name. In our team we prefix all variable names
with an "@" to distinguish it from other database identifiers (e.g.
"@InvoiceNo").

Also consider prefixing the column names with the table name or a reference:

SELECT ISNULL(Extended,0), ISNULL(Extended_Conv
,0)
INTO vExtended, vExtended_Conv
FROM Invoice as i
WHERE i.InvoiceNo = @InvoiceNo;

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Paul

2005-08-22, 3:23 am

Hi Greg, what you said did the trick and resolved the problem.

Thank you for helping me solve this problem.

Sincerely,
Paul Grossman

"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> wrote in message
news:430942ef$1@foru
ms-1-dub...
> Paul wrote:
>
>
> But how is the engine to determine the difference between "InvoiceNo" and
> "InvoiceNo" ?
>
> I strongly suggest you consider a different variable name since it is the
> same as a column name. In our team we prefix all variable names with an
> "@" to distinguish it from other database identifiers (e.g. "@InvoiceNo").
>
> Also consider prefixing the column names with the table name or a
> reference:
>
> SELECT ISNULL(Extended,0), ISNULL(Extended_Conv
,0)
> INTO vExtended, vExtended_Conv
> FROM Invoice as i
> WHERE i.InvoiceNo = @InvoiceNo;
>
> Hope this helps,
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

Sponsored Links