Hello I’m working with tamino v4.2

Having the following document structure:

I’d like to perform a group by operation

Sql syntax

select username, count(username)
from log
where description = “App search” and date like “%06/2008%”
group by username

How the sql query above would be in xquery?

I’ve tried the following one but count operation returns always 0 and I don’t know why …

declare namespace tf=“http://namespaces.softwareag.com/tamino/TaminoFunction”

let $a := (
for $b in log
where (($b/description = “App search”) and (tf:containsText($b/date,“???06/2008*”)))
return $b
)
for $d in distinct-values($a/username/text())
return


{$d}

{
let $z := (for $c in input()/log
where (
($c/description = “App search”) and (tf:containsText($c/date,“???06/2008*”))
and ($c/username = “$d/text()”)
)

   return $c
)return
<cont>
{count($z)}
</cont>

}

Any help would be much appreciate.

Thanks in advance.

Hi,

staying as close as possible to your query, you would have to modify as follows:

declare namespace tf="http://namespaces.softwareag.com/tamino/TaminoFunction"

let $a := (for $b in input()/log
where (($b/description = "App search") and    
(tf:containsText($b/date,"???06/2008*")))
return $b)
for $d in distinct-values($a/username/text())
return
<usu>
<nom>{$d}</nom>
{
let $z := (for $c in input()/log
where ($c/description = "App search") and  
tf:containsText($c/date,"???06/2008*")
and ($c/username = $d)
)
return $c
)
return  <cont>{count($z)}</cont>
}
</usu> 

actually only minor changes (in particular, you cannot apply /text() to $d as $d is already a text node, and you must not quote $d)
The query could be written more compact as

declare namespace tf="http://namespaces.softwareag.com/tamino/TaminoFunction"
let $log:=input()/log[./description = "App search" and
tf:containsText(./date,"???06/2008*")]
for $d in distinct-values ($log/username/text()) return
<usu>
<nom>
{$d}
</nom>
<cont>
{count($log[username=$d])}
</cont>
</usu> 

regards

harald

hi,
although there is an example i can’t create ‘group-by’-statment in tamino.

i’f got a xml like:

<files>
<file>
<owner></owner> 
<name></name> 
<size></size> 
<extension></extension> 
<creationTime></creationTime> 
<lastWriteTime></lastWriteTime> 
<lastAccessTime></lastAccessTime> 
<path></path> 
<parentDirectory></parentDirectory> 
<rootDirectory></rootDirectory> 
</file>
</files>

now i’m trying to get a ‘group-by’-stamtement like :

select count(name), name
from   files
group by name

my XQuery-statement looks like:

let $a := input()/files
for $d in distinct-values($a/file/name/text()) return ($d, count($a[file/name=$d]))

does anyone see the error?!?

Thanks for help.

Strange, today it works.

try

let $a := input()/files
for $d in distinct-values($a/file/name/text()) return ($d, count($a/file[name=$d]))