RE:VARCHAR LENGTH

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