467,116 Members | 1,165 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,116 developers. It's quick & easy.

Pass Throughs and Reports - Record Order?

Have worked for many years with the above both toegther and separately
(as in Jet and reports), I was always under the impression that the
order by clause of a query had no real bearing on the order of records
shown in a report based on the query. Sorting & grouping has to be
specified.

However, in A2003, I've been working on a large app for the past 6
months and have finally gotten around to creating and manuipulating
reports. I'm finding to my puzzlement that even without sorting and
grouping specified, the order of appearance of records on the reports
based on PTQs seems to be as one specifies in the PTQ.

IS this normal for PTQs? The Oracle SQL I'm generating is anywhere from
8 to 25 pages long (disgustingly monotonous union queries - it's a very
bad database design, though not my own) and it would really be lovely if
it was NOT necessary to parse through the various select clauses and
figure out what to plop into the sorting & grouping properties of a
report...

Thanks to anyone with any perspective on this.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Feb 24 '06 #1
  • viewed: 1532
Share:
2 Replies
On Fri, 24 Feb 2006 18:00:08 -0330, Tim Marshall
<TI****@PurplePandaChasers.Moertherium> wrote:

I have no specific knowledge about Oracle as a back-end, but as you
know, all recordsets are ordered. Even in the absence of an ORDER BY
clause, the trained eye can see an order. Often a PK.

I think it's still good practice to NOT specifically order your report
queries, and to use the Grouping and Sorting to do its thing.

I feel your pain about the db design. I once worked on a project where
the design was so bad I created a layer of queries that essentially
was the "correct" (IMHO) design. Then I would use those queries as the
base "tables" for my other queries. Once you are used to a normalized
design, it's hard to work with a messy one.
Have you noticed that most good db designers agree on the "correct" db
design? We may argue in the margins, but essentially there is only one
"correct" design. 3NF yeah!

-Tom.
Have worked for many years with the above both toegther and separately
(as in Jet and reports), I was always under the impression that the
order by clause of a query had no real bearing on the order of records
shown in a report based on the query. Sorting & grouping has to be
specified.

However, in A2003, I've been working on a large app for the past 6
months and have finally gotten around to creating and manuipulating
reports. I'm finding to my puzzlement that even without sorting and
grouping specified, the order of appearance of records on the reports
based on PTQs seems to be as one specifies in the PTQ.

IS this normal for PTQs? The Oracle SQL I'm generating is anywhere from
8 to 25 pages long (disgustingly monotonous union queries - it's a very
bad database design, though not my own) and it would really be lovely if
it was NOT necessary to parse through the various select clauses and
figure out what to plop into the sorting & grouping properties of a
report...

Thanks to anyone with any perspective on this.


Feb 25 '06 #2
Tom van Stiphout wrote:
I think it's still good practice to NOT specifically order your report
queries, and to use the Grouping and Sorting to do its thing.
I think you're right. Just to be sure, I'm dropiing any order by clause
for PTQs I give to reports. Still, it rather shocked me when I first
saw the reports giving stuff in the correct order.
I feel your pain about the db design. I once worked on a project where
the design was so bad I created a layer of queries that essentially
was the "correct" (IMHO) design.


Yup. 8) I was going to try to describe the horrific design of our
system, but I just droned on for too long. Suffice to say that that to
propely pull information out of this I need a union query with up to
four very large separate queries, with each of these having a union
query with multiple parts inside. It can balloon up to an select
statement that in 10 arial font occupies 24 pages in a Word document.
That's pretty big in my experience, and there's absolutely no way Jet
could handle it using ODBC linked tables (there's lots of subqueries,
anyway, which I've found Jet doesn't do too well with when I've got lots
of records) which is why I stick to PTQs...
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Mar 1 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by marwa mohamed | last post: by
4 posts views Thread by Rich Moser | last post: by
7 posts views Thread by Zlatko Matić | last post: by
1 post views Thread by Hexman | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.