Focal Point
Subqueries in focus

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/2791092351

February 09, 2006, 05:55 AM
Anubha Saini
Subqueries in focus
can we write subqueries in focus. I don't want to execute sql but the report requires a subquery. How can we do it in webfocus
let say for e.g
i have query
select * from emp where sal=(select max(sal) from emp);
How can we get result of this query in webfocus. can anyone help me
February 09, 2006, 08:26 AM
mgrackin
The FOCUS language does not have subqueries like SQL. However, the following is a example of duplicating the same functionality with a HOLD file.

The first pass of the data creates a file containing the MAX value. the second pass then uses the file in a selection statement to select only records that have the MAX value.

TABLE FILE CAR
SUM MAX.RCOST
ON TABLE SAVE AS MAXVAL
END
-RUN
TABLE FILE CAR
PRINT RCOST DCOST
BY COUNTRY
BY CAR
BY MODEL
BY BODYTYPE
WHERE RCOST IN FILE MAXVAL
END


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
February 09, 2006, 08:29 AM
mgrackin
Here's another example of how to get the same result using a multiverb request.

TABLE FILE CAR
SUM MAX.RCOST
PRINT RCOST DCOST
BY COUNTRY
BY CAR
BY MODEL
BY BODYTYPE
WHERE TOTAL RCOST EQ MAX.RCOST
END


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011