CODEWITHSUNDEEP
comparisondecimalxquery
IronheadJay
IronheadJay

Reputation: 35

Comparing two decimal values in xquery

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

Upvotes: 1

Views: 1176

Answers (2)

Dave Cassel
Dave Cassel

Reputation: 8422

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).

Upvotes: 1

Christian Gr&#252;n
Christian Gr&#252;n

Reputation: 6229

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)))

Upvotes: 3

Related Questions