RE: SQL View Problem.. Is there a Solution?

Make them CLOBs.

V6R1 will allow you longer record length (64k??). I think IBM might PTF
that enhancement back to V5R4. I know it'll work in a regular SELECT
statement, not sure about view.
That said, you may exceed that limit as well, so CLOBs are still probably a
better overall solution. Something like:

CREATE VIEW viewA AS
(SELECT CAST(longField1 AS CLOB(500K)) AS longField1,
CAST(longField2 AS CLOB(500K)) AS longField2
FROM myFile1 INNER JOIN myFile2 USING(commonIdField))

Hopefully your .NET tooling can handle CLOBs.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: SQL View Problem.. Is there a Solution?

I am getting the following error (trimmed for content):

SQL State: 54010
Vendor Code: -101
Message: [SQL0101] SQL statement too long or complex. Cause . . . . . :
The SQL statement is longer than the limit allowed for length or
complexity. The reason code is 3. One of the following reason codes
indicates the error: 3 - The sum of the lengths of the non-LOB columns in a
select list, table, view definition, or user defined table function is
greater than 32766 or the definition contains a LOB and the sum of the
lengths specified on the ALLOCATE clause for varying-length fields and the
non-varying field lengths is greater than 32740. The maximum length is
reduced if any of the columns are varying-length or allow null values.

Is there any way to get around this? I am trying to create a .NET reporting
services report and have two very long comment fields that are needed in the
view. Breaking it into two views isn't working for this report. Otherwise it
is back to the drawing board.