CODEWITHSUNDEEP
sqlstored-proceduresfirebirdfirebird-psql
gdekoker
gdekoker

Reputation: 195

Assistance with Firebird SQL stored procedure

I have a very simple stored procedure:

ALTER PROCEDURE SP_BALANCE_USD
(
  PER SMALLINT
)
RETURNS
(
  ACCOUNT_NUMBER CHAR(21),
  AMOUNT NUMERIC(15, 4)
)
AS
BEGIN
    SELECT
        L.ACCOUNT_NUMBER, SUM(-(CURRROUND(L.DEBIT,2) - CURRROUND(L.CREDIT,2)))
    FROM
        LEDGER L
    WHERE
        L.LEDGER_ACCOUNT = '31621' AND L.PERIOD = :PER
    GROUP BY
        L.ACCOUNT_NUMBER
    INTO
        ACCOUNT_NUMBER, AMOUNT
END;

I am getting an error:

TOKEN UNKNOWN LINE 22 COLUMN 1.

Line 22 column 1 refers to the last line of the code.

Somewhere I am making a really simple syntax error and for some reason I am not able to track it down. Any assistance will be greatly appreciated.

Upvotes: 1

Views: 182

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 108992

A token unknown error always includes the unknown token in the error message, in this case the unknown token is END:

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 23, column 1
END

Inside a stored procedure, each statement must end with a semi-colon.

The corrected procedure is:

ALTER PROCEDURE SP_BALANCE_USD
(
  PER SMALLINT
)
RETURNS
(
  ACCOUNT_NUMBER CHAR(21),
  AMOUNT NUMERIC(15, 4)
)
AS
BEGIN
    SELECT
        L.ACCOUNT_NUMBER, SUM(-(CURRROUND(L.DEBIT,2) - CURRROUND(L.CREDIT,2)))
    FROM
        LEDGER L
    WHERE
        L.LEDGER_ACCOUNT = '31621' AND L.PERIOD = :PER
    GROUP BY
        L.ACCOUNT_NUMBER
    INTO
        ACCOUNT_NUMBER, AMOUNT;
END

Notice the ; after AMOUNT on the last line of the SELECT statement.

Upvotes: 2

Related Questions