> >Are there any known problems using joins involving
> >stored procedures in IBO 3.6?? The following sql
> >(and others) causes IBConsole (vers 1.0.0.326) and
> >ib_wisql to hang and the server to terminate
> >abnormally. Whereas they work ok with some other tools.
> >
> >SELECT
> > C.WIDECUSTOMERID,
> > A.*
> >FROM
> > AR_AGING('%','1/1/2001') A INNER JOIN CUSTOMER C ON
> > A.CUSTOMERID = C.CUSTOMERID
> >ORDER BY 1

> Please post the ddl for your stored procedure.
>

The following select sql fails with IBO but works
with FIBPlus and Python using gvib


SELECT
C.WIDECUSTOMERID,
A.*
FROM
AR_AGING_LOAN('%','1/1/2001') A INNER JOIN CUSTOMER C ON
A.CUSTOMERID = C.CUSTOMERID
ORDER BY 1


CREATE TABLE CUSTOMER
(
CUSTOMERID VARCHAR(10) NOT NULL,
WIDECUSTOMERID COMPUTED BY ( F_RIGHT(' ' ||
CUSTOMERID, 16) ),

....etc
)



CREATE PROCEDURE AR_AGING_LOAN
(
INCUSTOMERID VARCHAR(16),
INENDDATE TIMESTAMP
)
RETURNS
(
CUSTOMERID VARCHAR(16),
LASTARTRANSID INTEGER,
FIRSTTRANSDATE TIMESTAMP,
LASTTRANSDATE TIMESTAMP,

D120 NUMERIC(9, 2),
D90 NUMERIC(9, 2),
D60 NUMERIC(9, 2),
D30 NUMERIC(9, 2),
D0 NUMERIC(9, 2),
BALANCE NUMERIC(9, 2),
DPLUS NUMERIC(9, 2)
)
AS
DECLARE VARIABLE LASTCUSTOMERID VARCHAR(16) ;
DECLARE VARIABLE TMPCUSTOMERID VARCHAR(16) ;
DECLARE VARIABLE ISPSEUDOTRANS INTEGER;
DECLARE VARIABLE AMOUNT NUMERIC(9,2);
DECLARE VARIABLE CREDIT NUMERIC(9,2);
DECLARE VARIABLE ARBALANCE NUMERIC(9,2);
DECLARE VARIABLE AGE NUMERIC(9,2);
DECLARE VARIABLE ARTRANSID INTEGER;
DECLARE VARIABLE TRANSDATE TIMESTAMP;
DECLARE VARIABLE ARTYPE VARCHAR(16);
DECLARE VARIABLE PREVARTRANSID INTEGER;
DECLARE VARIABLE PREVTRANSDATE TIMESTAMP;
DECLARE VARIABLE LASTCREDITDATE TIMESTAMP ;
BEGIN
LASTCUSTOMERID = '';
FOR
SELECT
CAST(A.CUSTOMERID AS VARCHAR(16)) AS CUSTOMERID,
A.TRANSDATE ,
A.ARTRANSID ,
CAST(A.ARTYPE AS VARCHAR(16)) AS ARTYPE,
A.ARAMOUNT,
CAST(A.ARBALANCE AS NUMERIC(9,2)) AS ARBALANCE
FROM
ARTRANS A
WHERE
(A.CUSTOMERID LIKE :INCUSTOMERID)
UNION
SELECT
CAST(L.CUSTOMERID AS VARCHAR(16)) AS CUSTOMERID,
CAST(LID.DUEDATE AS TIMESTAMP) AS TRANSDATE,
CAST(A.ARTRANSID AS INTEGER) AS ARTRANSID,
CAST(NULL AS VARCHAR(16)) AS ARTYPE,
LID.DUEAMOUNT AS ARAMOUNT,
CAST(0 AS NUMERIC(9,2)) AS ARBALANCE
FROM
(LOAN L INNER JOIN LOANINSTALMENTDUE LID ON
L.LOANID = LID.LOANID )
INNER JOIN ARTRANS A ON
L.ARTRANSID=A.ARTRANSID
WHERE
(A.REVERSALARTRANSID IS NULL)
AND
(L.CUSTOMERID LIKE :INCUSTOMERID)
ORDER BY 1, 2
INTO
:TMPCUSTOMERID,
:TRANSDATE,
:ARTRANSID,
:ARTYPE,
:AMOUNT,
:ARBALANCE
DO
BEGIN
IF (LASTCUSTOMERID <> TMPCUSTOMERID) THEN
BEGIN
IF (LASTCUSTOMERID <> '') THEN
BEGIN
CUSTOMERID = LASTCUSTOMERID ;
D0 = D0 + CREDIT ;
BALANCE = D120 + D90 + D60 + D30 + D0 ;
SUSPEND ;
END
D120 = 0 ;
D90 = 0 ;
D60 = 0 ;
D30 = 0 ;
D0 = 0 ;
BALANCE = 0 ;
CREDIT = 0 ;
LASTCUSTOMERID = TMPCUSTOMERID ;
FIRSTTRANSDATE = TRANSDATE ;
LASTCREDITDATE = NULL ;
END
IF (ARTYPE='Loan') THEN
AMOUNT = 0 ;
AGE = INENDDATE - TRANSDATE;
IF (AGE < 0) THEN
BEGIN
DPLUS = DPLUS + AMOUNT ;
AMOUNT = 0 ;
END
IF (AMOUNT >0) THEN
BEGIN
IF (AGE >= 120) THEN
D120 = D120 + AMOUNT;
ELSE IF (AGE >= 90) THEN
D90 = D90 + AMOUNT ;
ELSE IF (AGE >= 60) THEN
D60 = D60 + AMOUNT;
ELSE IF (AGE >= 30) THEN
D30 = D30 + AMOUNT ;
ELSE IF (AGE >= 0) THEN
D0 = D0 + AMOUNT ;
ELSE
DPLUS = DPLUS + AMOUNT ;
END
ELSE
BEGIN
LASTCREDITDATE = TRANSDATE ;
CREDIT = CREDIT + AMOUNT ;
END

D120 = D120 + CREDIT ;
CREDIT = D120;
IF (D120 < 0) THEN D120 = 0;
IF (CREDIT > 0) THEN CREDIT = 0;

D90 = D90 + CREDIT ;
CREDIT = D90;
IF (D90 < 0) THEN D90 = 0;
IF (CREDIT > 0) THEN CREDIT = 0;

D60 = D60 + CREDIT ;
CREDIT = D60;
IF (D60 < 0) THEN D60 = 0;
IF (CREDIT > 0) THEN CREDIT = 0;

D30 = D30 + CREDIT ;
CREDIT = D30;
IF (D30 < 0) THEN D30 = 0;
IF (CREDIT > 0) THEN CREDIT = 0;

D0 = D0 + CREDIT ;
CREDIT = D0;
IF (D0 < 0) THEN D0 = 0;
IF (CREDIT > 0) THEN CREDIT = 0;

LASTTRANSDATE = TRANSDATE ;
LASTARTRANSID = ARTRANSID ;
END
IF (LASTCUSTOMERID <> '') THEN
BEGIN
CUSTOMERID = LASTCUSTOMERID ;
D0 = D0 + CREDIT ;
BALANCE = D120 + D90 + D60 + D30+ D0 ;
SUSPEND ;
END

END