XQuery question

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

XQuery question

Sava Jurisic
Hi All,

I hope this is a simple xquery question for you, but I simply couldn't figure it
out;

My xml file:
<?xml version="1.0" encoding="UTF-8"?>
<bookkeeping date="20050125">
    <Cash>
        <record billed="60" category="Fee" drid="3" index="1" pid="14" qty="1"
received="60" unitprice="">
            <desc>Consul/Exam 60</desc>
            <bdate>20050125</bdate>
        </record>
        <record billed="40" category="Fee" drid="2" index="1" pid="15" qty="1"
received="20" unitprice="">
            <desc>Adjust  40</desc>
            <bdate>20050130</bdate>
        </record>
        <record billed="40" category="Fee" drid="2" index="1" pid="15" qty="1"
received="20" unitprice="">
            <desc>OST: Adjust  40</desc>
            <bdate>20050130</bdate>
        </record>
     </Cash>
</bookkeeping>
---------------------------------------------------------------------------
My Query:

let $year := 2005
let $jan_bill := //bookkeeping[@date[contains(., fn:concat($year,
"01"))]]//record/@billed
let $jan_recv := //bookkeeping[@date[contains(., fn:concat($year,
"01"))]]//record/@received
return
<Year_end year="{$year}">
<January billed="{fn:sum(for $i in $jan_bill where $i ne '' return $i)}"
received="{fn:sum(for $y in $jan_recv where $y ne '' return
fn:number(translate($y, '()','  ')))}"/>
</Year_end>
----------------------------------------------------------------------------

This query works fine, but it is not what I need; What I need is:

If <desc/> contains "OST:" word, I need to exclude the amount of "billed" of
that node; In above example, the result should be:

<Year_end year="2005">
    <January billed="100.0" received="100.0"/>
</Year_end>

The current result:

<Year_end year="2005">
    <January billed="140.0" received="100.0"/>
</Year_end>
---------------------------------------------------

MANY THANKS!
Sava
[hidden email]
     



-------------------------------------------------------
This SF.Net email is sponsored by:
Power Architecture Resource Center: Free content, downloads, discussions,
and more. http://solutions.newsforge.com/ibmarch.tmpl
_______________________________________________
Exist-open mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/exist-open
Reply | Threaded
Open this post in threaded view
|

Re: XQuery question

Pierrick Brihaye
Hi,

Sava Jurisic wrote:

 > The current result:
 >
 > <Year_end year="2005">
 >     <January billed="140.0" received="100.0"/>
 > </Year_end>


 From http://demo.exist-db.org/xquery/xquery.xq, I've tried this :

let $doc : =
<doc>
<bookkeeping date="20050125">
     <Cash>
         <record billed="60" category="Fee" drid="3" index="1" pid="14"
qty="1"
received="60" unitprice="">
             <desc>Consul/Exam 60</desc>
             <bdate>20050125</bdate>
         </record>
         <record billed="40" category="Fee" drid="2" index="1" pid="15"
qty="1"
received="20" unitprice="">
             <desc>Adjust  40</desc>
             <bdate>20050130</bdate>
         </record>
         <record billed="40" category="Fee" drid="2" index="1" pid="15"
qty="1"
received="20" unitprice="">
             <desc>OST: Adjust  40</desc>
             <bdate>20050130</bdate>
         </record>
      </Cash>
</bookkeeping>
</doc>

let $year := 2005
let $jan_bill := $doc//bookkeeping[@date[contains(., fn:concat($year,
"01"))]]//record/@billed
let $jan_recv := $doc//bookkeeping[@date[contains(., fn:concat($year,
"01"))]]//record/@received
return
<Year_end year="{$year}">
<January billed="{fn:sum(for $i in $jan_bill where $i ne '' return $i)}"
received="{fn:sum(for $y in $jan_recv where $y ne '' return
fn:number(translate($y, '()','  ')))}"/>
</Year_end>

I get :

<Year_end year  ="2005"  >
   <January billed ="140.0" received ="100.0" />
</ Year_end >

What version are you using ?

Cheers,

p.b.



-------------------------------------------------------
This SF.Net email is sponsored by:
Power Architecture Resource Center: Free content, downloads, discussions,
and more. http://solutions.newsforge.com/ibmarch.tmpl
_______________________________________________
Exist-open mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/exist-open
Reply | Threaded
Open this post in threaded view
|

Re: XQuery question

Michael Beddow-2
I think Pierrick may have misunderstood what Sava meant by "the result
should be". I suspect Sava meant "The result I would like to see if I could
figure out the right code to produce it", not "the result which eXist should
be producing for the query I'm currently using."

One plea here. Yes, it's good (indeed necessary) to provide sample data and
queries to show what a problem is. But it's even better from the viewpoint
of those of us who like to help as far as we can if posters can simplify the
samples they offer so that they show only the issue in question. For a
poster, stripping out unnecessary parts ought to be straightforward: after
all he/she knows what all the detail is for and therefore which parts of it
are dispensible. Added to which, the exercise of homing in on the
problematic parts can sometimes actually cause a flash of insight into the
problem that makes the posting unnecessary. For those of us who don't know
(and in all honesty often don't want to know) the details of a poster's data
or applications, having to peer past them to identify the key points is
additional work which we aren't the best people to perform.

That plea isn't by any means solely prompted by the current post, BTW. There
have been quite a few other instances lately where I for one simply haven't
had the time or the energy to dig down to the real problem through the
project-specific data and coding that were covering it.

Anyway, what I would suggest here is the use of the if-then-else
construction (warning to non-RTFM practitioners: you do need an else in
XQuery, even if it's a do-nothing). The following amended query still
produces the same wrong results, for the unsurprising reason that I've
pasted the existing code into both branches of the conditional. However,
tweaking the code in the THEN branch would allow the required conditionality
to be achieved. Also, I've just made the minimal changes needed to
illustrate the principle here. The code could (should) be reorganised to
remove a lot of the duplication and trim down the content of the conditional
blocks.

=================

let $year := 2005
let $jan_bill := $doc//bookkeeping[@date[contains(., fn:concat($year,
"01"))]]//record/@billed
let $jan_recv := $doc//bookkeeping[@date[contains(., fn:concat($year,
"01"))]]//record/@received

return
if (starts-with($doc/bookkeeping/desc,'OST')) then
<Year_end year="{$year}">
<January billed="{fn:sum(for $i in $jan_bill where $i ne '' return $i)}"
received="{fn:sum(for $y in $jan_recv where $y ne '' return
fn:number(translate($y, '()','  ')))}"/>
</Year_end>
else
<Year_end year="{$year}">
<January billed="{fn:sum(for $i in $jan_bill where $i ne '' return $i)}"
received="{fn:sum(for $y in $jan_recv where $y ne '' return
fn:number(translate($y, '()','  ')))}"/>
</Year_end>

===========================
Michael Beddow



-------------------------------------------------------
This SF.Net email is sponsored by:
Power Architecture Resource Center: Free content, downloads, discussions,
and more. http://solutions.newsforge.com/ibmarch.tmpl
_______________________________________________
Exist-open mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/exist-open
Reply | Threaded
Open this post in threaded view
|

Re: XQuery question

Sava Jurisic
In reply to this post by Pierrick Brihaye
Pierrick Brihaye <pierrick.brihaye <at> free.fr> writes:

Hi Pierrick,

I am using the latest exist 20050805

... and I get the same:

<Year_end year  ="2005">
  <January billed ="140.0" received ="100.0"/>
</Year_end>

However, I need to see

<Year_end year  ="2005">
    <January billed ="100.0" received ="100.0"/>
</Year_end>
-------------------------------------------------------
Like Michael said, "The result I would like ti see IF I could figure out the
right code to produce it" ... whereever I have a node with <desc>OST:
...</desc>, I would like to EXCLUDE the billed amount from that node from
calculation;
In above example, 'billed' should be eq '100', not '140';

Thanks,
Sava





-------------------------------------------------------
This SF.Net email is sponsored by:
Power Architecture Resource Center: Free content, downloads, discussions,
and more. http://solutions.newsforge.com/ibmarch.tmpl
_______________________________________________
Exist-open mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/exist-open
Reply | Threaded
Open this post in threaded view
|

Re: Re: XQuery question

Adam Retter-7
In reply to this post by Sava Jurisic
So you want to filter out all results that have "OST:" in the text?

If so surely something like

[not(contains(., "OST:"))]

would do the trick. The . means the current node, so you may need to
replace the . with the XPath to your desc node from the root or current
node or adjust your context appropriately...

Cheers Adam

On Wed, 2005-10-19 at 17:42 +0100, Sava Jurisic wrote:

> Pierrick Brihaye <pierrick.brihaye <at> free.fr> writes:
>
> Hi Pierrick,
>
> I am using the latest exist 20050805
>
> ... and I get the same:
>
> <Year_end year  ="2005">
>   <January billed ="140.0" received ="100.0"/>
> </Year_end>
>
> However, I need to see
>
> <Year_end year  ="2005">
>     <January billed ="100.0" received ="100.0"/>
> </Year_end>
> -------------------------------------------------------
> Like Michael said, "The result I would like ti see IF I could figure
> out the
> right code to produce it" ... whereever I have a node with <desc>OST:
> ...</desc>, I would like to EXCLUDE the billed amount from that node
> from
> calculation;
> In above example, 'billed' should be eq '100', not '140';
>
> Thanks,
> Sava
>
>
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by:
> Power Architecture Resource Center: Free content, downloads,
> discussions,
> and more. http://solutions.newsforge.com/ibmarch.tmpl 
> _______________________________________________
> Exist-open mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/exist-open
>


-------------------------------------------------------
This SF.Net email is sponsored by:
Power Architecture Resource Center: Free content, downloads, discussions,
and more. http://solutions.newsforge.com/ibmarch.tmpl
_______________________________________________
Exist-open mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/exist-open
Reply | Threaded
Open this post in threaded view
|

Re: Re: XQuery question

Michael Beddow-2
> So you want to filter out all results that have "OST:" in the text?
>

Probably not. Sava wants (I think) to process nodes that have a <desc> value
starting with OST differently from those that don't. Specifically, he wants
to make sure that the value of the billed attribute on the parent <record>
of such nodes doesn't get added into the billed total he's calculating.

OK, forget my somewhat crude attempt to prompt a major rethink. I ought to
resist the attempt to re-write other people's code, my own is bad enough.

Leaving the original more or less as-is, but replacing

<January billed="{fn:sum(for $i in $jan_bill where $i ne '' return $i)}"
with
<January billed="{fn:sum(for $i in $jan_bill where $i ne '' and not
(starts-with($i/../desc,'OST'))
return $i)}"

will produce

<Year_end year="2005">
    <January billed="100.0" received="100.0"/>
</Year_end>

as required

I think I'll  sit out the rest of this thread at the bottom of the hole I've
dug for myself.

Michael Beddow




-------------------------------------------------------
This SF.Net email is sponsored by:
Power Architecture Resource Center: Free content, downloads, discussions,
and more. http://solutions.newsforge.com/ibmarch.tmpl
_______________________________________________
Exist-open mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/exist-open
Reply | Threaded
Open this post in threaded view
|

Re: Re: XQuery question

Pierrick Brihaye
Hi,

Michael Beddow wrote:

> OK, forget my somewhat crude attempt to prompt a major rethink. I ought to
> resist the attempt to re-write other people's code, my own is bad enough.

I have less problems to rewrite someone's code if it can't show good
pratices and improve performance :

let $doc : =
<doc>
<bookkeeping date="20050125">
     <Cash>
         <record billed="60" category="Fee" drid="3" index="1" pid="14"
qty="1"
received="60" unitprice="">
             <desc>Consul/Exam 60</desc>
             <bdate>20050125</bdate>
         </record>
         <record billed="40" category="Fee" drid="2" index="1" pid="15"
qty="1"
received="20" unitprice="">
             <desc>Adjust  40</desc>
             <bdate>20050130</bdate>
         </record>
         <record billed="40" category="Fee" drid="2" index="1" pid="15"
qty="1"
received="20" unitprice="">
             <desc>OST: Adjust  40</desc>
             <bdate>20050130</bdate>
         </record>
      </Cash>
</bookkeeping>
</doc>

let $year := 2005
(: isolate constants :)
let $whatIwant := concat($year, "01")
(: double predicate :)
let $jan_bill := $doc//bookkeeping[contains(@date,
$whatIwant)]//record[not(starts-with(desc,"OST"))]/@billed
(: use fonctions when constructing :)
let $jan_recv := $doc//bookkeeping[contains(@date,
$whatIwant)]//record/translate(@received, '()','  ')
return
<Year_end year="{$year}">
<!-- ... and use simple statements ! -->
<January billed="{sum($jan_bill)}"
received="{sum($jan_recv)}"/>
</Year_end>

Cheers,

p.b.


-------------------------------------------------------
This SF.Net email is sponsored by:
Power Architecture Resource Center: Free content, downloads, discussions,
and more. http://solutions.newsforge.com/ibmarch.tmpl
_______________________________________________
Exist-open mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/exist-open
Reply | Threaded
Open this post in threaded view
|

Re: XQuery question

Sava Jurisic
In reply to this post by Michael Beddow-2
Michael Beddow <mbexlist-2 <at> mbeddow.net> writes:

>
> I think I'll  sit out the rest of this thread at the bottom of the hole I've
> dug for myself.
>
> Michael Beddow
>
> -------------------------------------------------------
> This SF.Net email is sponsored by:
> Power Architecture Resource Center: Free content, downloads, discussions,
> and more. http://solutions.newsforge.com/ibmarch.tmpl
>


Michael, Pierrick,

THANKS A LOT for the help ... it did the trick;

I really appreciate your help;

Cheers,
Sava



-------------------------------------------------------
This SF.Net email is sponsored by:
Power Architecture Resource Center: Free content, downloads, discussions,
and more. http://solutions.newsforge.com/ibmarch.tmpl
_______________________________________________
Exist-open mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/exist-open