The following code snippet worked in 7.6.8.
DEFINE FILE SYSCOLUM
-*------------------------------------------------------------------------------
-* look for first equal in deftext to remove left hand side
-*------------------------------------------------------------------------------
TXTFILTERLEN/I5 = POSIT(DEFTEXT || '~', 300, '~', 1, 'I5') - 1;
POSEQ/I3 = POSIT(DEFTEXT, TXTFILTERLEN, '=', 1, 'I3');
FILTERACTUAL/A3000 = SUBSTR(TXTFILTERLEN, DEFTEXT, POSEQ+1, TXTFILTERLEN, 300, FILTERACTUAL);
FILTERACTUALLEN/I5 = POSIT(FILTERACTUAL || ';', 3000, ';', 1 , 'I5');
-*------------------------------------------------------------------------------
-*String Table Name and Dot from Filter
-*------------------------------------------------------------------------------
TBNAMEDOT/A255 = TBNAME || '.';
TBNAMEDOTUP/A255 = UPCASE(255,TBNAMEDOT,'A255');
TBNAMELEN/I3 = POSIT(TBNAMEDOT, 255, '.', 1, 'I3');
TBLNAME/A50 = SUBSTR(TBNAMELEN-1, TBNAMEDOTUP, 1, TBNAMELEN-1,50,TBLNAME);
FLTNAMEDOT/A255 = NAME || '.';
FLTNAMELEN/I3 = POSIT(FLTNAMEDOT, 255, '.', 1, 'I3');
FLTNAME/A50 = SUBSTR(FLTNAMELEN-1, NAME, 1, FLTNAMELEN-1,50, FLTNAME);
FILTERDESC/A3000 = STRREP(FILTERACTUALLEN, FILTERACTUAL, TBNAMELEN, TBNAMEDOTUP, 0, 'X', 3000, FILTERDESC);
END
TABLE FILE SYSCOLUM
WHERE DEFINE EQ 'F';
BY FLTNAME BY TBLNAME
PRINT FILTERDESC
ON TABLE SET PAGE-NUM OFF
ON TABLE HOLD AS TEMPFILTER FORMAT SAME_DB
END
JOIN
SP_RPT_FLT_RELN_VW.SP_RPT_FLT_RELN_VW.FLT_NAME
AND SP_RPT_FLT_RELN_VW.SP_RPT_FLT_RELN_VW.TBL_NAME IN SP_RPT_FLT_RELN_VW
TO MULTIPLE TEMPFILTER.TEMPFILT.FLTNAME AND TEMPFILTER.TEMPFILT.TBLNAME
IN TEMPFILTER AS J0
END

Now in 7.6.9, I am receiving the 205 error when I attempt to create a join to the hold file tempfilter.

If I remocve the join and comment out the ON TABLE HOLD AS line, it works fine.
Any help would be appreciated.


Experience level: BEGINNER

This message has been edited. Last edited by: knegrotto,

Format SAME_DB ?

maybe try a -RUN after the END

quote:

TABLE FILE SYSCOLUM
WHERE DEFINE EQ 'F';
BY FLTNAME BY TBLNAME
PRINT FILTERDESC

PS Never seen a piece of code so backward!

quote:

ON TABLE HOLD AS TEMPFILTER FORMAT SAME_DB

Never seen anything like it. What kind of strange format is SAME_DB? Also Darin is right your code is backwards. In your own interest, please write your code like this:

 
TABLE FILE SYSCOLUM
PRINT FILTERDESC
BY FLTNAME
BY TBLNAME
WHERE DEF_FIELD EQ 'F'
ON TABLE...
END 

This will it more readable for yourself and for others.

SAME_DB is a format that allows you to write temporary tables in the database type of the master. If you have to massage a couple of tables to do a more efficient join, for example, you can do the whole thing in DB2, Oracle, etc. instead of bringing all the data back to WebFOCUS.

With that said, it doesn't make sense in this context. SYSCOLUMN is a system master.

Perhaps there is another error right before the FOC205 error, relating to the ON TABLE HOLD - maybe your User ID now does not have access privileges to create the temporary DBMS table.

Well At leats my "Backwards" code provided some entertainment value LOL

What finally worked for me was to to extract the data I wanted from SYSCOLUM and my oracle table and save both as FOCUS hold files. I created a single value index on each which In then used to join them.

Here is the code snippet if you are up to viewing any more of my beginners code LOL

And I would absolutely LOVE to get advice on how any of this could be done more simply.

DEFINE FILE SP_RPT_FLT_RELN_VW
FILTERKEY/A200 = FLT_NAME || '.' || TBL_NAME
END
-*------------------------------------------------------------------------------
-*INCDEFFLT* Print Default Filter data and save as HOLD file
-*------------------------------------------------------------------------------
TABLE FILE SP_RPT_FLT_RELN_VW
PRINT FILTERKEY RPT_ID FLT_NOT_OPT
BY FLT_NAME BY TBL_NAME
WHERE RPT_ID EQ '&REPORT_ID'
AND DFLT_FLAG EQ 'Y';
ON TABLE HOLD AS TEMPDEFAULTS FORMAT FOCUS INDEX FILTERKEY
END
-RUN
-*------------------------------------------------------------------------------
-*INCDEFFLT* Get Filter Description from SYSCOLUM
-*------------------------------------------------------------------------------
DEFINE FILE SYSCOLUM
-*------------------------------------------------------------------------------
-*INCDEFFLT* look for first equal in deftext to remove left hand side
-*------------------------------------------------------------------------------
TXTFILTERLEN/I5 = POSIT(DEFTEXT || '~', 300, '~', 1, 'I5') - 1;
POSEQ/I3 = POSIT(DEFTEXT, TXTFILTERLEN, '=', 1, 'I3');
FILTERACTUAL/A3000 = SUBSTR(TXTFILTERLEN, DEFTEXT, POSEQ+1, TXTFILTERLEN, 300, FILTERACTUAL);
FILTERACTUALLEN/I5 = POSIT(FILTERACTUAL || ';', 3000, ';', 1 , 'I5');
-*------------------------------------------------------------------------------
-*INCDEFFLT* Remove Table Name and Dot from Filter
-*------------------------------------------------------------------------------
TBNAMEDOTUP/A255 = UPCASE(255,TBNAME || '.','A255');
TBNAMELEN/I3 = POSIT(TBNAMEDOT, 255, '.', 1, 'I3');
TBLNAME/A50 = SUBSTR(TBNAMELEN-1, TBNAMEDOTUP, 1, TBNAMELEN-1,50,TBLNAME);
FILTERDESC/A3000 = STRREP(FILTERACTUALLEN, FILTERACTUAL, TBNAMELEN, TBNAMEDOTUP, 0, 'X', 3000, FILTERDESC);
FILTERKEY/A200 = NAME || '.' || TBLNAME
END
-*------------------------------------------------------------------------------
-*INCDEFFLT* Save Filter Description as Hold File
-*------------------------------------------------------------------------------
TABLE FILE SYSCOLUM
BY FILTERKEY
WHERE DEFINE EQ 'F'
PRINT FILTERKEY FILTERDESC
ON TABLE HOLD AS TEMPDESC FORMAT FOCUS INDEX FILTERKEY
END
-RUN
-*------------------------------------------------------------------------------
-*INCDEFFLT* Join Hold Files and Display Filter Information
-*------------------------------------------------------------------------------
JOIN TEMPDEFAULTS.FILTERKEY IN TEMPDEFAULTS TO MULTIPLE TEMPDESC.FILTERKEY IN TEMPDESC
END
TABLE FILE TEMPDEFAULTS
PRINT
FLT_NOT_OPT AS ''
FLT_NAME AS 'Filter Name'
FLT_NOT_OPT AS ''
FILTERDESC AS 'Criteria'

quote:

ON TABLE HOLD AS TEMPDEFAULTS FORMAT FOCUS INDEX FILTERKEY

If it makes you feel any better the woman I replaced wrote her code the same way you did with her IF/WHERE first and then her BY statements. In 20 years she never did a multi-verb request. I'm not sure it would work. Also, since I am in the habit of top down, with my SUM/PRINT first and BY and WHERE following it is sometimes difficult to maintain her code. 'nuff said.

You are indexing on FILTERKEY and it is not a sort field. Try making it your first by so that your file is sorted in that order.