Hi David,
> From what you are saying you want to be able to
> 1. Order this "table" (view) of data
> 2. Group By specific fields
> Even in Oracle, the rownum is not something that one should base
> joins on, because it can be as dynamic as the data.
Oh. In views I suppose it would be. In regular tables it is not
though right? My identity columns in sql-server in four tables are
the fields I use to join them together.
> In the "convert" example below, it does give you something that is
> based on the data and would be unique.
But I need a value to be sequential, based on the ordering of the
records at that given time the view is invoked (as the select query
which creates it puts them in the correct order).
> I feel that your concerns can be address directly with SQL without
> making a psuedo-column or a temp table.
Maybe you're right. The SQL here and there was frying my brain. I
wanted a vastly easier way to do about 1001 reports I need to do, and
my whole life would be SO much nicer if only this data were in one
table, in order, with a sequential field attached. :-) But then, of
course, the data would not be so modular as four one-to-many
relationships are now.... so I thought a view would be the solution.
> How do you want to join this view to other data? Perhaps we can
> approach this from an alternate perspective.
Thanks for considering it.
It's tough to do complex stuff in email without taking way too much
of people's helpful time with a novel, yet unless they get the
picture they can't have context for serious code help besides 'how do
I do one little thing?'.
I'll try and lay it out clearly if I can. I'm not sure if I should
send this personally, but then, I guess the delete button works...
others might learn something from this discussion.
There are tons of reports I need to do, it'll vary. But I'll recount
the first and main need (one display version) and the logic:
===================
[these are partial need-to-know outlines of the tables]
table tunit stores unit(chapter) info for a textbook.
unitid(identity),
unitnum(eg 'B-11'),
unitname (eg 'Sampling Distributions'),
courseseq (order in course).
table tsection stores sections(of ea chapter).
sectid(identity),
sectname(e.g., 'Basics' or 'Self-Assess'),
unitseq (int)(sequence within the unit).
JOIN: tunit.unitid (one) = tsection.unitid (many).
table tpage stores pages(of ea section).
pageid(identity),
pagenav (eg 'Uses 2'),
pagetitle (eg 'Uses 2 - Evaluating Research Questions')
sectseq (int)(sequence within the section)
JOIN: section.sectid (one) = tpage.sectid (many)
Here shows unit, some sections and some pages in one section:
Unit B-11: Sampling Distributions
Think First
Basics
Basics 1 - Definition of a Sampling Distribution
Practice Material for Basics 1
Basics 2- Approximating a Sample Distribution
Practice Material for Basics 1
Basics 3 - Characteristics of a Sampling Distribution
Practice Material for Basics 1
Uses (same multi-page format as basics)
Warnings (etc.)
(many other sections)
table telement stores 'elements' -- items in each page. An element
is a "piece of page": an essay question (SAQ1), a multiple choice
question (MCQ1), a heading (HDG1), a text/code element (TXT1), a java
applet (JAVA), a specially formatted 'example' surround by certain
graphics (EXPL), more. The elements I need here are only the Q&A
elements though.
elemid(identity), big table -- all product content for the most part -
- fields that relate in QA elements are:
qnum,
question,
answer.
pageseq (int)(sequence within page).
JOIN: tpage.pageid (one) = telement.pageid (many)
The combined tables (this IS "the textbook content") are sequenced
first by unit, then by section, then by page, then by element. The
sequencing is done via the sequencing column found in each table.
It's more complex than what I'd design on my own, but the client had
an existing book/site and said, "It's gotta look JUST LIKE THIS."
What is convenient for databasing what not the priority, display and
intermixture of elements so it'd be all one system was. Of course, it
had to look like his print but it also had to work like a dynamic
website where Q&A are submitted -- and where the content can be moved
all over the place by editors as they wanted to be able to do as a
big requirement, so they wouldn't need so much webmastering for
making changes not just in content but also in organization and
structure of the content. So, this was the means I came up with for
making those criteria happen. It's a pain in the butt in some ways
but *in the way the client needs to use it* it works very well.
So the four tables join on their ID fields, and order by their
sequence fields. I have to do:
ORDER BY
tsection.unitseq, tpage.sectseq, telement.pageseq
to get the final order I need for the Q&A elements for even one unit
to display in a report in the proper order.
First join: I now need to associate with all this, the multiple
choice options affiliated with each MCQ1 (multiple choice question).
Those are in a table called MCQ. Depending on the report I may need
only the 'correct' option or all of them (being forced to add the
correct=yes criteria to my where wipes out my query, removing all non-
mcq questions (my SAQ rows vanish), even when I do outer or full
outer joins with element+mcq, which oughtta work, but what do I
know). This table has OptionID(identity), and related stuff like the
option text, and is it a correct option (realans).
JOIN: telement.elemid (one) = mcq.elemid (many)
But wait... if it were THAT easy.... :-) There's more to it.
Once the whole unit-section-page-element-mcqOptions is ready -- which
is really the ideal-case 'base' that I wanted in the one view --
Then I need to associate that with the answers table students submit
to, called tuserans. I duplicated some values in this table from
others, unitid,sectid,pageid,elemid even though only elemid is
critical at the join level. The table also has:
course# for the student (currid) (primary value whole site uses)
user# for the student (uid) (second primary value for site)
student's answer if it's an essay question (saans)
option# student chose as answer in mcq's if it's that (mcans)
option# for the one that is the accurate choice (mcreal)
calc'd-on-input: is this answer correct? (mcacc)
JOIN: telement.elemid (one) = tuserans.elemid (many)
After that, I need to associate the user table, which has
UID(identity), lname, fname, email and some other misc. roster-
related fields.
JOIN: tusers.uid (one) = tuserans.uid (many)
I'm trying to make a view-with-sequential-column value because:
Once I get all that together in one SQL select query, FIRST it all
has to be ordered by the actual textbook sequence, so whether the
report spans a unit, section or page, the Q&A are ordered correctly.
Then it has to be GROUPED by *item in sequence*, or perhaps the UID,
depending on the report need:
(a) show me each question and then all the student answers for that
question, OR,
(b) show me each student and all the questions/answers for them. Q&A
In proper sequence of course.
But the Q&A elements don't get to proper unit-sect-page sequence
unless I do all three of those table-sequence columns, together in
order.
Alas, if I am busy doing ORDER BY
tsection.unitseq asc, tpage.sectseq asc, telement.pageseq asc,
I cannot then GROUP by UID or ElemID because I have to use the order
by columns, in the order they are in the query, in order to do my cf
group output.
It's a damnable dilemma!
If I had a new sequential column in my nicely-sequenced view, here is
what I could do:
Join my nice table that has everything I ever wanted to know about
Q&A elements, easily to other tables like the answers & users, then:
Order by NewSeq,lname,fname
Then I could GROUP on NewSeq so every question was in the right order
on display, and then order by (after that) the student names. (As
just one of many vastly easier examples.)
I'm stopping now. I hope this made sense. I really appreciate the
experience of others here. I'm spending WAY too much time and not
getting much done while trying to figure stuff like this out, and I'm
already behind schedule.
Best regards,
Palyne
-------------------------------------------------------------------------
This email server is running an evaluation copy of the MailShield anti-
spam software. Please contact your email administrator if you have any
questions about this message. MailShield product info: www.mailshield.com
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
