2 Replies Latest reply: Nov 26, 2012 8:08 AM by kozand RSS

    Aggregation Last /Max with xmltable HOW TO?

    kozand
      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
          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
            Hello odie_63      
            Thank You for solution.