Re: Oracle reports: linked queries

From: Paul Dorsey <pdorsey.dulcian_at_worldnet.att.net>
Date: 10 Dec 1998 02:29:39 GMT
Message-ID: <74nbmj$ha9_at_bgtnsc02.worldnet.att.net>

Ahhhhhh!!! No! NO! NO!

Multiple queries mean that you are doing the moral equivelent of a correlated where the correlation happens over the network. I have written hundreds of reports (many of which were very complex). Since I figured out the tool I have written less than a handfull of reports that used more than 2 or 3 queries.

Probably, over 90% of reports should have 1 query. Learning SQL is a really good idea.

Yes, there are times that you need to use the linked query option, but it should be used about as often as you need to do a manual anchor.

---

As to your first question. You can nicely change sorts at runtime using lexical parameters.

--
Paul Dorsey
Dulcian, Inc.
www.dulcian.com
212 595 7223
Robin Quasebarth wrote in message
<366E504D.15E5D887_at_data-pointnospam.com>...


>By using independent queries and joining them you gain the ability to have


>each group/query behave differently on the layout.  Such as.... if you want

>the first group to repeat 'down' and the next group to repeat 'across and

>then down.'

>

>Did you include your 'order by' in you query?  Remember that your break

>groups effect your sort order also.  Also, having independent queries can

>resolve the need to have outer joins on table that are 'outer joined'

(which


>causes an error)  in the one query.  Each query group will have its own


>order.  If an outer query needs to order according to columns in the first

>query you should include a reference to that table in the second query even

>though you may not redisplay those columns on your layout.

>

>When I created a transcript for a university I used 43 queries that

>reference 90 tables or view and the whole thing fit on one page and was

>quite amazing to have each area such as 'Coursework Completed', 'Coursework

>in Progress', 'Attributes for Coursework Completed', 'Computer Competency

>Requirements Status' ,'Langueage Requirement Status', etc, each have their

>own behavior on the layout. It ran very fast, also.  rq -- to resond by

>e-mail remove nospam from e-mail address.

>

>Scot Balfour wrote:

>

>> My team is trying to understand the differences between writing a single

>> query that joins several tables versus and writing one query per table

>> and linking them together.

>>

>> It seems like when you link the independent queries: A and B, you can't

>> select the order of the fields that appear in the default layout. We

>> wanted a report that included fields: A1, A2, B3, A3, B4 but the default

>> layout always took all of A's fields first, then all of Bs'.  We could

>> omit fields, but not resequence.

>>

>> We concluded that if you needed to include fields from multiple tables

>> and you wanted to order the default layout by alternating between fields

>> from table A with table B, you needed to write a single query with a

>> join, instead of linking independent queries.

>>

>> Can anyone validate this conclusion or better explain their

>> success/rationale behind linked queries?  Thanks.

>

Received on Thu Dec 10 1998 - 03:29:39 CET