Hi, I have one main cursor in plsql. Now for each row of that cursor, I have to select the ID field for employee for that row and use that ID to execute four select statements under the main for loop for that cursor to set different variables. How do I do this in Webfocus? I dont see loops or host variables supported in Webfoucs like C/java.
I cannot join the other 4 selects to main cursor because fields from those 4 selects are optional and might not contain values. Also I only need to select values related to ID of main cursor not the whole table and fileter afterwards.
Any idea how to do this in Webfoucs?
Thanks
Abu

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

Abu, what you're trying to do is indeed a JOIN. You should be joining your EMPLOYEE table (or whatever object your current cursor is based on) and join it to the other 3 or 4 data sources to have access to the related data.

quote:

I cannot join the other 4 selects to main cursor because fields from those 4 selects are optional and might not contain values

That's what OUTER JOINS are for.

If all you need is a list of the distinct employee ID that matched the criteria, you can query your database with the JOIN structure on, HOLD your results and then get the DST.EMPLOYEE_ID from your HOLD file.

In fact, that's the way you should try doing it in PL/SQL as well. Databases were designed to work efficiently on sets of data and when one forces it to do a row-by-row approach (your cursor for instance) a lot of overhead is created most times unnecessarily. There are obviously situations where a row-by-row processing is needed (otherwise the database vendor would not have implemented the functionality) but this case *could be* one of those doable via JOIN/sets, but only you can make that call as you know your data and business rules.

Finally, WebFOCUS cannot be compared to C or Java because it is not a procedural language but a 4GL one intended more for processing of batches of data. The "loop" notion you have corresponds actually to the internal "fetching/processing" the FOCUS engine does when going over the data and doing stuff such as producing a report or creating temporary files for further processing.

So, try something like:

[CODE]
SET ALL=ON
JOIN EMPLOYEE_ID IN EMPLOYEE TO MULTIPLE EMPLOYEE_ID IN TABLE1 AS J0
JOIN EMPLOYEE_ID IN EMPLOYEE TO MULTIPLE EMPLOYEE_ID IN TABLE2 AS J1
JOIN EMPLOYEE_ID IN EMPLOYEE TO MULTIPLE EMPLOYEE_ID IN TABLE3 AS J2

TABLE FILE EMPLOYEE
PRINT
EMPLOYEE.EMPLOYEE_ID
EMPLOYEE.FIELD1
EMPLOYEE.FIELD2 ....
TABLE1.FIELDS ...
TABLE2.FIELDS ...
TABLE3.FIELDS ...
WHERE
-* If you need to filter on TABLE1, TABLE2, etc. you'll need to
-* check documentation on how to screen out records out of a short path
ON TABLE HOLD AS HRESULTS
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
END

-* Get the list of employee IDs that you're interested in
TABLE FILE HRESULTS
PRINT DST.EMPLOYEE_ID
WHERE -* originally from TABLE1, TABL2E2, etc.
END
[code]


I hope that will help you get some ideas.

---

Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.

Thanks njsden, the approach you mentioned works although it is very inefficient. Actually, I had to join to about 7 or more tables, (employees email, phone, college, high school, major etc). I had to select all records from those optional tables whereas I needed only a few records corresponding to a particular employee. This is I should say grossly wrong, and a very bad design. If I could use only PL/SQL then I should have used the main cursor, and inside the main for loop, only selected records corresponding to that particular employee and saved in some local variables and print when all tables are handled.
However, now I have to select all data from those 7/8 optional tables and then match it with the employees from main select statement.
Anyways, it is working (although very slow).

Abu, I think the inefficiencies lie in the database design and/or the way the problem is being dealt with.

quote:

now I have to select all data from those 7/8 optional tables and then match it with the employees from main select statement.

quote:

I had to select all records from those optional tables whereas I needed only a few records corresponding to a particular employee.

quote:

This is I should say grossly wrong, and a very bad design

I totally agree with the last statement. Why are you selecting ALL data from your tables to match it back to the main select? That is not how JOINs work and that should not be happening at all. That seems more like an procedural implementation of a join which is not necessarily how the database would handle it. When a database resolves a JOIN (a to b to c), the optimizer builds an execution plan in such a way that only the matching records are retrieved, not *all* as your seeing in your environment, unless of course one decides to "implement" a join procedurally instead of letting the database handling it directly.

If you only need info of a particular employee, then:

select e.stuff, em.stuff, ep.stuff, ec.stuff, eh.stuff 
 from emp e
left outer join emp_email em on (e.emp_id = em.emp_id)
left outer join emp_phone ep on (e.emp_id = ep.emp_id)
left outer join emp_college ec on (e.emp_id = ec.emp_id)
left outer join emp_highschool eh on (e.emp_id = eh.emp_id)
where e.em_id = :employee_id;

Should be giving you only the information you need for that one employee. That's what the WHERE filter would do. The database would not retrieve ALL records, only the ones it needs. Build such a statement directly in your database (I assume Oracle), check the execution plan and start reviewing what the optimizer is doing (or not doing) to improve performance. for instance, are there indices on emp_email.emp_id, emp_phone.emp_id, etc.?

That should be giving you very quick results in one single statement, no cursor, no loop.

Now back to WebFOCUS. If the join above satisfies your needs, now you have to make sure that the JOIn you have in WebFOCUS is actually being properly translated and passed to Oracle. If the behaviour is not the same then something is not being done properly from the WebFOCUS side and the database is not doing the join but it's WebFOCUS instead trying to do it itself (not necessarily what you or I'd want).

It is very hard to give advice when one does not know the business problem you are trying to resolve. I am just giving my opinion about your comparison about your PL/SQL processing and how do it in WebFOCUS.

Actual details about your requirement are needed. For instance, why are you joining to 7/8 tables if you don't need the information in them at all? You mention setting some variables in PL/SQL, but what is the purpose of those variables now that you are working with WebFOCUS? are those calculated fields perhaps?

Better yet Abu, why don't you enable traces in WebFOCUS so you can have a glance at the actual SQL statement that WebFOCUS is sending to the database for resolve? that'll tell you for sure if the database is handling the join and filters or if WebFOCUS resorted to do it itself because of "something" else. The traces will help you spot that. You can then capture that SQL, take it to your favourite Oracle SQL client, ask for an execution plan and review what could be improved.

Again, without knowing your data, your table design and more importantly your business needs it becomes almost impossible to provide any advice suitable to your particular case, only generic stuff so I hope you'll be able to find some useful piece among all this ranting

Thanks again njsden. The multiple left outer join is exactly what I tried in Oracle (Oracle is our database server) after I knew that I cannot use cursor and local variables in each iteration of loop. However, when I do multiple left outer joins, oracle gives me error saying that only one left outer join is allowed.
The reason, I cannot inner join all of these tables at once is that some of the employees might not have college degrees and they are eliminated from the main select as they dont have any college record. So, I want to have a main select statement to select all employees, then use the IDs from here to select college, high school, email, phone,address etc which are optional.
Right now, the way I have implemented in Webfocus is, I have a main select which selects a group of employees that are hired last 21 days and hold it to hold01 file, then I have hold files from each of these optional tables that select records based on other conditions (last college degree, last high school attended, most recent address, most recent active phone, etc). These other hold files contain all the employees which I dont want. Once I have all other hold files I then join it with main select which has employees who joined in last 21 days. Because I am selecting all employee records from optional tables it slows down a lot. If Oracle allowed multiple left outer joins in same select statement then it would do the job also. Sorry for this long mail.

quote:

oracle gives me error saying that only one left outer join is allowed.

quote:

If Oracle allowed multiple left outer joins in same select statement then it would do the job also

Abu, could you post the actual Oracle error you're getting and your original SQL? just change the table and field names if confidentiality is an issue.

One of us here could probably give some extra ideas as to how to re-write the query to make it work in Oracle.

I understand that given that obstacle, you resorted to read the contents of all those tables in WF and HOLD them in separate files to later join them and produce the report, and you're absolutely right in the fact that this is slowing down the whole thing and it's better to push things to Oracle.

Just post the SQL and I'll see what I can do.

In addition, would you please update your signature to mention which version/release of Oracle you're using? (Standard/Enterprise 10g/11g, etc.).

That will help us help you better

1) main select
SELECT ID,LAST_NAME,FIRST_NAME, TITLE,...
FROM JOBS,EMPL,ORGN,...
WHERE ...(emps joined company in last 21 days)
on table hold as hold01
2)SELECT ID,EMAIL_ADDRESS
FROM EMAL Where ... hold02
3)select ID, phoneno from phone where ... hold03
4) other similar selects totaling 8 hold files.
5) then hold01 left outer joined with othe 7 hold files to get the result in Webfocus.

Can I refer to the IDs in 1st select statement when creating the other hold files, hold02, hold03.. hold08? That way, I dont have to select all the records from those optional tables.

I have to re-write the sql code to reproduce the multiple left outer joins error in single select statement. I will do that as soon as I can. Many of our staff are now on vacation, and it has been busy, so give me some time to send you the error. We have Oracle 11g Enterprise edition with release 11.2.0.2.0.
Thanks
Abu

Why create 8 hold files and then join them? Can't you join your database tables together and retrieve all the information at one time?

@Francis, I cannot do this one time because I cannot do inner join. Oracle does not support multiple left outer joins in same select statement. If I do inner join, I loose employees who do not have college info or high school info or email or others.

Abu, you did not really answered my question. I need to see the SQL statement you created originally in Oracle (forget WebFOCUS for now), the one that has all of the outer joins in it and that failed with an error. I also wanted to see the actual ORA-xxxx error you mentioned.

The fact is, I've written countless SQL statements and had come with weird combinations of multiple inner/outer joins and they do work.


@Francis, that's what I suggested Abu since my first response last week but apparently he or she encountered an error where Oracle was not allowing multiple outer joins together.

Abu, skip the SQL passthru you're doing and let WebFOCUS handle the SQL translation.

If you look at the first code layout I provided you with you'll see that I suggest that you create JOIN structures from EMPLOYEE to each of your tables. The SET ALL=ON will automatically turn them all into outer joins. The final TABLE FILE request will get everything you need.

There must be something in the SQL you wrote yourself to the database that is not allowing outer joins go through but believe, Oracle can handle that and much more

I get this error when I outer join multiple tables,
Error
ORA-01417: a table may be outer joined to at most one other table.
the code below,
select t1.sorpcol_pidm,
t1.sorpcol_sbgi_code,
t2.stvsbgi_desc,
t1.sorpcol_trans_recv_date,
t3.sordegr_degc_code,
t4.stvdegc_desc,
t3.sordegr_degc_date,
t5.sormajr_majr_code_major,
t6.stvmajr_desc
from sorpcol t1, stvsbgi t2, sordegr t3, stvdegc t4, sormajr t5, stvmajr t6
where t1.sorpcol_pidm = 'a pidm'
and t1.sorpcol_sbgi_code = t2.stvsbgi_code
and t1.sorpcol_pidm = t3.sordegr_pidm
and t3.sordegr_degc_code = t4.stvdegc_code
and t5.sormajr_pidm(+) = t1.sorpcol_pidm
and t5.sormajr_majr_code_major(+) = t6.stvmajr_code;

Hi Abu,

You might want to consider rewriting your query with ANSI join syntax, rather then with the 'older style' Oracle ('+') syntax.

With ANSI join syntax you'd increase your chances of someone being able to spot something for you, given that this isn't an Oracle forum. Also, with switching to ANSI join, it might be easier for you to find the issue yourself.

Dave

I second David's suggestion. Support for ANSI JOIN syntax was introduced in Oracle 9i back in the early 2000's and has become my personal preferred method of implementing joins.

If you take a look at my SQL example posted before, you'll see a sequence of SELECT ... FROM LEFT OUTER JOIN ... LEFT OUTER JOIN .... that was also suggested which actually allows outer joining to multiple tables.

Abu, please don't take me wrong but when things are suggested here we expect somehow that the interested party will at least try them out and then decide if that helps or not. I can see that you considered the initial suggested approach as "very inefficient" and "grossly wrong and a bad design". But seeing that apparently you did not try either of the suggested methods I posted to implementing this I don't clearly see what your argument was to disqualify the proposed methods ...

Anyway, if you insist in doing it with SQL passthru and the "classic" Oracle (+) method instead of "pure" WebFOCUS syntax or the ANSI one, you could perhaps try the following which I'm confident will bypass the Oracle error while still giving you the same results:

select t1.sorpcol_pidm,
t1.sorpcol_sbgi_code,
t2.stvsbgi_desc,
t1.sorpcol_trans_recv_date,
t3.sordegr_degc_code,
t4.stvdegc_desc,
t3.sordegr_degc_date,
t5.sormajr_majr_code_major,
t5.stvmajr_desc
from sorpcol t1, stvsbgi t2, sordegr t3, stvdegc t4, 
     (
        select sormajr.sormajr_pidm, sormajr.sormajr_majr_code_major,
               stvmajr.stvmajr_desc 
          from sormajr, stvmajr
         where sormajr.sormajr_majr_code_major(+) = stvmajr.stvmajr_code
     ) t5
where t1.sorpcol_pidm = 'a pidm'
  and t1.sorpcol_sbgi_code = t2.stvsbgi_code
  and t1.sorpcol_pidm = t3.sordegr_pidm
  and t3.sordegr_degc_code = t4.stvdegc_code
  and t5.sormajr_pidm(+) = t1.sorpcol_pidm;

I would really, really advise that you tried doing it the WebFOCUS way though as it may be easier to maintain in the future, but it is obviously up to you to decide which way you'll go. I hope you'll find the solution you need.

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

Hey njsden, I really appreciate your help. Regarding the 1st approach, the problem is, its not one table (not only emp table), thats why I cannot just left outer join. The main select involves about 7 tables. Then each of the other select also involves multiple tables(e.g for college info, it has college code in one table, college degree in one table, trascript info another table). Same applies for high school, telephone, email etc.
I really implemented the way you suggested. I have the main select saved in one hold file and other select in other hold files and then left outer joined the main hold file to other hold files to get the final report. If I could somehow refer to the main hold file's fields when selecting for other hold files, this performance problem would not have happened and I would not have to select all the data from optional tables and then match with the main select.

quote:

I really implemented the way you suggested. I have the main select saved in one hold file and other select in other hold files and then left outer joined the main hold file to other hold files to get the final report.

Sorry Abu but I never suggested that you broke your data retrieval in 2 or more pieces to then put them back together in WebFOCUS. That seems like an odd attempt to replicate some of what you did originally in PL/SQL but that is not as effective in the WebFOCUS ground. The main HOLD file would be "okay" as you'd know which records to go after in your WHERE statement but for the other HOLD files you would need to pretty much read your whole tables and that is of course a performance killer.

My suggestion has always been to try and do all the joins at once, no matter how many outer joins there are or at what level. By using a combination of inline views, WITH clause and ANSI syntax I don't see why this wouldn't be achievable in one step at the Oracle level. I already provided an example that uses an inline view to issue one outer join and then use that result to outer join to the "main" select stream but I don't know if you tried that.

As I don't have access to your table design I cannot really help with the SQL statement itself and I'm afraid I don't know how else I could help or get the message through. Sorry

Actually that oracle error confused me. I thought that I can only do one left outer join at a time in Oracle. So, I broke it into pieces and then joined the hold files. Here is the problem that I have,

1)select A1.ID, A1.fname, A1.lname, A2.job_title, A3.join_date
from A1, A2, A3 where ...

2)select B1.ID, B1.collegeCode, B2.CollegeDesc, B3.transcript_date, B3.Grad_date
from B1, B2, B3 where ...B1.collegecode = B2.collegecode and B1.ID=B3.ID

3)select C1.ID, C1.HighSchool_code, C2.HighSchool_Name, C3.transcript_date, C3.Grad_date where .. C1.HighSchool_code=C2.Highschool_code and C1.ID = C3.ID and ...

Now, in the above example, I have to report all of the employees from 1st select, they might have no college record, 1 college record or multiple college record, they might have no high school record, 1 high school record or multiple high school records.
How do write one select using the 3 selects that I mentioned above?

I have to report all of the employees from 1st select

Abu, just one extra question for you. Are you displaying information only from the first set of tables (A1, A2, etc.)? or do you also need to display info from B* and C*?

You said that you need all of the employees from the 1st select so why join to the other tables?

Anyway, you can try something like this:

with 
set1 as
(
   select A1.ID, A1.fname, A1.lname, A2.job_title, A3.join_date
     from A1, A2, A3 
    where ...
),
set2 as
(
   select B1.ID, B1.collegeCode, B2.CollegeDesc, B3.transcript_date, B3.Grad_date
     from B1, B2, B3 where ...B1.collegecode = B2.collegecode and B1.ID=B3.ID
),
set3 as
(
   select C1.ID, C1.HighSchool_code, C2.HighSchool_Name, C3.transcript_date, C3.Grad_date 
     from C1, C2, C3
    where .. C1.HighSchool_code=C2.Highschool_code and C1.ID = C3.ID and ...
)
select set1.*, set2.*, set3.*
  from set1
       left outer join set2 on (set1.college_code = set2.college_code)
       left outer join set3 on (set1.highschool_code = set3.highschool_code)
 where <date and other filters here>

Once again, if all you need to display is information from your A* tables, then avoid joining to the other ones altogether.