XQuery question

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

XQuery question

Sava Jurisic
Hi all,

I am stuck with my query and hope some of you gurus can advice how to resolve
this issue;

XML files are all kept in a single collection; The structure is as follows:

<?xml version="1.0" encoding="UTF-8"?>
<bookkeeping date="20041003">
    <Cash>
        <record billed="60" category="Fee" drid="3" index="1" pid="3" qty="1"
received="60" unitprice="">
            <desc>item x</desc>
            <bdate>20050119</bdate>
        </record>
        <record billed="0" category="Fee" drid="3" index="1" pid="14" qty="1"
received="(-60)" unitprice="">
            <desc>item y</desc>
            <bdate>20050119</bdate>
        </record>
           .
           .
           .
           .
   </cash>
   <visa/>
   <master/>
</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 $y)}"/>

------------------------------------------------------------------------------

Now, the query works just fine except when I have the value of either attributes
"billed" or "received" set to "(XYZ)" (point is that the value has brackets and
I can not change that)

The exception i have is like this:

"An exception occurred during query execution: cannot convert string '(-50)'
into a double  "

What is the workaround???

MANY THANKS!!!

Sava











-------------------------------------------------------
SF.Net email is sponsored by:
Tame your development challenges with Apache's Geronimo App Server. Download
it for free - -and be entered to win a 42" plasma tv or your very own
Sony(tm)PSP.  Click here to play: http://sourceforge.net/geronimo.php
_______________________________________________
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-2
Hi,

Sava Jurisic wrote:

> Now, the query works just fine except when I have the value of either attributes
> "billed" or "received" set to "(XYZ)" (point is that the value has brackets and
> I can not change that)
>
> The exception i have is like this:
>
> "An exception occurred during query execution: cannot convert string '(-50)'
> into a double  "
>
> What is the workaround???

Something like :

let $doc :=
  <blah>
    <record received="60"/>
    <record received="(-60)"/>
  </blah>
let $recv := $doc/record/@received
let $normalized_recv := for $y in $recv where $y ne '' return
fn:number(translate($y, '()','  '))
return
<result received="{fn:sum($normalized_recv)}"/>

Cheers,

--
Pierrick Brihaye, informaticien
Service régional de l'Inventaire
DRAC Bretagne
mailto:[hidden email]
+33 (0)2 99 29 67 78


-------------------------------------------------------
SF.Net email is sponsored by:
Tame your development challenges with Apache's Geronimo App Server. Download
it for free - -and be entered to win a 42" plasma tv or your very own
Sony(tm)PSP.  Click here to play: http://sourceforge.net/geronimo.php
_______________________________________________
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
In reply to this post by Sava Jurisic
Sava,

I can see from your post that you yourself understand the source of your
problem and are indeed just asking about workarounds, but anyone who has
been spared an acquaintance with accounting conventions might need a little
more explanation:

Sava's data uses the convention (-nn) to indicate deficits or debits,
where -nn is, of course a signed numeric value. The problem arises because
he wants to pass attribute values using this notational convention to
fn:sum(), which expects a sequence either of numeric values or of values
that can be cast to numeric and hence chokes on the opening parenthesis. (I
may be mistaken here, but I'd expect such a value  to be rejected by a
schema validator if the value in question were typed as numeric, but maybe
somewhere in the labyrinth of W3C datatyping rules there is allowance for
this notation as a quasi-operator??).

Since as far as I can see both eXist and XQuery are doing the right thing
here, the only solution seems to munging those values before summing them
along the lines Pierrick has suggested. You would, I think also need a
post-summing munge to put the parentheses back where the result was
negative.

You could also, I suppose, consider a local override to the fn:sum() method
that would enable it to cope with parentheses around a castable string
value. Or, more respectably, create your own custom summing function that
knew what to do and use that instead in your queries.

But if you prefer not to mess with core functions or add your own, and if
the precise lexical format of your data is indeed sacred, then you probably
do have to do this case-by-case in your queries. Is there no possibility of
a global transform to remove those parentheses (but keeping the sign, of
course) at storage time, followed by a transform of any results to put them
back where needed?

Michael Beddow



-------------------------------------------------------
SF.Net email is sponsored by:
Tame your development challenges with Apache's Geronimo App Server. Download
it for free - -and be entered to win a 42" plasma tv or your very own
Sony(tm)PSP.  Click here to play: http://sourceforge.net/geronimo.php
_______________________________________________
Exist-open mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/exist-open