This discussion is archived
6 Replies Latest reply: Sep 29, 2011 2:18 AM by abhi26 RSS

Alternative of groupby in XQUERY TRANFORMATION..

abhi26 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    BRILLIANT ANSWER...WORKING!!!!!!!!!!!
    BRAVO


    thanks and regards,
    Aryaaaaaaaaaa...

Legend

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