Error code 1252 Compiled code too long

[fox26apgmr]

Hi,
I am getting error code 1252 (the statement exceeded the size of the FoxPro internal buffer). I've shortened the statement to 1407 by abbreviating, removing blanks, etc.. It's an sql statement with UNIONs. Anyone know what the buffer size is? Is there a way to increase the buffer?
TIA, Joe

 

[rob444]

The max length of a command-line is 2048 bytes.

Perhaps you should show the statement in question here...

Rob,

 

[rgbean]

Joe,
Are you using any macros (&) in this command? They must be expanded and their length counts toward the 2048 that Rob mentions.

Rick

 

[fox26apgmr]

This is the query before I shortened it. Each select works ok on it's own. The UNION of the first and second or the UNION of the second and third work also.
SELECT N.acctno, f_name, l_name, street, city, state, zip, h_phone,;
bill_name, schoolname, ongoing, timetocan, bal_due, pymt_due,;
pymt_ddate, pymt_num, t_num_pymt, lettertype, cancelby, frequency,;
SPACE(19) AS CN, " " AS CT, { / / } AS CX, SPACE(20) AS BA,;
SPACE(9) AS RN, " " AS CS, SPACE(30) AS BN, descriptn;
FROM Newcontr N, Category K;
WHERE (LEFT(N.acctno,3)+N.category=K.category;
OR "000"+N.category=K.category);
AND N.acctno NOT IN(SELECT acctno FROM Ma_Ccd WHERE active);
AND N.acctno NOT IN(SELECT acctno FROM Ma_Eps WHERE active);
UNION;
SELECT N.acctno, f_name, l_name, street, city, state, zip, h_phone,;
bill_name, schoolname, ongoing, timetocan, bal_due, pymt_due,;
pymt_ddate, pymt_num, t_num_pymt, lettertype, cancelby, frequency,;
cardnumber AS CN, cardtype AS CT, expiration AS CX, SPACE(20) AS BA,;
SPACE(9) AS RN, " " AS CS, SPACE(30) AS BN, descriptn;
FROM Newcontr N, Ma_ccd C, Category K;
WHERE (LEFT(N.acctno,3)+N.category=K.category;
OR "000"+N.category=K.category);
AND C.acctno=N.acctno;
UNION;
SELECT N.acctno, f_name, l_name, street, city, state, zip, h_phone,;
bill_name, schoolname, ongoing, timetocan, bal_due, pymt_due,;
pymt_ddate, pymt_num, t_num_pymt, lettertype, cancelby, frequency,;
SPACE(19) AS CN, SPACE(4) AS CT, { / / } AS CX, E.bnk_ac_num AS BA,;
E.bnk_rt_num AS RN, E.bnk_acct_t AS CS, E.bnk_name AS BN,descriptn;
FROM Newcontr N, Ma_Eps E, Category K;
WHERE (LEFT(N.acctno,3)+N.category=K.category;
OR "000"+N.category=K.category);
AND E.acctno=N.acctno INTO CURSOR result
This is the error I get when I compile:
Error in line 31: Compiled code for this line too long.
Thanks, Joe

 

[rgbean]

Joe,
Because you are real close to the limit, there is one more &quot;fix&quot; that seems to work. Eliminate all the &quot; AS <field>&quot;s in the 2nd and 3rd SELECTs. By definition, the field names comes from the first SELECT, so the others are just ignored anyway.

Rick

 

[rob444]

Hi Joe,

Having a first look at the sql statement there might be a confusion with &quot;Ma_ccd C&quot; because C also is the 3rd workarea.

You could also shorten your sql statement by appointing the aliasses to the used tables before you use the sql statement.

something like:

use table1 alias x in 0
use table2 alias y in 0
use table 3 alias z in 0
....
select .....from x,y,z

Rob.

 

[rob444]

{ / / } could be shortened to {} which works the same.

Rob.

 

[fox26apgmr]

A combination of eliminating all the &quot; AS <field>&quot;s in the 2nd and 3rd SELECTs and using {} in place of { / / } did it. Thanks for all of your suggestions!