How to add a size limit?

Nina_Lewau_(sablinadmin_-_(deleted))

we're using a LiveReport from Knowledge where we get the size of users Personal Workspace. We would like to limit the result by only listing the Personal Workspace that are over 200 MB. You cen here first see the correct report, and then what we tried to do that doesn't work - what's wrong?Correct:select c.id, c.name, c.firstname, c.lastname, sum (b.datasize)/1048576 "Personal Folder Size in MB" from kuaf c, dtree a, dversdata b where c.groupid is not null and c.deleted = 0 and a.ownerid = -(c.id) and a.parentid >= c.id and a.dataid = b.docid group by c.id, c.name, c.firstname, c.lastname order by sum (b.datasize)/1048576 desc What's wrong?select c.id, c.name, c.firstname, c.lastname, sum (b.datasize)/1048576 "Personal Folder Size in MB" from kuaf c, dtree a, dversdata b where c.groupid is not null and c.deleted = 0 and a.ownerid = -(c.id) and a.parentid >= c.id and a.dataid = b.docid AND (sum (b.datasize)/1048576)>200 group by c.id, c.name, c.firstname, c.lastname order by sum (b.datasize)/1048576 desc

Filippo_Bianchini_(x-fbianchini_-_(deleted))

Message from Filippo Bianchini via eLinkthe error is using the sum in the where clause.You can't have aggregates in the WHERE clause, only in the HAVING clause. Ifyou put something in the WHERE clause of an object in Designer, then itstays in the WHERE clause. What you have is not valid SQLtry thisselect c.id, c.name, c.firstname, c.lastname, sum (b.datasize)/1048576"Personal Folder Size in MB" from kuaf c, dtree a, dversdata b wherec.groupid is not null and c.deleted = 0 and a.ownerid = -(c.id) anda.parentid >= c.id and a.dataid = b.docid group by c.id, c.name,c.firstname, c.lastnamehaving sum(b.datasize)/1048576 >200order by sum (b.datasize)/1048576 desc-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: lunedì 23 agosto 2004 10:08To: eLink RecipientSubject: How to add a size limit?How to add a size limit?Posted by Englund, Nina on 08/23/2004 04:03 AMwe're using a LiveReport from Knowledge where we get the size of usersPersonal Workspace. We would like to limit the result by only listing thePersonal Workspace that are over 200 MB. You cen here first see the correctreport, and then what we tried to do that doesn't work - what's wrong?Correct:select c.id, c.name, c.firstname, c.lastname, sum (b.datasize)/1048576"Personal Folder Size in MB" from kuaf c, dtree a, dversdata b wherec.groupid is not null and c.deleted = 0 and a.ownerid = -(c.id) anda.parentid >= c.id and a.dataid = b.docid group by c.id, c.name,c.firstname, c.lastname order by sum (b.datasize)/1048576 descWhat's wrong?select c.id, c.name, c.firstname, c.lastname, sum (b.datasize)/1048576"Personal Folder Size in MB" from kuaf c, dtree a, dversdata b wherec.groupid is not null and c.deleted = 0 and a.ownerid = -(c.id) anda.parentid >= c.id and a.dataid = b.docid AND (sum (b.datasize)/1048576)>200group by c.id, c.name, c.firstname, c.lastname order by sum(b.datasize)/1048576 desc[To reply to this thread, use your normal E-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe "Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, è pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto" "This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person."