[SOLVED] INTERBASE bad parameter number error when using subqueries

INTERBASE bad parameter number error when using subqueries

I have been working on switching over to an Interbase database from SQLITE. One of the issues I have run into is a "bad parameter number" error. So, I went back to one of the sample databases that Embarcadero supplies (EMPLOYEE) to tried various SQL commands to reproduce the problem.

So here is a scenario: I want a query that gives the average sales amount for all sales to a particular customer over a pre-specified amount. So, here is a query that works for that:

SELECT AVG(TOTAL_VALUE) AS AvgSales
FROM SALES
WHERE TOTAL_VALUE > :TOTAL_VALUE
  AND CUST_NO = :CUSTNO

Now, I want to store that average in a table. But how can you do that? Here is what I tried:

UPDATE <table>
SET <Column> = (SELECT AVG(TOTAL_VALUE) AS AvgSales
                FROM SALES
                WHERE TOTAL_VALUE > :TOTAL_VALUE
                  AND CUST_NO = :CUST_NO)

But when you try to execute that, it gives the error message "bad parameter number"

If you hard code the two parameters, it works fine:

UPDATE <table>
SET <Column> = (SELECT AVG(TOTAL_VALUE) AS AvgSales
                FROM SALES
                WHERE TOTAL_VALUE > 10000
                  AND CUST_NO = 1001)

After more experimenting, it looks to me like any parameter in a subquery will generate that same error message rather than executing. For example, this also generates the same error message (and hard coding the parameters works):

SELECT AMT
FROM
    (SELECT AVG(TOTAL_VALUE) AS AvgSales
     FROM  SALES
     WHERE TOTAL_VALUE > :TOTAL_VALUE
       AND CUST_NO = :CUSTNO
    ) X(Amt)

So, how do you go about storing such a value in an Interbase table?

Solution

Using a procedure can solve your problem, the scheme :

CREATE OR ALTER PROCEDURE UPDATEAVERAGES(CUSTNO INTEGER,TOT NUMERIC())
AS 
DECLARE VARIABLE NEWVALUE NUMERIC();
BEGIN 
SELECT AVG(TOTAL_VALUE) FROM SALES WHERE TOTAL_VALUE>:TOT AND CUST_NO=:CUSTNO INTO :NEWVALUE;
UPDATE <table> SET <column>=:NEWVALUE <where>;
END;