Solved

Oracle Group query

Posted on 2013-01-04
5
358 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 73

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 73

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MULTIPLE DATE QUERY 15 76
MS SQL 2008 Divide by zero error encountered. Error 3 33
grant user/role question 11 25
What's wrong with this T-SQL Foreign Key? 7 39
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

815 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now