Here is SQL_TEXT column in SQL_DATA TABLE:
SQL_TEXT
------------------------------------------------------------------------

SELECT RC_L_NAME1,
RC_L_NAME2,
RC_L_STREET,
SUBSTR(RC_L_CITYST,1,LENGTH(STRIP(RC_L_CITYST)) - 3),
SUBSTR(RC_L_CITYST,LENGTH(STRIP(RC_L_CITYST)) - 2,3)
FROM VALEVSRC
WHERE RC_L_TIN > 000000000000
AND
RC_L_NAME1 = 'BANK OF AMERICA'
AND
RC_L_LEVYTYPE = 'A'
AND
ORDER BYRC_L_NAME2;

I am running the following query trying to parsing using recursion(&
delimiter):


WITH TEMP1(R, C1) AS
( SELECT ROW_NUMBER() OVER() R,STRIP(SQL_TEXT) FROM SQL_DATA),
TEMP2(R, C1) AS
(SELECT R , C1
From TEMP1
WHERE R = 1
UNION ALL
SELECT TEMP2.R + 1
,TEMP2.C1 || '&' || TEMP1.C1
FROM TEMP1, TEMP2
WHERE TEMP1.R = TEMP2.R + 1 )
SELECT C1
FROM TEMP2
WHERE R = (SELECT MAX(R) FROM TEMP2);

And i got an error:

sqlcode: -433

SQL0433N Value "SELECT RC_L_NAME1,&RC_L_NAME2,&RC_L_STREET,&SUBSTR
(RC_L_CITY " is too long.
Explanation:

The value "<value>" required truncation by a system (built-in) cast or
adjustment function, which was called to transform the value in some
way. The truncation is not allowed where this value is used.

How to fix this query. It is working in other cases?
Thank's in advance

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200803/1

lenygold via DBMonster.com wrote:
> Here is SQL_TEXT column in SQL_DATA TABLE:
> SQL_TEXT
> ------------------------------------------------------------------------
>
> SELECT RC_L_NAME1,
> RC_L_NAME2,
> RC_L_STREET,
> SUBSTR(RC_L_CITYST,1,LENGTH(STRIP(RC_L_CITYST)) - 3),
> SUBSTR(RC_L_CITYST,LENGTH(STRIP(RC_L_CITYST)) - 2,3)
> FROM VALEVSRC
> WHERE RC_L_TIN > 000000000000
> AND
> RC_L_NAME1 = 'BANK OF AMERICA'
> AND
> RC_L_LEVYTYPE = 'A'
> AND
> ORDER BYRC_L_NAME2;
>
> I am running the following query trying to parsing using recursion(&
> delimiter):
>
>
> WITH TEMP1(R, C1) AS
> ( SELECT ROW_NUMBER() OVER() R,STRIP(SQL_TEXT) FROM SQL_DATA),
> TEMP2(R, C1) AS
> (SELECT R , C1
> From TEMP1
> WHERE R = 1
> UNION ALL
> SELECT TEMP2.R + 1
> ,TEMP2.C1 || '&' || TEMP1.C1
> FROM TEMP1, TEMP2
> WHERE TEMP1.R = TEMP2.R + 1 )
> SELECT C1
> FROM TEMP2
> WHERE R = (SELECT MAX(R) FROM TEMP2);
>
> And i got an error:
>
> sqlcode: -433
>
> SQL0433N Value "SELECT RC_L_NAME1,&RC_L_NAME2,&RC_L_STREET,&SUBSTR
> (RC_L_CITY " is too long.
> Explanation:
>
> The value "<value>" required truncation by a system (built-in) cast or
> adjustment function, which was called to transform the value in some
> way. The truncation is not allowed where this value is used.
>
> How to fix this query. It is working in other cases?
> Thank's in advance
>
The recursion seed determines the length. So you need to add some padding.

SELECT R , CAST(C1 AS VARCHAR(<somebigger number>)) FROM


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Thank You Serge it is working.

Serge Rielau wrote:
>> Here is SQL_TEXT column in SQL_DATA TABLE:
>> SQL_TEXT
>[quoted text clipped - 46 lines]
>> How to fix this query. It is working in other cases?
>> Thank's in advance
>
>The recursion seed determines the length. So you need to add some padding.
>
>SELECT R , CAST(C1 AS VARCHAR(<somebigger number>)) FROM
>

--
Message posted via http://www.dbmonster.com