Hi,
I have a sql query that looks like this,
select a1.f1, a1.f2, a1.f3,
(select count(*) from a2 where a2.f1 = a1.f1 and a2.f2 = 'something') as "employee have no of a2 stuff"
from a1;
Any idea? why something is so easy in pl/sql but so complex in webfocus? How to use nested subquery in codition clause of main query in webfocus?
thanks
AK

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

Hey Abu,
I believe you'll need to create a hold file to hold the results of your subquery on a2. Then join that hold file to a1.

Hi

With WebFOCUS 77 You have the option to add a subquery on the master file level.
You can add a "virtual" field which will retrieve a single record from the sql sub-query,
then you can add a WHERE statement on this field which will be transalated to sql subquery request performed in the database.
You can download a presentation explaining how to use it given in Summit 2011:
metadata enhancements

Yours

Eran

Maybe something like this:

SET ALL = PASS
-*
JOIN f1 IN a1 TO ALL f1 in a2 as J1
-*
TABLE FILE a1
 SUM
  FST.a1.f2
  FST.a1.f3
  CNT.a2.f1 AS 'employee have no of a2 stuff'
 BY a1.f1
 WHERE (a2.f2 EQ 'something');
END

You can also use the SQL Report Wizard. It allows you to insert sql directly into a Webfocus report. When you create a new procedure, in the window that pops up, change the "create with:" field to SQL Report Wizard.

@Dan -

Maybe not. We are not told whether (f1, f2, f3) covers the primary key of a1, so the answer set may well contain duplicate rows.

A PRINT verb and a "conditional" left outer join would be needed to cover that possibility; or a carefully crafted MATCH FILE.

Thanks everyone for sharing your knowledge. Actually, the task for me was to determine if an employee is an alumni or not in our university. I had to count no of degrees awarded by our university for an employee and if someone has 1 or more degrees then he/she is an alumni. It was very simple in Oracle, i.e pl/sql.
However, for webfocus, I had to select all records from degree table and save it in a hold file (a very tiny fraction from university degree table are also an employee in our university). Then I joined this degree table with the original report containing all the current university employees. Then count degrees for all employees and if the count is > 0 then he/she is an alumni else not an anlumni. The report is working fine. However, I don't like the idea of selecting all records from the degree table and save it in a hold file. In pl/sql it could have been just a single subquery selecting from the degree table using emplyee ID. Because the degree table is huge it contains all the degrees awarded by the university from its inception but our university has only couple of thousand employees. Anyway, the report is working, thats the bottomline.
Thanks
Abu

Could you not join the Employee table to the Degree table and then count the number of times an employee is on the degree table ?