6 Replies Latest reply: Sep 29, 2011 4:18 AM by abhi26 RSS

    Alternative of groupby in XQUERY TRANFORMATION..

    abhi26
      Hello,
      Suggest alternative of group by ,which i dont want to use here..as i dun want my elements to be selected in any paticular order.Please sugesst how can i use let command here.(here tranformations has been done between input and output xsd)

      <ns0:InvoicesResponse>

      <ns0:ListOfInvoices>
      {
      for $item1 in $executeReturn1/ns1:referencedtransactions/ns1:item

      group $item1 as $group by
      $item1/ns1:btOhinvamtDoc/ns1:amount as $key0,
      $item1/ns1:btOhstatus as $key1,
      $item1/ns1:btOhrefnum as $key2,
      $item1/ns1:btOhduedate/ns1:date as $key3,
      $item1/ns1:csId as $key4
      return
      <ns0:Invoices WATTotalCharges = "{ xs:string($key0) }"
      WATStatus = "{ data($key1) }"
      WATInvoiceNumber = "{ data($key2) }"
      WATDueDate = "{ xs:string(translate('CD/AB/EFGH','EFGH-CD-AB', xs:string(fn:substring-before($key3,'T'))))}"
      WATCustomerId = "{ xs:string($key4) }"/>
      }
      <ns0:Status>SUCCESS</ns0:Status>
      </ns0:ListOfInvoices>
      </ns0:InvoicesResponse>
      };
        • 1. Re: Alternative of groupby in XQUERY TRANFORMATION..
          tsuji
          First, I don't recognize "group ... by ..." keywords in xquery itself. Perhaps it is just figurative alluding the use in sql... Second, I don't recognize either the translate() function used in the code... I can figure what its intention by guessing, but I doubt the syntax and the correctness of the 3rd argument's position as well as the effectiveness of the replacing and replaced characters. So I just keep the substring-before in the following suggestion.

          To do it in xquery, I think this is what it would look like for the corresponding block.
          <ns0:InvoicesResponse>
          <ns0:ListOfInvoices>
          {
          let $base:=$executeReturn1/ns1:referencedtransactions/ns1:item
          for 
              $key0 in distinct-values($base/ns1:btOhinvamtDoc/ns1:amount)
             ,$key1 in distinct-values($base[ns1:btOhinvamtDoc/ns1:amount = $key0]/ns1:btOhstatus)
             ,$key2 in distinct-values($base[ns1:btOhinvamtDoc/ns1:amount = $key0 and ns1:btOhstatus = $key1]/ns1:btOhrefnum)
             ,$key3 in distinct-values($base[ns1:btOhinvamtDoc/ns1:amount = $key0 and ns1:btOhstatus = $key1 and ns1:btOhrefnum = $key2]/ns1:btOhduedate/ns1:date)
             ,$key4 in distinct-values($base[ns1:btOhinvamtDoc/ns1:amount = $key0 and ns1:btOhstatus = $key1 and ns1:btOhrefnum = $key2 and ns1:btOhduedate/ns1:date = $key3]/ns1:csId)
          return
          <ns0:Invoices WATTotalCharges = "{ xs:string($key0) }"
          WATStatus = "{ data($key1) }"
          WATInvoiceNumber = "{ data($key2) }"
          WATDueDate = "{ fn:substring-before($key3,'T') }"
          WATCustomerId = "{ xs:string($key4) }"/>
          <ns0:Status>SUCCESS</ns0:Status>
          }
          </ns0:ListOfInvoices>
          </ns0:InvoicesResponse>
          • 2. Re: Alternative of groupby in XQUERY TRANFORMATION..
            odie_63
            Hi,
            Suggest alternative of group by ,which i dont want to use here..
            What's your XQuery engine? And is what you posted a real working XQuery?

            The group-by clause is part of the XQuery 3.0 working draft (http://www.w3.org/TR/xquery-30/#id-group-by), which is currently only implemented by a few processors.
            But as tsuji noticed, this :
            group $item1 as $group by
            is not a standard syntax.
            Is that an implementation-specific feature?

            Edit : in addition to tsuji's suggestion, a similar approach here : http://www.w3.org/TR/xquery-30/#id-select-distinct

            Edited by: odie_63 on 28 sept. 2011 21:39
            • 3. Re: Alternative of groupby in XQUERY TRANFORMATION..
              abhi26
              Hello,
              Thanks very much for the reply..
              wat ever u wrote is not giving me any error..For one set of invoice,it is giving me output...but wen i am repeating the item block ,the for loop should execute twice and give me two blocks of output(invoice response),which is not showing.which means my the forloop is not executing twice....can u give me the reason..
              • 4. Re: Alternative of groupby in XQUERY TRANFORMATION..
                abhi26
                Hello,
                My requirement is,the item block will be repeated with values being same/duplicated and i should get two blocks in invoiceresponse.
                But in the code u suggested ,it is giving me 2 blocks of response wen given different values...
                • 5. Re: Alternative of groupby in XQUERY TRANFORMATION..
                  tsuji
                  I can only imagine without seeing what is the input and what structure of output is desired. If item is multiple corresponding to different invoices (?)...
                  Maybe something like this will give you the clue of how to properly nesting the elements?
                  It may or may not guess right, but that's the idea.
                  <ns0:InvoicesResponse>
                  {
                  let $collection:=$executeReturn1/ns1:referencedtransactions/ns1:item
                  for $base in $collection
                  return
                  <ns0:ListOfInvoices>
                  {
                  for
                      $key0 in distinct-values($base/ns1:btOhinvamtDoc/ns1:amount)
                     ,$key1 in distinct-values($base[ns1:btOhinvamtDoc/ns1:amount = $key0]/ns1:btOhstatus)
                     ,$key2 in distinct-values($base[ns1:btOhinvamtDoc/ns1:amount = $key0 and ns1:btOhstatus = $key1]/ns1:btOhrefnum)
                     ,$key3 in distinct-values($base[ns1:btOhinvamtDoc/ns1:amount = $key0 and ns1:btOhstatus = $key1 and ns1:btOhrefnum = $key2]/ns1:btOhduedate/ns1:date)
                     ,$key4 in distinct-values($base[ns1:btOhinvamtDoc/ns1:amount = $key0 and ns1:btOhstatus = $key1 and ns1:btOhrefnum = $key2 and ns1:btOhduedate/ns1:date = $key3]/ns1:csId)
                  return
                  <ns0:Invoices WATTotalCharges = "{ xs:string($key0) }"
                  WATStatus = "{ data($key1) }"
                  WATInvoiceNumber = "{ data($key2) }"
                  WATDueDate = "{ fn:substring-before($key3,'T') }"
                  WATCustomerId = "{ xs:string($key4) }"/>
                  <ns0:Status>SUCCESS</ns0:Status>
                  }
                  </ns0:ListOfInvoices>
                  }
                  </ns0:InvoicesResponse>
                  • 6. Re: Alternative of groupby in XQUERY TRANFORMATION..
                    abhi26
                    BRILLIANT ANSWER...WORKING!!!!!!!!!!!
                    BRAVO


                    thanks and regards,
                    Aryaaaaaaaaaa...