group the query result

[cfmx]

Hi all,
I have this sql string

select tt.ENTRYID,
tt.PROJECT,
tt.DEVELOPER,
tt.TASK,
tt.DESCRIPTION,
to_char(tt.WORKDATE, 'MM/DD/YYYY') as WORKDATE,
tt.DURATION,
tt.MILEAGE,
p.PROJECTNAME,
u.USERNAME
from TIMETRACKER tt,
BUGPROJECT p,
BUGUSER u
where tt.WORKDATE between TO_DATE('" + start_proj_date + "', 'mm/dd/yyyy') and TO_DATE('" + end_proj_date + "', 'mm/dd/yyyy')
and tt.PROJECT = p.PROJECT
and tt.DEVELOPER = u.USERID

Now I want to group by tt.DEVELOPER and then group by tt.PROJECT
So when i display them it looks like this

Developer: JJ
Project: Proj 1
Task: task 1
Task: task 2
...
Project: Proj 2
Task: task 1
Task: task 2
...

Developer: James
Project: Proj 1
Task: task 1
Task: task 2
...
Project: Proj 2
Task: task 1
Task: task 2
...

Will this possible to do with only one query? The reason I'm doing this is I'm putting all resultSet into contexts and pass to velocity page.

JJ //

 

[DBAwhosaysNIE]

the short answer: sort of...

it could be done with one UGLY query using either a UNION of summany and detail subqueries (hence sort-of) or possibly a subquery w/GROUPING functions (see Oracle docs) within an outer "wrapper" to reverse the sort (summary 1st). if you're just running this from sqlplus & spooling to flat file it might be possible to use sqlplus' formatting commands (ttitle, compute, etc.) to do this but that would also get ugly.

this is one case where I would actually write a PL/SQL block w/DBMS_OUTPUT (or UTIL_FILE) using nested cursors and (as most of my posts indicate) I'm usually a pretty hardcore "KISS" person...

 

[cfmx]

Thx for quick response.

When you say 'UGLY' you mean the sql is big and slow? If this is possible then can you show me the sample code based on my original sql statement?

Here is what I'm doing for this.
I'm executing the sql in servlet and put the results into context and pass to velocity page(html). After I get the context, I use "for loop" to read query list from the context and display. But I do not know how to group them... I'm not sure this question is belong to java or other place but I thought I could solve this problem if I can write sql statement with that function.

Sorry for too much asking.
Thank you.