Ian,
Stephane mentioned the possibility of using analytical functions. I know
that you have used the analytical functions in the past so you may have
already taken Stephane's idea and run with it, and maybe Stephane has as
well. If not, here is Stephane's solution modified so that the DB function
"course_index" (and the call to it) is replaced with an in-line view using
the ROW_NUMBER function.
Note in the following select that "course_set", using the in-line view with
the ROW_NUMBER function, returns the same results as Stephane's calculation
using the DB function. I included the use of Stephane's code calling his
function for comparison purposes.
SELECT
x.emplid,
sum(x.course_id * power(10,z.stephane - 1)) course_set,
sum(x.course_id * power(10,course_index(x.emplid,x.course_id) - 1)) Steph
FROM
ian x,
(SELECT emplid,
course_id,
ROW_NUMBER() OVER (PARTITION BY emplid
ORDER BY course_id desc) as stephane
FROM ian) z
WHERE
x.emplid = z.emplid and
x.course_id = z.course_id
GROUP BY
x.emplid
ORDER BY
2,1
When you showed the groupings, you had emplid's with the same courses on a
single line separated by commas. I'm not sure if that is how you want the
output, or, if you were simply showing the pairings. If you want the output
like that, then obviously the above isn't doing that, and with the number of
matching emplid's unknown in the "real" world, a call to a DB function to
get them on the same line would be needed.
I've got a question that maybe someone can answer. One approach I tried was
using the CAST function. This required creating a type (not a purely
relational approach):
create or replace type IanTableType as table of number
/
1 select emplid,
2 cast(multiset(select course_id from ian x where x.emplid =
ian.emplid ) as IanTableType) x
3 from ian
4* group by emplid
SQL> /
EMPLID X
---------- ------------------------------
1 IANTABLETYPE(1)
2 IANTABLETYPE(2, 3)
3 IANTABLETYPE(3, 4, 5)
4 IANTABLETYPE(3, 4, 5)
5 IANTABLETYPE(2, 3)
6 IANTABLETYPE(1)
7 IANTABLETYPE(2)
8 IANTABLETYPE(3, 4)
8 rows selected.
Now this gives me each emplid on a single line with the courses they took.
If I could order by the CAST function I could accomplish the same thing as
in the prior query. But, you can't order by the CAST function, at least not
directly. Any tricks to simulate this or get around it?
Regards,
Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781
>
> I don't think you can, unless, perhaps, you use the analytical functions
> which I have never had the opportunity to play with. Problem number 1
> is getting an identifier for each set of courses. Since you must get
> this through a 'GROUP BY', the only chance is a numerical expression. An
> obvious candidate is something like sum(course_id * power(10, n -1))
> where n is the order (starting with 1) of course ids suitably ordered
> for each employee - restarting from 1 with each employee. Getting n is
> the trouble. You cannot get it through rownums and in-line views, it
> would require some kind of ugly three-way correlation between views in
> the FROM clause and a subquery. The best solution I see, but it's not a
> 'pure play' one, is to create a function
>
> create or replace function course_index(p_emplid in number,
> p_course_id in number)
> return number
> as
> n_val number;
> begin
> select a.n
> into n_val
> from (select b.course_id, rownum n
> from (select course_id
> from courses_taken
> where emplid = p_emplid
> order by 1 desc) b) a
> where a.course_id = p_course_id;
> return n
> exception
> when no_data_found then
> return null;
> end;
>
> Then it becomes relatively easy to write
>
> select emplid, sum(course_id * power(10, course_index(emplid,
> course_id) - 1) course_set
> from courses_taken
> group by emplid
>
> and then to do whatever you want.
>
> --
> Regards,
>
> Stephane Faroult
> Oriole Corporation
> Voice: +44 (0) 7050-696-269
> Fax: +44 (0) 7050-696-449
> Performance Tools & Free Scripts
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Larry Elkins
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
