Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle Group query

Posted on 2013-01-04
5
Medium Priority
?
365 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

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.​
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

715 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