Comparing two decimal values in xquery

1
votes

I am trying to find which price value is the smallest in xquery. I have two price values being passed in XML and I can't seem to get past syntax errors when trying to compare the values.

Example XML:

<purchase>
  ....
  <articlePrice>$20.00</articlePrice>
  <issuePrice>$154.00</issuePrice>
  ....
</purchase>

I have the below to strip out the '$'

let $price := fn:substring-after($price, '$')
let $priceIssue := fn:substring-after($priceIssue, '$')

I've tried to use the built in fn:min() but gives "XDMP-UNEXPECTED: (err:XPST0003) Unexpected token syntax error, unexpected QName_".

fn:min(((xs:decimal($price)), (xs:decimal($priceIssue))))

Ive tried using both 'gt' and '>' in an if statement but still get syntax error above.

The only way that I could get around the errors is casting them as strings but the comparison is wrong. Given the data above, as strings the logic returns '154.00' as smaller than '20.00' because the '1' is smaller than the '2'.

My exact code:

let $price := if (xs:string($ecommerce/articlePrice)) then fn:substring-after(xs:string($ecommerce/articlePrice), '$') else ""

let $priceIssue := if (xs:string($ecommerce/issuePrice)) then fn:substring-after(xs:string($ecommerce/issuePrice), '$') else ""

let $priceDisplay := fn:min(xs:decimal($price), xs:decimal($priceIssue))

results as XDMP-ARGTYPE: (err:XPTY0004) fn:min(20.0, 154.0) -- arg2 is not of type xs:string

2
comparisondecimalxquery
There is a pair of parentheses missing around the fn:min arguments in the expression for $priceDisplay. As it is written, it refers to the two-argument version, which has a collation as the second argument - thus the error message. Rather you need the single argument version, passing a sequence of values as the argument, and a literal sequence must be parenthesized, unless it consists of a single item. Also note that you cannot cast an empty string to xs:decimal, so better use an empty sequence, i.e. () in the else expressions. - Gunther

2 Answers

3
votes

I’m not sure how your complete code looks like (it looks a little bit sketchy), but the following query should work:

let $doc :=
  <purchase>
    <articlePrice>$20.00</articlePrice>
    <issuePrice>$154.00</issuePrice>
  </purchase>
let $price := fn:substring-after($doc/articlePrice, '$')
let $priceIssue := fn:substring-after($doc/issuePrice, '$')
return fn:min((xs:decimal($price), xs:decimal($priceIssue)))
1
votes

If you have the option to do so, I would adjust the XML. Right now, you're mixing different parts of the data: the numeric value and the currency. 

let $doc := 
  <purchase>
    <articlePrice curr="$">20.00</articlePrice>
    <issuePrice curr="$">154.00</issuePrice>
  </purchase>
return fn:min(($doc/articlePrice, $doc/issuePrice))

Your calculations would be a lot easier this way. You could then add the dollar sign back in when you're ready to present something to the end user. Another advantage: with simple numbers, you'll be able to index elements like this as numbers instead of strings (maybe not important for this particular case, but helpful in a lot of cases).