This discussion is archived
2 Replies Latest reply: Nov 26, 2012 6:08 AM by kozand RSS

Aggregation Last /Max with xmltable HOW TO?

kozand Newbie
Currently Being Moderated
Hello
I have following select:

select
u.barcode_group_id,
u.EQ_ID,
n.BARCODE_ID,
n.BARCODE_MODULE,
r.RESULT_STATE,
r.RESULT_USER_ID,
r. RESULT_BARCODE,
r.RESULT_TIMETSAMP
from

xmltable (
'/order/jobList/barcodeGroup' PASSING xmltype('<order knr="40700083" vin="WVWZZZAAZED900001" lfdnr="750" pin="" type="UELE" product="BL">
<jobList>
<barcodeGroup ID="" equipmentID="123" type="type1" tmLock="true">
<barcode ID="" module="914" manufacturerCode="7R00">
</barcode>
<barcode ID="" module="915" manufacturerCode="AA00">
</barcode>
<result timeStamp="2012-11-22 12:49:34.8176" state="OK" userId="0">
</result>
<result timeStamp="2012-11-22 12:50:07.9124" state="NOK" userId="0">#12E901131A </result>
</barcodeGroup>

</jobList>
</order>')
columns
BARCODE_GROUP_ID varchar2(100) path '@ID',
Eq_id varchar2(100) path '@equipmentID',
BARCODE_GROUP_TYPE varchar2(100) path '@type',
TM_LOCK varchar2(100) path '@tmLock',
BARCODE_XML XMLTYPE path './barcode',
BARCODE_RESULT_XML xmltype path './result'
) u,
xmltable (
'/barcode' PASSING u.BARCODE_XML
columns
BARCODE_ID varchar2(100) path '@ID',
BARCODE_MODULE varchar2(100) path '@module'
) n,
xmltable (
'/result' PASSING u.BARCODE_RESULT_XML
columns
RESULT_TIMETSAMP varchar2(100) path '@timeStamp',
RESULT_STATE varchar2(100) path '@state',
RESULT_USER_ID varchar2(100) path '@userId',
RESULT_BARCODE varchar2(4000) path './text()'
) r;

The question is how to write this if I want to have only last result ( max “timestamp” ) for each “barcodeGroup” node?
Thanks in advance for help.

Best regards.

Edited by: kozand on 2012-11-26 01:18
  • 1. Re: Aggregation Last /Max with xmltable HOW TO?
    odie_63 Guru
    Currently Being Moderated
    For example :
    SELECT u.barcode_group_id,
           u.EQ_ID,
           n.BARCODE_ID,
           n.BARCODE_MODULE,
           r.RESULT_STATE,
           r.RESULT_USER_ID,
           r. RESULT_BARCODE,
           r.RESULT_TIMESTAMP
    FROM XMLTable (
           '/order/jobList/barcodeGroup' 
           PASSING 
    xmltype('<order knr="40700083" vin="WVWZZZAAZED900001" lfdnr="750" pin="" type="UELE" product="BL">
     <jobList>
      <barcodeGroup ID="" equipmentID="123" type="type1" tmLock="true">
       <barcode ID="" module="914" manufacturerCode="7R00"></barcode>
       <barcode ID="" module="915" manufacturerCode="AA00"></barcode>
       <result timeStamp="2012-11-22 12:49:34.8176" state="OK" userId="0"></result>
       <result timeStamp="2012-11-22 12:50:07.9124" state="NOK" userId="0">#12E901131A </result>
      </barcodeGroup>
     </jobList>
    </order>')
         COLUMNS BARCODE_GROUP_ID   varchar2(100) path '@ID',
                 Eq_id              varchar2(100) path '@equipmentID',
                 BARCODE_GROUP_TYPE varchar2(100) path '@type',
                 TM_LOCK            varchar2(100) path '@tmLock',
                 BARCODE_XML        XMLTYPE       path './barcode',
                 BARCODE_RESULT_XML xmltype       path './result'
         ) u,
         XMLTable (
           '/barcode' PASSING u.BARCODE_XML
           COLUMNS
             BARCODE_ID     varchar2(100) path '@ID',
             BARCODE_MODULE varchar2(100) path '@module'
         ) n,
         XMLTable (
           'for $r in /result 
            order by xs:dateTime(translate($r/@timeStamp," ","T")) descending
            return $r' 
           PASSING u.BARCODE_RESULT_XML
           COLUMNS
             RESULT_TIMESTAMP timestamp      path 'translate(@timeStamp," ","T")',
             RESULT_STATE     varchar2(100)  path '@state',
             RESULT_USER_ID   varchar2(100)  path '@userId',
             RESULT_BARCODE   varchar2(4000) path './text()',
             result_order     for ordinality
         ) r
    WHERE r.result_order = 1
    ;
  • 2. Re: Aggregation Last /Max with xmltable HOW TO?
    kozand Newbie
    Currently Being Moderated
    Hello odie_63      
    Thank You for solution.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points