TABLE FILE B_SQL_COMPANY
PRINT
COMPANYID
ON TABLE HOLD AS HLD1
END


TABLE FILE B_SQL_PROJECTS
PRINT *
WHERE COMPANYID IN (HLD1)END
-EXIT

is there any way to write the inner query in webfocus ??

tks/sam

Sam

What you did is almost the way to do this, there are however some limitations in the size of the hold file.

Change your line "ON TABLE HOLD AS HLD1" to
"ON TABLE SAVE AS HLD1" or "ON TABLE HOLD AS HLD1 FORMAT ALPHA"
And after that first END ad the line -RUN.

In your first TABLE you should add some WHERE statements, otherways a inner join would be a better solution.

If both database tables are in the same format (SQL) you also can write your selection on the server within SQL and do the reporting on that view.

Thanks...

don't have scope for writing sqls...

Your code is a good way to do this subject to limitations Frank mentions. As for using an inner join, let me first clarify your question. You want records from PROJECTS where company id is in COMPANY, right?

The following would do it:

SET ALL=OFF (This is the default, but doesn't hurt to add it)
JOIN CLEAR *
JOIN COMPANYID IN B_SQL_PROJECTS TO COMPANYID IN B_SQL_COMPANY AS J1

TABLE FILE B_SQL_PROJECTS
PRINT
B_SQL_PROJECTS.COMPANYID
B_SQL_PROJECTS.projectid
B_SQL_PROJECTS.fieldn
B_SQL_COMPANY.COMPANYID NOPRINT
END

The reference to the company id field in COMPANY activates the join. The join with set all=on means only display records where all segments are satisfied (i.e. you actually found a matching companyid record in the cross-referenced file). Thus you only get records from PROJECTS where the companyid also exists in COMPANY.

The actual syntax for the join will differ somewhat if you're using a recent version of the GUI tool. Something more like

JOIN
INNER B_SQL_PROJECTS.COMPANYID IN B_SQL_PROJECTS TO UNIQUE B_SQL_COMPANY.COMPANYID IN B_SQL_COMPANY AS J0
END

subsequent correction of syntax per Ginny's later post

This message has been edited. Last edited by: Darin Lee,

quote:

SET ALL=ON (This is the default, but doesn't hurt to add it)

Just to clarify, OFF is the default for SET ALL.

my mistake - Ginny is correct. OFF is the default and SET ALL=OFF is what you want to use.