Hello,
I have problem in using sort by with join in xquery.

Three product xml are in tamino they look like

... ---------------------------------------------------- ... ---------------------------------------------------- ... ---------------------------------------------------- I am trying to retrived Product information of sku="123" and sku="456" sorted by RecommendedProducts Sequence of the parent product 012.. the query goes something like

for $b in input()/Product,
$a in input()/Product
where $b/RecommendedProducts/RecommendedProduct/@Sku = $a/@Sku and $b/@Sku = 012
return $a
sort by ($b/RecommendedProducts/RecommendedProduct/@Sequence)

The query works interrms of fetching the records but not with sortby

I am new to xquery . Could this be done ? and any help will be much appreciated
Thanks
Dan

#Tamino
#API-Management
#webMethods

The reason your query doesn’t work is that “sort by” is not a part of the FLOWR expression, and so in particular the use of $b in the sort expression refers to a variable no longer in scope. I think you want to use the “order by” clause within the FLOWR expression:

for $b in input()/Product,
$a in input()/Product
where $b/RecommendedProducts/RecommendedProduct/@Sku = $a/@Sku and $b/@Sku = 012
order by $b/RecommendedProducts/RecommendedProduct/@Sequence
return $a 

#API-Management
#webMethods
#Tamino

Thanks for your reply. “order by” doesn’t seem to work.

Error message is
Syntax Error at line 6, column 7: found “by” when expecting any of: “or”, “and”, “div”, “mod”, " *", “return”, “intersect”, “union”, “except”, “/”, “//”, “=”, “!=”, “<=”, “>=”, “eq”, “ne”, “gt”, “ge”, “lt”, “le”, "< ", “>”, “-”, “+”, “=>”, “|”, “(”, “[”, “sort”, “stable”

We are using Tamino XML Server 4.2.1. . I am not sure whether this version supports the order by functionality. Can anyone think of any other alternative?. Thanks in advance.

#Tamino
#API-Management
#webMethods

Yes, the “order by” clause was added in Tamino 4.4.

I think your best bet may be to “wrap” the result:

for $b in input()/Product,
$a in input()/Product
where $b/RecommendedProducts/RecommendedProduct/@Sku = $a/@Sku and $b/@Sku = 012
return <my-result>
<seq>{$b/RecommendedProducts/RecommendedProduct/@Sequence/text()}</seq>
{$a}</my-result>
sort by (my-result/seq) 

Then you’ll have to pull the actual answer out of the element in your client.

#API-Management
#webMethods
#Tamino

Hi,

Due to performance reasons I would suggest not to sort the result of an element construction. It would be better to do the sorting as early as possible. The according query looks like this:

for $b in input()/Product sort by ./RecommendedProducts/RecommendedProduct/@Sequence, 
$a in input()/Product 
where $b/RecommendedProducts/RecommendedProduct/@Sku = $a/@Sku and $b/@Sku = 012 
return $a

Best Regards,

Thorsten

#webMethods
#Tamino
#API-Management