火鸟存储过程数据类型转换时发生溢出错误

Question

我有以下存储过程:

SET TERM ^ ;
CREATE PROCEDURE SP_OUTWARD_ACCOUNTS (
STARTDATE Timestamp,
ENDDATE Timestamp )
RETURNS (
DATEX Timestamp,
PERSONNAME Varchar(50),
FILENO Varchar(30),
ACCOUNTTYPE Smallint,
AMOUNT Decimal(9,2),
DUEDATE Timestamp,
BANKNAME Varchar(50),
CHECKNO Varchar(10),
NOTES Varchar(200),
PAIDINFULL Smallint,
PAIDSF Decimal(9,2),
BANKACCNO Varchar(20),
CHECKOWNER Varchar(50),
ENDORSEDTO Integer,
STATE Integer,
ID Integer,
MYTRANSID Integer,
MYTRANSTYP Smallint,
PERSONID Integer,
CHECKGIVER Varchar(50) )
AS
BEGIN
for
select * from (
SELECT        ACCOUNTS.DATEX, PEOPLE.NAME as personname, ACCOUNTS.ACCOUNTTYPE, ACCOUNTS.AMOUNT, ACCOUNTS.DUEDATE, BANKS.BANKNAME, ACCOUNTS.CHECKNO, 
                     ACCOUNTS.NOTES, ACCOUNTS.PAIDINFULL, SUM(PAYMENTS.AMOUNTPAID) AS PAIDSF, ACCOUNTS.BANKACCNO, ACCOUNTS.CHECKOWNER, 
                     ACCOUNTS.ENDORSEDTO, ACCOUNTS.STATE, 
                      ACCOUNTS.OUTTRANSID  AS MYTRANSID, 
                     ACCOUNTS.OUTTRANSTYP  AS MYTRANSTYP, ACCOUNTS.ID, 
                     ACCOUNTS.PERSONID, ACCOUNTS.FILENO,'' as checkgiver
FROM            ACCOUNTS LEFT OUTER JOIN
                     PEOPLE ON ACCOUNTS.PERSONID = PEOPLE.ID LEFT OUTER JOIN
                     BANKS ON ACCOUNTS.BANKID = BANKS.BANKID LEFT OUTER JOIN
                     PAYMENTS ON ACCOUNTS.ID = PAYMENTS.ACCOUNTID
WHERE       (ACCOUNTS.DATEX BETWEEN :STARTDATE AND :ENDDATE)  AND (ACCOUNTS.OUTTRANSTYP<>-1) and accounts.ACCOUNTTYPE<>2
GROUP BY ACCOUNTS.DATEX, personname, ACCOUNTS.FILENO, ACCOUNTS.ACCOUNTTYPE, ACCOUNTS.AMOUNT, ACCOUNTS.DUEDATE, BANKS.BANKNAME, ACCOUNTS.CHECKNO, 
                     ACCOUNTS.NOTES, ACCOUNTS.PAIDINFULL, ACCOUNTS.BANKACCNO, ACCOUNTS.CHECKOWNER, ACCOUNTS.ENDORSEDTO, ACCOUNTS.STATE, ACCOUNTS.ID, 
                     MYTRANSID, MYTRANSTYP, ACCOUNTS.PERSONID,checkgiver
union SELECT        ACCOUNTS.DATEX, PEOPLE.NAME as personname, ACCOUNTS.ACCOUNTTYPE, ACCOUNTS.AMOUNT, ACCOUNTS.DUEDATE , BANKS.BANKNAME, ACCOUNTS.CHECKNO, 
                     ACCOUNTS.NOTES, ACCOUNTS.PAIDINFULL, SUM(PAYMENTS.AMOUNTPAID) AS PAIDSF, ACCOUNTS.BANKACCNO, ACCOUNTS.CHECKOWNER, 
                     ACCOUNTS.ENDORSEDTO, ACCOUNTS.STATE, 
                      ACCOUNTS.OUTTRANSID  AS MYTRANSID, 
                     ACCOUNTS.OUTTRANSTYP  AS MYTRANSTYP, ACCOUNTS.ID, 
                     ACCOUNTS.PERSONID, ACCOUNTS.FILENO, x.name as checkgiver
FROM            ACCOUNTS LEFT OUTER JOIN
                     PEOPLE ON ACCOUNTS.ENDORSEDTO = PEOPLE.ID LEFT OUTER JOIN
                     BANKS ON ACCOUNTS.BANKID = BANKS.BANKID LEFT OUTER JOIN
                     PAYMENTS ON ACCOUNTS.ID = PAYMENTS.ACCOUNTID
                    left outer join (select ACCOUNTS.id, PEOPLE.name from PEOPLE inner join ACCOUNTS on people.id=accounts.PERSONID) x on x.ID=accounts.ID
WHERE       (ACCOUNTS.DATEX BETWEEN :STARTDATE AND :ENDDATE)  AND (ACCOUNTS.OUTTRANSTYP<>-1) and accounts.ACCOUNTTYPE=2
GROUP BY ACCOUNTS.DATEX, personname, ACCOUNTS.FILENO, ACCOUNTS.ACCOUNTTYPE, ACCOUNTS.AMOUNT,ACCOUNTS.DUEDATE, BANKS.BANKNAME, ACCOUNTS.CHECKNO, 
                     ACCOUNTS.NOTES, ACCOUNTS.PAIDINFULL, ACCOUNTS.BANKACCNO, ACCOUNTS.CHECKOWNER, ACCOUNTS.ENDORSEDTO, ACCOUNTS.STATE, ACCOUNTS.ID, 
                     MYTRANSID, MYTRANSTYP, ACCOUNTS.PERSONID,checkgiver
           )          
                     order by DATEX
into
:DATEX,
:personname,
:FILENO,
:ACCOUNTTYPE,
:AMOUNT,
:DUEDATE,
:BANKNAME,
:CHECKNO,
:NOTES,
:PAIDINFULL,
:PAIDSF,
:BANKACCNO,
:CHECKOWNER,
:ENDORSEDTO,
:STATE,
:ID,
:MYTRANSID,
:MYTRANSTYP,
:PERSONID,
:checkgiver
DO
begin
suspend;
end
END^
SET TERM ; ^
GRANT EXECUTE
 ON PROCEDURE SP_OUTWARD_ACCOUNTS TO  SYSDBA;

当我在查询屏幕上运行这段代码时，它可以工作，并且它编译为存储过程，但是当我尝试将存储过程运行为:

SELECT p.XX, p.PERSONNAME, p.FILENO, p.ACCOUNTTYPE, p.AMOUNT, p.YY, p.BANKNAME,     p.CHECKNO, p.NOTES, p.PAIDINFULL, p.PAIDSF, p.BANKACCNO, p.CHECKOWNER, p.ENDORSEDTO, p.STATE, p.ID, p.MYTRANSID, p.MYTRANSTYP, p.PERSONID, p.CHECKGIVER
FROM SP_OUTWARD_ACCOUNTS('2014-03-01', '2014-03-20') p

---

IBPP错误发生。

* IBPP::SQLException *背景:声明::获取消息:isc_dsql_fetch failed.

SQL Message: -413数据类型转换时发生溢出。

引擎代码:335544334引擎信息:从字符串"2014-02-28 00:00:00.0000"转换错误在过程' sp_outard_accounts '行:28,col: 1

---

对

怎么了?请帮助。表结构:

    TABLE ACCOUNTS
(
  ACCOUNTTYPE Smallint,
  PERSONID Integer,
  DUEDATE Timestamp,
  NOTES Varchar(200) DEFAULT '',
  AMOUNT Decimal(9,2) DEFAULT 0,
  BANKID Integer,
  DIRECTION Smallint,
  TRANSID Integer DEFAULT -1,
  DATEX Timestamp,
  ID Integer NOT NULL,
  PAIDINFULL Smallint DEFAULT 0,
  CHECKNO Varchar(10),
  TRANSTYPE Smallint DEFAULT -1,
  BANKACCNO Varchar(20) CHARACTER SET ASCII,
  CHECKOWNER Varchar(50),
  ENDORSEDTO Integer DEFAULT 0,
  STATE Integer DEFAULT 0,
  OUTTRANSID Integer DEFAULT -1,
  OUTTRANSTYP Smallint DEFAULT -1,
  DEPOSITBANK Integer DEFAULT -1,
  PARENT Integer DEFAULT -1,
  FILENO Varchar(30),
  CONSTRAINT PK_ACCOUNTS PRIMARY KEY (ID)
);

:

TABLE PEOPLE
(
  CTYPE Smallint,
  NAME Varchar(50),
  COMPANY Varchar(50),
  PHONE Varchar(30),
  MOBILE Varchar(30),
  EMAIL Varchar(40),
  ADDRESS Varchar(120),
  NOTES Varchar(200),
  ID Integer NOT NULL,
  HIDDEN Smallint DEFAULT 0
);

银行:

TABLE BANKS
(
  BANKNAME Varchar(50),
  BANKBALANCE Decimal(9,2) DEFAULT 0,
  BANKID Integer NOT NULL
);

支付:

TABLE PAYMENTS
(
  ACCOUNTID Integer,
  NOTES Varchar(200),
  AMOUNTPAID Decimal(9,2) DEFAULT 0,
  PAYMENTDATE Timestamp,
  ID Integer NOT NULL,
  RECORDDATETIME Timestamp DEFAULT CURRENT_TIMESTAMP,
  DIRECTION Smallint
);

Answer 1

问题是选择中的列与INTO子句中的输出列不匹配。INTO的映射基于位置，而不是名称!

如果你仔细看，它们是这样映射的:

ACCOUNTS.DATEX -------------> :DATEX,
PEOPLE.NAME as personname --> :personname,
ACCOUNTS.ACCOUNTTYPE -------> :FILENO,      <==== Mismatch starts here
ACCOUNTS.AMOUNT ------------> :ACCOUNTTYPE,
ACCOUNTS.DUEDATE -----------> :AMOUNT,
BANKS.BANKNAME -------------> :DUEDATE,
....

当ACCOUNTS.DUEDATE被分配给AMOUNT(这是一个DECIMAL(9,2))时发生问题。这是一个不支持的转换，所以Firebird尝试TIMESTAMP => string (VARCHAR/CHAR) => DECIMAL(9,2)。这个转换失败。

请注意，这似乎不是唯一的列顺序混淆，所以只修复这个并不能解决问题。

最好的解决方案可能是用与INTO子句相同顺序的显式列表替换SELECT *。