I have 3 SQL tables that I'd loke to join. Table A has dept_id, dept_descr. Table B has dept_id, subdept_id, subdept_descr. Table C has subdept_id, sales_amt.

How can I join these 3 tables so that I can get the following result:

dept_id, dept_descr, subdept_id, subdept_descr, sales_amt

Thanks!

Joel

JOIN DEBPT_ID IN TABLEA TO ALL DEPT_ID IN TABLEB AS JAB
JOIN SUBDEPT_ID IN TABLEA TO ALL SUBDEPT_ID IN TABLEC AS JAC
TABLE FILE TABLEA
SUM SALES_AMT
BY DEPT_ID
BY DEPT_DESCR
BY SUBDEPT_ID
BY SUBDET_DESCR
END

Hi Joel,

I would create a view on sql server doing these joins and create a master file on it.


CREATE VIEW [NameOfTheView]
AS
Select A.dept_id, A.dept_descr, B.subdept_id,B.subdept_descr, C.sales_amt
inner join B On A.dept_id=B.dept_id
inner joiin B.subdept_id=C.subdept_id
GO

Joel,

I agree wholeheartedly with Majid Jeddi's solution.

This is a fine example of how it's always better to push the hard work back into the RDBMS it you have the ability to do so. Sometimes, it's not easy because of user's security access, knowledge, DBA availability, etc.

Effecting the JOIN in the RDBMS will also cause a marked reduction in the elapsed time to produce the report.

Chris Burtt

Why the need to create a SQL View. Isn't that why WebFocus has the Join Tool? This appears to be a rather straight forward request.

I understand creating SQL Views/Stored Procedures sometimes makes it easier - but then you have the overhead of maintaining these views (and the master file).

I have some complicated WebFocus Reports with multiple hold-files, dialog-manager, multiple joins... (Some of created by me, others by IBI Consultants). Looking back at some reports, I think a Stored Procedure would have been a better solution. (Hey the reports work - why mess with them - except maybe they could be faster as stored proc!)

One disadvantage of a stored procedure is that unless you know exactly all the input parameters required beforehand - you are bringing back more data to SQLOUT than you need actually need. Then you do a "Where" with WebFocus to filter SQLOUT. So sometimes the stored proc solution is slower.

Here is my question ---

What is the perfect balance of "pushing the work back to the RDBMS" vs using Web-Focus (Dev-Studio)? I always seem to be faced with the decision - do I create another SQL view/stored procedure or do I do the little extra work in WebFocus (such as create the join).

I would say it depends on the number of tables, the size of the files, if you are having to pull data across platforms and if the join from fields all reside in the host. In this case, you are joining from FIELD1 in TABLEA to FIELD1 in TABLEB and from FIELD2 in TABLEB to FIELD2 in TABLEC. Unless the tables are very small, I would use a view.

In my experience, I would suggest understanding what you are doing.

Are you using this view for other reports ?

There are benefits to using a Join in the fex as apposed to a join in the DB.

For example, if you reference the join in the DB and use columns from 2 of the 3 tables, an SQL DB will/may read all three tables, which adds up to overheads. Keep in mind that behind the scenes, a View is pretty much an SQL request.

What I have seen in the past is that different queries can have different efficiencies. A bit of testing can help.

The code we write here allways has a check of the time in between steps, so inefficient code can be spotted.

In fact with the Oracle DB we have (Pretty complex design), we have found that in many cases extracting each tables into flat files and then joining can be quicker that letting Oracle do the join.

Hope this helps

Waz - that was helpful!

I agree - you need to know all the facts first before you decide to create a view vs using WebFocus (Efficient/Responsive reports are important!)

I'm always being asked to create sql views/procedures with everything so the user can ad-hoc almost anything. So I end up with views that joins several tables and then they complain the report is slow - I can't win!.

So - I guess it boils down to this - it depends on what your doing!

If the join is done properly, as in the code posted by Jimbo, then the join will be done by the RDBMS even without creating a view.

Run trace code when developing to verify that the join is being passed to the relational engine.

SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 78
SET TRACEUSER = ON
SET XRETRIEVAL=OFF

Jammer, if you are creating many views in SQL, have you considered joins in the master. You will still get the benefits of a FOCUS join but you get the single view on the data.

I also agree with GinnyJakes, all of our code has SQL tracing on (set up in an environment prep include), a minor overhead for the output, but can be invaluable for diagnosis of issues and performance.

Waz,

Be sure to comment out the include before rolling the program to production. The trace creates a file in edatemp that must be removed manually.