Compiled Code Too Long

[StacyStacy]

Hi.

I need help. I am joining 2 tables together by whereby I need at lease 75 fields just from one table. After writing the code, I received an error msg, "Required clause not present in command". So as a result, I am re-writing the code, line by line, sometimes, code by code and testing it. I have been successful up until the end when I now receive the following error message: "Compiled code for this line too long"? Why is this happening and what should I do? Here's a sample:

select jt1.ssn, jt1.wia_area, jt1.entertdte1,jt1.lastname, jt1.firstname, jt1.initial, jt1.appdate, jt1.address, jt1.city, jt1.county, jt1.state,;
jt1.zipcode, jt1.areacode, jt1.phone, jt1.age, jt1.birthday, jt1.sex, jt1.citizenshp, jt1.selectserv, jt1.education, jt1.highgrade, jt1.ethnic,;
jt1.indalaskan, jt1.asian, jt1.black, jt1.pacisle, jt1.white, jt1.hispanic, jt1.offender, jt1.limitedeng, jt1.homeless, jt1.runaway, jt1.teenparent,;
jt1.veteran, jt1.vietnamvet, jt1.disablevet, jt1.recsepvet, jt1.laborstat, jt1.grossfminc, jt1.numinfamly, jt1.hourlywage, jt1.wageatdisl, jt1.dislocdate,;
jt1.familystat, jt1.basiceduc, jt1.metroresid, jt1.ssi, jt1.afdc, jt1.refugeasst, jt1.fosterchld, jt1.foodstamp, jt1.inddisable, jt1.econdisadv,;
jt1.jobspartic, jt1.displahome, jt1.rcvdnrpay, jt1.titletype, jt1.needsbased, jt1.certifdate, jt1.ythaddasst, jt1.pellgrant, jt1.adult, jt1.dislworker,;
jt1.oldyouth, jt1.yngyouth, jt1.fromrresp, jt1.fromjtpa, jt1.inadult, jt1.locdworker, jt1.locyouth, jt1.stateyouth, jt1.statedhome, jt1.incumwork,;
jt1.otherswide, jt1.rapidresp, jt1.addrapresp, jt1.natemgrant, jt1.natgrannum, jt1.adulteduc, jt1.jobcorps, jt1.farmworker, jt1.natamerprg,;
jt1.vetprogram, jt1.tradeadj, jt1.naftataa, jt1.voceduc, jt1.vocrehab, jt1.wagpeyser, jt1.welf2work, jt1.csbgprog, jt1.hudprg, jt1.ucclaimant,;
jt1.othnonwia, jt1.titlevact, jt1.itaestabl, jt1.firstcore, jt1.firstinten, jt1.firsttrain, jt1.treadsklev, jt1.tmathsklev, jt1.termreason, jt1.recsuppsrv,;
jt1.recnrpstip, jt1.complwia, jt1.recfupserv,jt1.termdate,jt1.tkeyedby, jt1.tdatekeyed;
from jt1,jt2;
where (jt1.ssn=jt2.ssn and jt1.wia_area=jt2.wia_area and jt1.entertdte1=jt2.entertdte1);
into table partdata

Thanks,

 

[rgbean]

In FPx 2.x, commands are limited to 2048 characters. While I haven't counted them, your statement certainly looks like it may have hit that limit. Other than eliminating fields, reducing the table field name lengths or maybe using a one character alias for the tables, one thing you might want to consider is upgrading to VFP - commands there have a limit of 8,192.

Rick

 

[StacyStacy]

Thanks.

 

[DSummZZZ]

Or maybe convert to to a SCAN. Create an index on jt2, set a relation from jt1, scan and fill a new table.

Another option would be to use:

SELECT jt1.* from jt1,jt2;
where (jt1.ssn=jt2.ssn and jt1.wia_area=jt2.wia_area and jt1.entertdte1=jt2.entertdte1);
into table partdata

BROWSE FIELDS ssn, wia_area, entertdte1, lastname.....


Dave S.

 

[Gilesy]

Have you tried concatenating this? (excuse spelling if wrong!)

A_str = '<half of code>'
b_str = '<other half>'
c_str = a_str + b_str
&c_str

Think I've done this sucessfully before.


Happy birthday to me!

 

[StacyStacy]

Thanks all!
I did reduce the table field name lengths by using a one character alias for the tables. That kind of worked.

I had to stop writing this program and re-write the 'long way', which worked. I am running out of time. I pulled it into Chrystall reports whereby I choose my fields and identified my criteria.

Once I complete this report, I will revisit this issue. Keep your suggestions coming. )

Thanks all and happy birthday to Gilesy!! ))

 

[rgbean]

Gilesy,
FYI: Expanded macros are still limited by the 2,048 command length limitation.

Rick

 

[darrellblackhawk]

One way around it is the following;


* Create a list of field names - only 11 here
* Notice there are commas encapsulating
* all of the field names. This makes the
* translation to field names algorithm easier

lcFieldList = ;
&quot;,ssn,&quot; + ;
&quot;wia_area,&quot; + ;
&quot;entertdte1,&quot; + ;
&quot;lastname,&quot; + ;
&quot;firstname,&quot; + ;
&quot;initial,&quot; + ;
&quot;appdate,&quot; + ;
&quot;address,&quot; + ;
&quot;city,&quot; + ;
&quot;county,&quot; + ;
&quot;state,&quot;

* Array of field names
dime laFieldNames{occurs(&quot;,&quot;,lcFieldList)-1)

* Load the field names array
FOR i = 1 TO OCCURS(&quot;,&quot;,lcFieldList)-1
laFieldNames(i) = ;
SUBSTR(lcFieldList, AT(&quot;,&quot;,lcFieldList,i)+1, ;
AT(&quot;,&quot;,lcFieldList,i+1)-AT(&quot;,&quot;,lcFieldList,i)-1)
NEXT

* Save whatever the current workarea is
lnWkArea = select()

* Perform the SQL select statement and
* just return all fields from the source table

select * from jt1,jt2;
where (jt1.ssn=jt2.ssn and jt1.wia_area=jt2.wia_area ;
and jt1.entertdte1=jt2.entertdte1);
into table partdata

* The newly created table is already selected,
* so now let's enable only those field we're
* interested in

set field to
set fields on

for i = 1 to alen(laFieldNames)
set fields to &laFieldNames(i)
next

* And that's it!

Darrell



'We all must do the hard bits so when we get bit we know where to bite'

 

[Gilesy]

Hope I haven't given you guys duff info, but I have a line in some of my code where LEN(bf3) is 2250:

BROWSE FIELDS &bf3 FOR &criteria

This works fine. And I'm sure I've done the same for a select sometime in the past, but can't put my finger on it. I'll experiment later when I get a chance.

 

[StacyStacy]

Thanks!

 

[rgbean]

Gilesy,
What version of FPD or FPW are you using where this works? While I've run into the &quot;limit&quot; on more than one occassion, I'll admit I didn't bother to actually count the characters. I simply relied on the help file for an explanation.

There may be some exceptions, based on the actual command or the runtime evaluation. (Remember commands with macros aren't &quot;compiled&quot;, they are evaluated at runtime.)

Rick

 

[StacyStacy]

You know, writing the code the long way worked, but I must shorten JT1 & JT2. I am having trouble exporting it from Chrystal Reports into Excel. It's too many fields. So, I'm back ... please stay posted. Let me try some of your suggestions. thanks

 

[StacyStacy]

Help! This is still too long.

select A.ssn, A.wia_area, A.entertdte1,A.lastname, A.firstname, A.initial, A.appdate, A.address, A.city, A.county, A.state,;
A.zipcode, A.areacode, A.phone, A.age, A.birthday, A.sex, A.citizenshp, A.selectserv, A.education, A.highgrade, A.ethnic,;
A.indalaskan, A.asian, A.black, A.pacisle, A.white, A.hispanic, A.offender, A.limitedeng, A.homeless, A.runaway, A.teenparent,;
A.veteran, A.vietnamvet, A.disablevet, A.recsepvet, A.laborstat, A.grossfminc, A.numinfamly, A.hourlywage, A.wageatdisl, A.dislocdate,;
A.familystat, A.basiceduc, A.metroresid, A.ssi, A.afdc, A.refugeasst, A.fosterchld, A.foodstamp, A.inddisable, A.econdisadv,;
A.jobspartic, A.displahome, A.rcvdnrpay, A.titletype, A.needsbased, A.certifdate, A.ythaddasst, A.pellgrant, A.adult, A.dislworker,;
A.oldyouth, A.yngyouth, A.fromrresp, A.fromjtpa, A.inadult, A.locdworker, A.locyouth, A.stateyouth, A.statedhome, A.incumwork,;
A.otherswide, A.rapidresp, A.addrapresp, A.natemgrant, A.natgrannum, A.adulteduc, A.jobcorps, A.farmworker, A.natamerprg,;
A.vetprogram, A.tradeadj, A.naftataa, A.voceduc, A.vocrehab, A.wagpeyser, A.welf2work, A.csbgprog, A.hudprg, A.ucclaimant,;
A.othnonwia, A.titlevact, A.itaestabl, A.firstcore, A.firstinten, A.firsttrain, A.treadsklev, A.tmathsklev, A.termreason, A.recsuppsrv,;
A.recnrpstip, A.complwia,A.recfupserv,A.termdate,A.tkeyedby, A.tdatekeyed, A.lastchange, A.uireferral, B.enteractiv, B.enterproj, B.entertdate,;
B.enterjobti, B.exitactivi, B.exitprojec, B.exittdate, B.exitcomptr, B.exitjobtit;
from jt1 A,jt2;
where (A.ssn=B.ssn and A.wia_area=A.wia_area and A.entertdte1=B.entertdte1) and (jt1.dislworker=&quot;1&quot; and jt1.adult=&quot;1&quot;
into table JT1&2

 

[DSummZZZ]

Try this. Create a table structure containing the fields you need, in the order you want. Since I don't know your fields' types and sizes, I'll guess:

CREATE TABLE partdata;
   (ssn        C( 9), ;
    wia_area   C( 6), ;
    entertdte1 D( 8), ;
    lastname   C(25), ;
    firstname  C(20), ;
    initial    C( 1), ;
.
.  
.All the other fields here
.
   exittdate   D( 8), ;
   exitcomptr  C(10), ;
   exitjobtit  C( 6))

(Notice the first six correspond to jt1 and the last 3 correspond to jt2)

Then issue a:

SELECT jt1.*, ;
   jt2.enteractiv, jt2.enterproj, jt2.entertdate, ;
   jt2.enterjobti, jt2.exitactivi, jt2.exitprojec, ;
   jt2.exittdate, jt2.exitcomptr, jt2.exitjobtit ;
FROM jt1, jt2;
where (jt1.ssn=jt2.ssn and jt1.wia_area=jt2.wia_area ;
   and jt1.entertdte1=jt2.entertdte1);
into table interim

SELECT partdata
APPEND FORM interim

Dave S.

 

[marka1966]

RGBean and Gilesy,
I have over come the character limit using Macros many times. Most my queries are from 8 related tables with about 35 different fields to output. The

A_str = '<half of code>'
b_str = '<other half>'
c_str = a_str + b_str
&c_str

type solution has worked exceptionally well every time I have had to use it.

MarkA1966

 

[TheRambler]

I think DSummZZZ sugestion could solve the problem.

I would add that if the CREATE TABLE command gets too long, it could be changed to CREATE TABLE from ARRAY...

 

[DSummZZZ]

Good idea mlv1055.

Let me apologize for a typo in my prior post:
APPEND FORM interim

shoud be:
APPEND FROM interim


Dave S.

 

[StacyStacy]

Hey you guys, I know this is the FoxPro 2.6 Forum, but I need some help in Chrystal Reports 8.0. No one has responded to my question. So, here's a try: I need to adjust my page larger than 11x17 (Oversize) to fix 131 fields into the report. Can anyone help with that OR ... can anyone help with exporting the right from foxpro into Excel? Here's the catch: I have to write formulas to translate numerical data into words.

Examples:
SEX --> 1= Yes, 2=No
HIGHEST GRADE ---> 1=HighSchool 2= College 3=No Education

thanks

 

[rob444]

Sbook,

start a new thread with a new subject.

Rob.

 

[JRB-Bldr]

To sBook:

Since your new question (which does not belong in this thread) has nothing to do with Foxpro, you might try posting in the appropriate forum.

If you go to the Search box right below the banner and do a Find A Forum Search with the words &quot;Crystal Reports&quot; you will get a list of forums dealing with Crystal Reports.

Try there.

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com