Rocket DevOps (formerly Aldon)

We have inherited an SQL file definition from previous developers.  At the time, they made a good decision to move a file from DDS to DDL.  This file is a reference file and has many, many fields. When we go back into the source member to change a set of fields from 9,0 to 12,0 we end up with "SQL statement too long or complex."  The source member is a *TABLE/SQL.  I tested using ALTER TABLE from STRSQL and that all worked.  Now, what type of object do I create in Aldon so I can list all the fields to be updated with the new definitions and be able to run that source member tied to the original file. 

Yes, the problem is the CREATE TABLE failing.  When I tried to pull the LABEL statements out, it still failed. 


------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US
------------------------------

Aha!  Even when I tried to use ALTER TABLE, I still got the error.  From our point of view, we removed the fields needing to be changed from the base reference file and added them to a new reference file leaving the field names the same.  Now, we need only add the new reference file to the objects consuming the adjusted fields. 


 3 - The sum of the lengths of the non-LOB and XML columns in a select list, table, view definition, or user defined table function is greater than 32766 

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US
------------------------------

Original Message:
Sent: 07-25-2022 12:02
From: David Taylor
Subject: SQL Too long or complex.

We have inherited an SQL file definition from previous developers.  At the time, they made a good decision to move a file from DDS to DDL.  This file is a reference file and has many, many fields. When we go back into the source member to change a set of fields from 9,0 to 12,0 we end up with "SQL statement too long or complex."  The source member is a *TABLE/SQL.  I tested using ALTER TABLE from STRSQL and that all worked.  Now, what type of object do I create in Aldon so I can list all the fields to be updated with the new definitions and be able to run that source member tied to the original file. 

Yes, the problem is the CREATE TABLE failing.  When I tried to pull the LABEL statements out, it still failed. 


------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US
------------------------------

Hi David, 

Are you getting this error - The sum of the lengths of the non-LOB and XML columns in a select list, table, view definition, or user defined table function is greater than 32766 - with your alter table statement?

Is the table defined with pseudo source? Is the original DDL for the CREATE TABLE statement longer than 32766?
If not, you may be able to simply check out the pseudo source and redefine the columns (by updating the DDL) and recreate and promote it. 

If necessary, please send us the object's attributes (STRLMI > 1 > 11) and pseudo source. 

Best regards,
Jack

------------------------------
Jack Zhang
Software Engineer
Rocket Software Inc
Waltham MA US
------------------------------

Original Message:
Sent: 07-25-2022 15:08
From: David Taylor
Subject: SQL Too long or complex.

Aha!  Even when I tried to use ALTER TABLE, I still got the error.  From our point of view, we removed the fields needing to be changed from the base reference file and added them to a new reference file leaving the field names the same.  Now, we need only add the new reference file to the objects consuming the adjusted fields. 


 3 - The sum of the lengths of the non-LOB and XML columns in a select list, table, view definition, or user defined table function is greater than 32766 

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US

Original Message:
Sent: 07-25-2022 12:02
From: David Taylor
Subject: SQL Too long or complex.

We have inherited an SQL file definition from previous developers.  At the time, they made a good decision to move a file from DDS to DDL.  This file is a reference file and has many, many fields. When we go back into the source member to change a set of fields from 9,0 to 12,0 we end up with "SQL statement too long or complex."  The source member is a *TABLE/SQL.  I tested using ALTER TABLE from STRSQL and that all worked.  Now, what type of object do I create in Aldon so I can list all the fields to be updated with the new definitions and be able to run that source member tied to the original file. 

Yes, the problem is the CREATE TABLE failing.  When I tried to pull the LABEL statements out, it still failed. 


------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US
------------------------------

Here you go. Thanks for looking.  This relates to the case Manu reopened.

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US
------------------------------

Original Message:
Sent: 07-25-2022 15:34
From: Jack Zhang
Subject: SQL Too long or complex.

Hi David, 

Are you getting this error - The sum of the lengths of the non-LOB and XML columns in a select list, table, view definition, or user defined table function is greater than 32766 - with your alter table statement?

Is the table defined with pseudo source? Is the original DDL for the CREATE TABLE statement longer than 32766?
If not, you may be able to simply check out the pseudo source and redefine the columns (by updating the DDL) and recreate and promote it. 

If necessary, please send us the object's attributes (STRLMI > 1 > 11) and pseudo source. 

Best regards,
Jack

------------------------------
Jack Zhang
Software Engineer
Rocket Software Inc
Waltham MA US

Original Message:
Sent: 07-25-2022 15:08
From: David Taylor
Subject: SQL Too long or complex.

Aha!  Even when I tried to use ALTER TABLE, I still got the error.  From our point of view, we removed the fields needing to be changed from the base reference file and added them to a new reference file leaving the field names the same.  Now, we need only add the new reference file to the objects consuming the adjusted fields. 


 3 - The sum of the lengths of the non-LOB and XML columns in a select list, table, view definition, or user defined table function is greater than 32766 

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US

Original Message:
Sent: 07-25-2022 12:02
From: David Taylor
Subject: SQL Too long or complex.

We have inherited an SQL file definition from previous developers.  At the time, they made a good decision to move a file from DDS to DDL.  This file is a reference file and has many, many fields. When we go back into the source member to change a set of fields from 9,0 to 12,0 we end up with "SQL statement too long or complex."  The source member is a *TABLE/SQL.  I tested using ALTER TABLE from STRSQL and that all worked.  Now, what type of object do I create in Aldon so I can list all the fields to be updated with the new definitions and be able to run that source member tied to the original file. 

Yes, the problem is the CREATE TABLE failing.  When I tried to pull the LABEL statements out, it still failed. 


------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US
------------------------------

Thanks for your attachments, David! I assume the pseudo source is before your intended updates. 

Do you have a copy with the desired update? Can you also send us the failed create report, compile listing and joblog?

Thanks again,
Jack

------------------------------
Jack Zhang
Software Engineer
Rocket Software Inc
Waltham MA US
------------------------------

Original Message:
Sent: 07-25-2022 15:41
From: David Taylor
Subject: SQL Too long or complex.

Here you go. Thanks for looking.  This relates to the case Manu reopened.

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US

Original Message:
Sent: 07-25-2022 15:34
From: Jack Zhang
Subject: SQL Too long or complex.

Hi David, 

Are you getting this error - The sum of the lengths of the non-LOB and XML columns in a select list, table, view definition, or user defined table function is greater than 32766 - with your alter table statement?

Is the table defined with pseudo source? Is the original DDL for the CREATE TABLE statement longer than 32766?
If not, you may be able to simply check out the pseudo source and redefine the columns (by updating the DDL) and recreate and promote it. 

If necessary, please send us the object's attributes (STRLMI > 1 > 11) and pseudo source. 

Best regards,
Jack

------------------------------
Jack Zhang
Software Engineer
Rocket Software Inc
Waltham MA US

Original Message:
Sent: 07-25-2022 15:08
From: David Taylor
Subject: SQL Too long or complex.

Aha!  Even when I tried to use ALTER TABLE, I still got the error.  From our point of view, we removed the fields needing to be changed from the base reference file and added them to a new reference file leaving the field names the same.  Now, we need only add the new reference file to the objects consuming the adjusted fields. 


 3 - The sum of the lengths of the non-LOB and XML columns in a select list, table, view definition, or user defined table function is greater than 32766 

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US

Original Message:
Sent: 07-25-2022 12:02
From: David Taylor
Subject: SQL Too long or complex.

We have inherited an SQL file definition from previous developers.  At the time, they made a good decision to move a file from DDS to DDL.  This file is a reference file and has many, many fields. When we go back into the source member to change a set of fields from 9,0 to 12,0 we end up with "SQL statement too long or complex."  The source member is a *TABLE/SQL.  I tested using ALTER TABLE from STRSQL and that all worked.  Now, what type of object do I create in Aldon so I can list all the fields to be updated with the new definitions and be able to run that source member tied to the original file. 

Yes, the problem is the CREATE TABLE failing.  When I tried to pull the LABEL statements out, it still failed. 


------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US
------------------------------

There may be some duplication here.

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US
------------------------------

Original Message:
Sent: 07-25-2022 16:10
From: Jack Zhang
Subject: SQL Too long or complex.

Thanks for your attachments, David! I assume the pseudo source is before your intended updates. 

Do you have a copy with the desired update? Can you also send us the failed create report, compile listing and joblog?

Thanks again,
Jack

------------------------------
Jack Zhang
Software Engineer
Rocket Software Inc
Waltham MA US

Original Message:
Sent: 07-25-2022 15:41
From: David Taylor
Subject: SQL Too long or complex.

Here you go. Thanks for looking.  This relates to the case Manu reopened.

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US

Original Message:
Sent: 07-25-2022 15:34
From: Jack Zhang
Subject: SQL Too long or complex.

Hi David, 

Are you getting this error - The sum of the lengths of the non-LOB and XML columns in a select list, table, view definition, or user defined table function is greater than 32766 - with your alter table statement?

Is the table defined with pseudo source? Is the original DDL for the CREATE TABLE statement longer than 32766?
If not, you may be able to simply check out the pseudo source and redefine the columns (by updating the DDL) and recreate and promote it. 

If necessary, please send us the object's attributes (STRLMI > 1 > 11) and pseudo source. 

Best regards,
Jack

------------------------------
Jack Zhang
Software Engineer
Rocket Software Inc
Waltham MA US

Original Message:
Sent: 07-25-2022 15:08
From: David Taylor
Subject: SQL Too long or complex.

Aha!  Even when I tried to use ALTER TABLE, I still got the error.  From our point of view, we removed the fields needing to be changed from the base reference file and added them to a new reference file leaving the field names the same.  Now, we need only add the new reference file to the objects consuming the adjusted fields. 


 3 - The sum of the lengths of the non-LOB and XML columns in a select list, table, view definition, or user defined table function is greater than 32766 

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US

Original Message:
Sent: 07-25-2022 12:02
From: David Taylor
Subject: SQL Too long or complex.

We have inherited an SQL file definition from previous developers.  At the time, they made a good decision to move a file from DDS to DDL.  This file is a reference file and has many, many fields. When we go back into the source member to change a set of fields from 9,0 to 12,0 we end up with "SQL statement too long or complex."  The source member is a *TABLE/SQL.  I tested using ALTER TABLE from STRSQL and that all worked.  Now, what type of object do I create in Aldon so I can list all the fields to be updated with the new definitions and be able to run that source member tied to the original file. 

Yes, the problem is the CREATE TABLE failing.  When I tried to pull the LABEL statements out, it still failed. 


------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US
------------------------------

Hi David, 

Thanks for your attachments!  Please see if the following suggestion is useful
https://www.ibm.com/support/pages/sql0101n-statement-too-long-or-too-complex
> db2 update db cfg for <database name> using stmtheap 8000

There may be ways to change  system variables also. Please let us know how it goes.

In the meantime, I'll do more research and test your SQL source locally. 

Best regards,
Jack

------------------------------
Jack Zhang
Software Engineer
Rocket Software Inc
Waltham MA US
------------------------------

Original Message:
Sent: 07-25-2022 16:22
From: David Taylor
Subject: SQL Too long or complex.

There may be some duplication here.

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US

Original Message:
Sent: 07-25-2022 16:10
From: Jack Zhang
Subject: SQL Too long or complex.

Thanks for your attachments, David! I assume the pseudo source is before your intended updates. 

Do you have a copy with the desired update? Can you also send us the failed create report, compile listing and joblog?

Thanks again,
Jack

------------------------------
Jack Zhang
Software Engineer
Rocket Software Inc
Waltham MA US

Original Message:
Sent: 07-25-2022 15:41
From: David Taylor
Subject: SQL Too long or complex.

Here you go. Thanks for looking.  This relates to the case Manu reopened.

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US

Original Message:
Sent: 07-25-2022 15:34
From: Jack Zhang
Subject: SQL Too long or complex.

Hi David, 

Are you getting this error - The sum of the lengths of the non-LOB and XML columns in a select list, table, view definition, or user defined table function is greater than 32766 - with your alter table statement?

Is the table defined with pseudo source? Is the original DDL for the CREATE TABLE statement longer than 32766?
If not, you may be able to simply check out the pseudo source and redefine the columns (by updating the DDL) and recreate and promote it. 

If necessary, please send us the object's attributes (STRLMI > 1 > 11) and pseudo source. 

Best regards,
Jack

------------------------------
Jack Zhang
Software Engineer
Rocket Software Inc
Waltham MA US

Original Message:
Sent: 07-25-2022 15:08
From: David Taylor
Subject: SQL Too long or complex.

Aha!  Even when I tried to use ALTER TABLE, I still got the error.  From our point of view, we removed the fields needing to be changed from the base reference file and added them to a new reference file leaving the field names the same.  Now, we need only add the new reference file to the objects consuming the adjusted fields. 


 3 - The sum of the lengths of the non-LOB and XML columns in a select list, table, view definition, or user defined table function is greater than 32766 

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US

Original Message:
Sent: 07-25-2022 12:02
From: David Taylor
Subject: SQL Too long or complex.

We have inherited an SQL file definition from previous developers.  At the time, they made a good decision to move a file from DDS to DDL.  This file is a reference file and has many, many fields. When we go back into the source member to change a set of fields from 9,0 to 12,0 we end up with "SQL statement too long or complex."  The source member is a *TABLE/SQL.  I tested using ALTER TABLE from STRSQL and that all worked.  Now, what type of object do I create in Aldon so I can list all the fields to be updated with the new definitions and be able to run that source member tied to the original file. 

Yes, the problem is the CREATE TABLE failing.  When I tried to pull the LABEL statements out, it still failed. 


------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US
------------------------------

Jack, 

See the link and read the pages.  I have not been in these waters before, so everywhere is the deep end.  I see the command, put I am not sure where to type it.  I does not work from a command line, QSHELL command line,  or STRSQL command line.

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US
------------------------------

Original Message:
Sent: 07-26-2022 08:08
From: Jack Zhang
Subject: SQL Too long or complex.

Hi David, 

Thanks for your attachments!  Please see if the following suggestion is useful
https://www.ibm.com/support/pages/sql0101n-statement-too-long-or-too-complex
> db2 update db cfg for <database name> using stmtheap 8000

There may be ways to change  system variables also. Please let us know how it goes.

In the meantime, I'll do more research and test your SQL source locally. 

Best regards,
Jack

------------------------------
Jack Zhang
Software Engineer
Rocket Software Inc
Waltham MA US

Original Message:
Sent: 07-25-2022 16:22
From: David Taylor
Subject: SQL Too long or complex.

There may be some duplication here.

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US

Original Message:
Sent: 07-25-2022 16:10
From: Jack Zhang
Subject: SQL Too long or complex.

Thanks for your attachments, David! I assume the pseudo source is before your intended updates. 

Do you have a copy with the desired update? Can you also send us the failed create report, compile listing and joblog?

Thanks again,
Jack

------------------------------
Jack Zhang
Software Engineer
Rocket Software Inc
Waltham MA US

Original Message:
Sent: 07-25-2022 15:41
From: David Taylor
Subject: SQL Too long or complex.

Here you go. Thanks for looking.  This relates to the case Manu reopened.

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US

Original Message:
Sent: 07-25-2022 15:34
From: Jack Zhang
Subject: SQL Too long or complex.

Hi David, 

Are you getting this error - The sum of the lengths of the non-LOB and XML columns in a select list, table, view definition, or user defined table function is greater than 32766 - with your alter table statement?

Is the table defined with pseudo source? Is the original DDL for the CREATE TABLE statement longer than 32766?
If not, you may be able to simply check out the pseudo source and redefine the columns (by updating the DDL) and recreate and promote it. 

If necessary, please send us the object's attributes (STRLMI > 1 > 11) and pseudo source. 

Best regards,
Jack

------------------------------
Jack Zhang
Software Engineer
Rocket Software Inc
Waltham MA US

Original Message:
Sent: 07-25-2022 15:08
From: David Taylor
Subject: SQL Too long or complex.

Aha!  Even when I tried to use ALTER TABLE, I still got the error.  From our point of view, we removed the fields needing to be changed from the base reference file and added them to a new reference file leaving the field names the same.  Now, we need only add the new reference file to the objects consuming the adjusted fields. 


 3 - The sum of the lengths of the non-LOB and XML columns in a select list, table, view definition, or user defined table function is greater than 32766 

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US

Original Message:
Sent: 07-25-2022 12:02
From: David Taylor
Subject: SQL Too long or complex.

We have inherited an SQL file definition from previous developers.  At the time, they made a good decision to move a file from DDS to DDL.  This file is a reference file and has many, many fields. When we go back into the source member to change a set of fields from 9,0 to 12,0 we end up with "SQL statement too long or complex."  The source member is a *TABLE/SQL.  I tested using ALTER TABLE from STRSQL and that all worked.  Now, what type of object do I create in Aldon so I can list all the fields to be updated with the new definitions and be able to run that source member tied to the original file. 

Yes, the problem is the CREATE TABLE failing.  When I tried to pull the LABEL statements out, it still failed. 


------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US
------------------------------

Note - This topic continued via Rocket Tech Support Case # 00822709.​

------------------------------
Jack Zhang
Software Engineer
Rocket Software Inc
Waltham MA US
------------------------------

Original Message:
Sent: 07-26-2022 08:23
From: David Taylor
Subject: SQL Too long or complex.

Jack, 

See the link and read the pages.  I have not been in these waters before, so everywhere is the deep end.  I see the command, put I am not sure where to type it.  I does not work from a command line, QSHELL command line,  or STRSQL command line.

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US

Original Message:
Sent: 07-26-2022 08:08
From: Jack Zhang
Subject: SQL Too long or complex.

Hi David, 

Thanks for your attachments!  Please see if the following suggestion is useful
https://www.ibm.com/support/pages/sql0101n-statement-too-long-or-too-complex
> db2 update db cfg for <database name> using stmtheap 8000

There may be ways to change  system variables also. Please let us know how it goes.

In the meantime, I'll do more research and test your SQL source locally. 

Best regards,
Jack

------------------------------
Jack Zhang
Software Engineer
Rocket Software Inc
Waltham MA US

Original Message:
Sent: 07-25-2022 16:22
From: David Taylor
Subject: SQL Too long or complex.

There may be some duplication here.

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US

Original Message:
Sent: 07-25-2022 16:10
From: Jack Zhang
Subject: SQL Too long or complex.

Thanks for your attachments, David! I assume the pseudo source is before your intended updates. 

Do you have a copy with the desired update? Can you also send us the failed create report, compile listing and joblog?

Thanks again,
Jack

------------------------------
Jack Zhang
Software Engineer
Rocket Software Inc
Waltham MA US

Original Message:
Sent: 07-25-2022 15:41
From: David Taylor
Subject: SQL Too long or complex.

Here you go. Thanks for looking.  This relates to the case Manu reopened.

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US

Original Message:
Sent: 07-25-2022 15:34
From: Jack Zhang
Subject: SQL Too long or complex.

Hi David, 

Are you getting this error - The sum of the lengths of the non-LOB and XML columns in a select list, table, view definition, or user defined table function is greater than 32766 - with your alter table statement?

Is the table defined with pseudo source? Is the original DDL for the CREATE TABLE statement longer than 32766?
If not, you may be able to simply check out the pseudo source and redefine the columns (by updating the DDL) and recreate and promote it. 

If necessary, please send us the object's attributes (STRLMI > 1 > 11) and pseudo source. 

Best regards,
Jack

------------------------------
Jack Zhang
Software Engineer
Rocket Software Inc
Waltham MA US

Original Message:
Sent: 07-25-2022 15:08
From: David Taylor
Subject: SQL Too long or complex.

Aha!  Even when I tried to use ALTER TABLE, I still got the error.  From our point of view, we removed the fields needing to be changed from the base reference file and added them to a new reference file leaving the field names the same.  Now, we need only add the new reference file to the objects consuming the adjusted fields. 


 3 - The sum of the lengths of the non-LOB and XML columns in a select list, table, view definition, or user defined table function is greater than 32766 

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US

Original Message:
Sent: 07-25-2022 12:02
From: David Taylor
Subject: SQL Too long or complex.

We have inherited an SQL file definition from previous developers.  At the time, they made a good decision to move a file from DDS to DDL.  This file is a reference file and has many, many fields. When we go back into the source member to change a set of fields from 9,0 to 12,0 we end up with "SQL statement too long or complex."  The source member is a *TABLE/SQL.  I tested using ALTER TABLE from STRSQL and that all worked.  Now, what type of object do I create in Aldon so I can list all the fields to be updated with the new definitions and be able to run that source member tied to the original file. 

Yes, the problem is the CREATE TABLE failing.  When I tried to pull the LABEL statements out, it still failed. 


------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US
------------------------------

David, 

The link above was a DB2 command, so can only be used when connected via a DB2 client.

Anyway, as Manu proposed via Tech Support Case # 00822709, the reason for the issue and solution is:
"source file has reached the maximum size of 32766 allowed for an SQL Table on our iSeries and we may have no alternative but to create a 2nd field reference file with new fields going forward."

Have a wonderful day!
Jack

------------------------------
Jack Zhang
Software Engineer
Rocket Software Inc
Waltham MA US
------------------------------

Original Message:
Sent: 07-26-2022 08:23
From: David Taylor
Subject: SQL Too long or complex.

Jack, 

See the link and read the pages.  I have not been in these waters before, so everywhere is the deep end.  I see the command, put I am not sure where to type it.  I does not work from a command line, QSHELL command line,  or STRSQL command line.

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US

Original Message:
Sent: 07-26-2022 08:08
From: Jack Zhang
Subject: SQL Too long or complex.

Hi David, 

Thanks for your attachments!  Please see if the following suggestion is useful
https://www.ibm.com/support/pages/sql0101n-statement-too-long-or-too-complex
> db2 update db cfg for <database name> using stmtheap 8000

There may be ways to change  system variables also. Please let us know how it goes.

In the meantime, I'll do more research and test your SQL source locally. 

Best regards,
Jack

------------------------------
Jack Zhang
Software Engineer
Rocket Software Inc
Waltham MA US

Original Message:
Sent: 07-25-2022 16:22
From: David Taylor
Subject: SQL Too long or complex.

There may be some duplication here.

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US

Original Message:
Sent: 07-25-2022 16:10
From: Jack Zhang
Subject: SQL Too long or complex.

Thanks for your attachments, David! I assume the pseudo source is before your intended updates. 

Do you have a copy with the desired update? Can you also send us the failed create report, compile listing and joblog?

Thanks again,
Jack

------------------------------
Jack Zhang
Software Engineer
Rocket Software Inc
Waltham MA US

Original Message:
Sent: 07-25-2022 15:41
From: David Taylor
Subject: SQL Too long or complex.

Here you go. Thanks for looking.  This relates to the case Manu reopened.

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US

Original Message:
Sent: 07-25-2022 15:34
From: Jack Zhang
Subject: SQL Too long or complex.

Hi David, 

Are you getting this error - The sum of the lengths of the non-LOB and XML columns in a select list, table, view definition, or user defined table function is greater than 32766 - with your alter table statement?

Is the table defined with pseudo source? Is the original DDL for the CREATE TABLE statement longer than 32766?
If not, you may be able to simply check out the pseudo source and redefine the columns (by updating the DDL) and recreate and promote it. 

If necessary, please send us the object's attributes (STRLMI > 1 > 11) and pseudo source. 

Best regards,
Jack

------------------------------
Jack Zhang
Software Engineer
Rocket Software Inc
Waltham MA US

Original Message:
Sent: 07-25-2022 15:08
From: David Taylor
Subject: SQL Too long or complex.

Aha!  Even when I tried to use ALTER TABLE, I still got the error.  From our point of view, we removed the fields needing to be changed from the base reference file and added them to a new reference file leaving the field names the same.  Now, we need only add the new reference file to the objects consuming the adjusted fields. 


 3 - The sum of the lengths of the non-LOB and XML columns in a select list, table, view definition, or user defined table function is greater than 32766 

------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US

Original Message:
Sent: 07-25-2022 12:02
From: David Taylor
Subject: SQL Too long or complex.

We have inherited an SQL file definition from previous developers.  At the time, they made a good decision to move a file from DDS to DDL.  This file is a reference file and has many, many fields. When we go back into the source member to change a set of fields from 9,0 to 12,0 we end up with "SQL statement too long or complex."  The source member is a *TABLE/SQL.  I tested using ALTER TABLE from STRSQL and that all worked.  Now, what type of object do I create in Aldon so I can list all the fields to be updated with the new definitions and be able to run that source member tied to the original file. 

Yes, the problem is the CREATE TABLE failing.  When I tried to pull the LABEL statements out, it still failed. 


------------------------------
David Taylor
Senior Developer
Range Resources Corporation
Fort Worth TX US
------------------------------