Jain Jose.C wrote :
>Hi,
> I want ot add a big query into a Varchar String . I defined it
>Varchar(8000) . but it not supporting to store 8000 characters. It throws
>error like
>---- Error -------------------------------
>Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
>General error;-2010 POS(1) Assignment impossible, char value too long.
>call SI_SF_FINDDEPACCTBYCUST(1)
>How can i solve the problem. Please please help me...I really stuck with the
>problem
>by
>Jain Jose.C
The problem occurred in the following db-procedure :
CREATE DBPROC SI_SF_FINDDEPACCTBYCUST
(
IN PCUSTID FIXED(38)
)
RETURNS CURSOR
AS
VAR MYSQL VARCHAR(4000); CURSORSTR CHAR(8000);
BEGIN
SET MYSQL ='SELECT
1 temp,
acct.accountId,
acct.status status,
acpf.acctPortfolioId,
acctPortfolioNumber,
acctSuffixNumber,
acctDescription,
acctBalance,
createTimestamp,
updateTimestamp,
UpdateCounter,
la.PmtInstAmount as paymentAmount,
la.dueDate,
la.disbursed,
fc.financialControlId,
interestRate,
pp.registered,
pp.productCurrencyType as currency,
fc.FinancialControlCode as ProdServiceCode,
(SELECT LineOfCredit FROM SI_LoanProduct WHERE acct.FinancialControlId =
SI_LoanProduct.ProductId) AS LineOfCredit,
(SELECT OnlineAlertDays FROM SI_LoanProduct WHERE acct.FinancialControlId =
SI_LoanProduct.ProductId) AS OnlineAlertDays,
(SELECT OnlineRestDays FROM SI_LoanProduct WHERE acct.FinancialControlId =
SI_LoanProduct.ProductId) AS OnlineRestDays,
(SELECT TypeCode FROM SI_FinancialControlTypes , SI_FinancialControls
WHERE SI_FinancialControlTypes.FinancialControlTypeId =
SI_FinancialControls.FinancialControlTypeId
AND SI_FinancialControls.FinancialControlId =
acct.FinancialControlId) AS
AcctType,
(SELECT CategoryCode FROM SI_FinancialControlTypes , SI_FinancialControls
WHERE SI_FinancialControlTypes.FinancialControlTypeId =
SI_FinancialControls.FinancialControlTypeId
AND SI_FinancialControls.FinancialControlId =
acct.FinancialControlId) AS
CategoryCode,
(select CHR( count(*) ) from SI_AccountAssociates aa where acct.accountId
= aa.accountId and isDeleted!=''Y'') as acctAssocCount,
(select count(m.accountId) from SI_Memo m, SI_BusinessDate b where
m.customerId = 1 and m.Deleted = 0 and
SIGN(YEAR(m.ExpiryDate) * 1000 + DAYOFYEAR(m.ExpiryDate) -
YEAR(b.BusinessDate) * 1000 + DAYOFYEAR(b.BusinessDate)) *
DATEDIFF(m.ExpiryDate,b.BusinessDate) >= 0 and m.accountId = acct.accountId
and
SIGN(YEAR(m.StartDate) * 1000 + DAYOFYEAR(m.StartDate) -
YEAR(b.BusinessDate) * 1000 + DAYOFYEAR(b.BusinessDate)) *
DATEDIFF(m.StartDate,b.BusinessDate) <= 0 and m.PriorityCode = ''CRIT'') as
criticalMemo,
(select count(m.accountId) from SI_Memo m, SI_BusinessDate b where
m.customerId = 1 and m.Deleted = 0 and
SIGN(YEAR(m.ExpiryDate) * 1000 + DAYOFYEAR(m.ExpiryDate) -
YEAR(b.BusinessDate) * 1000 + DAYOFYEAR(b.BusinessDate)) *
DATEDIFF(m.ExpiryDate,b.BusinessDate) >= 0 and m.accountId = acct.accountId
and
SIGN(YEAR(m.StartDate) * 1000 + DAYOFYEAR(m.StartDate) -
YEAR(b.BusinessDate) * 1000 + DAYOFYEAR(b.BusinessDate)) *
DATEDIFF(m.StartDate,b.BusinessDate) <= 0 and m.PriorityCode = ''HIGH'') as
highMemo,
(select count(m.accountId) from SI_Memo m, SI_BusinessDate b where
m.customerId = 1 and m.Deleted = 0 and
SIGN(YEAR(m.ExpiryDate) * 1000 + DAYOFYEAR(m.ExpiryDate) -
YEAR(b.BusinessDate) * 1000 + DAYOFYEAR(b.BusinessDate)) *
DATEDIFF(m.ExpiryDate,b.BusinessDate) >= 0 and m.accountId = acct.accountId
and
DATEDIFF(m.StartDate , b.BusinessDate) <= 0 and m.PriorityCode =
''MED'')
as mediumMemo,
(select count(m.accountId) from SI_Memo m, SI_BusinessDate b where
m.customerId = 1 and m.Deleted = 0 and
DATEDIFF( m.ExpiryDate , b.BusinessDate) >= 0 and m.accountId =
acct.accountId and
DATEDIFF( m.StartDate , b.BusinessDate) <= 0 and m.PriorityCode =
''LOW'')
as lowMemo,
(select count(*) from SI_StopPayment spmt where spmt.accountId =
acct.accountId) as stopPayment,
(select count(m.accountId) from SI_Memo m, SI_BusinessDate b where
m.customerId = 1 and m.Deleted = 0 and
m.RestrictionCode <> ''NONE'' and m.accountId = acct.accountId) as
restrictions,
(acct.acctBalance - la.CreditLimit) as overLimitAmount,
(select rif from si_registeredPlan where registeredPlanId in (select
registeredPlanId from si_registeredAccount where accountId =
acct.AccountId)) as RRIF
FROM
SI_accountPortfolios acpf,
SI_productProperties pp,
SI_FinancialControls fc,
SI_Accounts acct,
SI_LOANACCOUNT la
WHERE
acpf.CustomerId = 1 AND
acpf.AcctPortfolioId = acct.acctPortfolioId(+) AND
acct.FinancialControlId = pp.ProductId AND
fc.FinancialControlId = pp.ProductId AND
acct.accountId = la.accountId(+) AND
acct.deleted = 0 AND
(la.Undone is NULL OR la.Undone = 0)' ;
$CURSOR = 'CUSTOMER_CURSOR';
CURSORSTR ='DECLARE ' || $CURSOR || ' CURSOR FOR ' || MYSQL;
EXECUTE CURSORSTR;
IF ($RC <> 0) AND ($RC <> 100) THEN STOP ($RC);
END ;
The select statement inside has a length of 4051 bytes, which does not fit into to
variable MYSQL of length 4000.
This produces error -2010. Please define MYSQL a little bit wider, for
example 'MYSQL CHAR(4100);'.
Regards,
Thomas
--
Thomas Anhaus
SAP DB, SAP Labs Berlin
[EMAIL PROTECTED]
http://www.sapdb.org/
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
