Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Go
New
Search
Notify
Tools
Reply
  
Subqueries in focus
 Login/Join
 
Member
posted
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
 
Posts: 14 | Registered: January 11, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 


Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.