0 Replies Latest reply: Jan 23, 2013 1:38 AM by 903527 RSS

    Query For Xquery Implementation of Date Function

    903527
      Hi All,
      Objective: In array Item, I have to check for device entry Dates.
      First device entry date would be the date corresponding to device seq no =0
      Latest entry date would be the date corresponding to max device seq no.
      Diff date should be differnce between first entry date and latest entry dates in days.

      Output should be like
      <FirstEntrydate></FirstEntrydate>
      <LatestEntryDate></LatestEntryDate>
      <Diffdate></Diffdate>
      <Maxdevice Seq no></MaxdeviceSeqno>


      the XML to transform is :
      <?xml version="1.0"?>
      <ns0:contractDeviceHistory xmlns:ns0="http://www.lhsgroup.com/ws_v3">
      <ns0:item>
      *<ns0:deviceEntryDate>*
      *<ns0:date>2015-01-09T09:30:22</ns0:date>*
      *</ns0:deviceEntryDate>*
      <ns0:deviceId>3</ns0:deviceId>
      <ns0:deviceModDate>
      <ns0:date>2013-01-21T09:30:22</ns0:date>
      </ns0:deviceModDate>
      <ns0:devicePort>devicePort_1</ns0:devicePort>
      *<ns0:deviceSeqno>2</ns0:deviceSeqno>*
      <ns0:deviceSmNum>deviceSmNum_1</ns0:deviceSmNum>
      <ns0:deviceStatus>deviceStatus_1</ns0:deviceStatus>
      <ns0:deviceUserLastMod>deviceUserLastMod_1</ns0:deviceUserLastMod>
      <ns0:deviceValidFrom>
      <ns0:date>2013-01-06T09:30:22</ns0:date>
      </ns0:deviceValidFrom>
      <ns0:hlcode>32</ns0:hlcode>
      <ns0:hlcodePub>hlcodePub_1</ns0:hlcodePub>
      <ns0:reason>64</ns0:reason>
      </ns0:item>
      <ns0:item>
      *<ns0:deviceEntryDate>*
      *<ns0:date>2014-01-09T09:30:22</ns0:date>*
      *</ns0:deviceEntryDate>*
      <ns0:deviceId>3</ns0:deviceId>
      <ns0:deviceModDate>
      <ns0:date>2013-01-21T09:30:22</ns0:date>
      </ns0:deviceModDate>
      <ns0:devicePort>devicePort_1</ns0:devicePort>
      *<ns0:deviceSeqno>1</ns0:deviceSeqno>*
      <ns0:deviceSmNum>deviceSmNum_1</ns0:deviceSmNum>
      <ns0:deviceStatus>deviceStatus_1</ns0:deviceStatus>
      <ns0:deviceUserLastMod>deviceUserLastMod_1</ns0:deviceUserLastMod>
      <ns0:deviceValidFrom>
      <ns0:date>2013-01-06T09:30:22</ns0:date>
      </ns0:deviceValidFrom>
      <ns0:hlcode>32</ns0:hlcode>
      <ns0:hlcodePub>hlcodePub_1</ns0:hlcodePub>
      <ns0:reason>64</ns0:reason>
      </ns0:item>
      <ns0:item>
      *<ns0:deviceEntryDate>*
      *<ns0:date>2012-01-09T09:30:22</ns0:date>*
      *</ns0:deviceEntryDate>*
      <ns0:deviceId>3</ns0:deviceId>
      <ns0:deviceModDate>
      <ns0:date>2012-01-21T09:30:22</ns0:date>
      </ns0:deviceModDate>
      <ns0:devicePort>devicePort_1</ns0:devicePort>
      *<ns0:deviceSeqno>0.0</ns0:deviceSeqno>*
      <ns0:deviceSmNum>deviceSmNum_1</ns0:deviceSmNum>
      <ns0:deviceStatus>deviceStatus_1</ns0:deviceStatus>
      <ns0:deviceUserLastMod>deviceUserLastMod_1</ns0:deviceUserLastMod>
      <ns0:deviceValidFrom>
      <ns0:date>2013-01-06T09:30:22</ns0:date>
      </ns0:deviceValidFrom>
      <ns0:hlcode>32</ns0:hlcode>
      <ns0:hlcodePub>hlcodePub_1</ns0:hlcodePub>
      <ns0:reason>64</ns0:reason>
      </ns0:item>
      </ns0:contractDeviceHistory>




      The Incomplete Xquery i wrote is this, which is not working. Plz suggest



      declare function xf:SIMReplacementInfoResBSCSToSiebel($contractDeviceHistory1 as element())
      as element(ns0:SIMReplacementResponse) {
      <ns0:SIMReplacementResponse>
      <ns0:SIMReplacement>
      {   

      for $item in $contractDeviceHistory1/ns1:item
      group $item as $group by

                     
                     $item/ns1:deviceSeqno as $key1,
                          $item/ns1:deviceEntryDate/ns1:date as $key2
                          
                          
                     
                     order by $key1 descending

      return
      <ns0:SIMReplacement
      MaxDeviceSeqNo= "{ fn:max($key1) }"
      FirstEntry_date = "{if(($key1) = '0.0')then($key2) else (0)}"
      LatestEntry_date="{if(($key1)= fn:max($key1))then($key2) else ($key2)}"

      /> }
      <ns0:Status>Success</ns0:Status>
      </ns0:SIMReplacement>
      </ns0:SIMReplacementResponse>
      };

      declare variable $contractDeviceHistory1 as element() external;

      xf:SIMReplacementInfoResBSCSToSiebel($contractDeviceHistory1)





      Regards
      Manu
      India.