Sql group by equivalence in Tamino XQuery

, ,
1

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.

2

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

3

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.

4

try


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