Solved

Oracle Group query

Posted on 2013-01-04
5
360 Views
Last Modified: 2013-01-07
I have the following query

select
LOOKUP_SUB_TYPE_CODE as PERMIT_ID,
LOOKUP_VALUE_TEXT as NET_ID,
RTRIM(XMLAGG(XMLELEMENT(e, LOOKUP_MISC_CODE || '|')).extract ('//text()'), '|') as PrintDates
FROM
OMNI_COMMON_LOOKUP WHERE LOOKUP_KEY='TPSA_PRINT_LOG'
group by LOOKUP_SUB_TYPE_CODE, LOOKUP_VALUE_TEXT

If there are multiple PermitIDs and the same NET_IDs it Groups them and then it will Pipe delimit the dates for the PrintDates field and it works good. I do however wanna have the PrintDates ordered Descending. I do not have to use the current logic if I cant get them in descending order.

Here is an example:
Permit_ID     Net_ID       PrintDates
100047          XYZ           08/08/2012 11:57:01 AM|08/08/2012 11:58:44 AM
                                          (I need this to order the dates desc)
Thanks
0
Comment
Question by:jknj72
  • 3
  • 2
5 Comments
 

Author Comment

by:jknj72
ID: 38745177
FYI, my PrintDate is a text field so it will have to be converted to dates
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38745193
just add an order by to the xmlagg call


SELECT lookup_sub_type_code AS permit_id,
       lookup_value_text AS net_id,
       RTRIM(
           XMLAGG(XMLELEMENT(e, lookup_misc_code || '|') ORDER BY
                                                             TO_DATE(
                                                                 lookup_misc_code,
                                                                 'mm/dd/yyyy hh:mi:ss am'
                                                             )).EXTRACT('//text()'),
           '|'
       )
           AS printdates
  FROM omni_common_lookup
 WHERE lookup_key = 'TPSA_PRINT_LOG'
GROUP BY lookup_sub_type_code, lookup_value_text
0
 

Author Comment

by:jknj72
ID: 38745289
Where would I put the DESC at?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 38745359
SELECT lookup_sub_type_code AS permit_id,
       lookup_value_text AS net_id,
       RTRIM(
           XMLAGG(XMLELEMENT(e, lookup_misc_code || '|') ORDER BY
                                                             TO_DATE(
                                                                 lookup_misc_code,
                                                                 'mm/dd/yyyy hh:mi:ss am'
                                                             ) DESC).EXTRACT('//text()'),
           '|'
       )
           AS printdates
  FROM omni_common_lookup
 WHERE lookup_key = 'TPSA_PRINT_LOG'
GROUP BY lookup_sub_type_code, lookup_value_text
0
 

Author Closing Comment

by:jknj72
ID: 38750604
Thank you
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sybase and replication server 13 40
Complex Query - help please 5 52
Requesting help with creating an SQL query with 2 tables 6 27
Loading flat file data in tables 2 42
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question