"having sum" clause after group by

building semantic layer
1

I need to generate the following sql with the ‘having sum’ after the group by clause. Can I force BO to generate a having sum? My database is ingres and it does support having sum.

SELECT
  ias.orders.div_responsible,
  count(distinct ias.orders.pro_num),
  sum(ias.revenue_detail.revenue_amount),
  min('<a href="http://bodev.winhq.blue.fedex.com/wijsp/scripts/openDocument.jsp?sDocName=pickuprevenue_detail&amp;sType=R&amp;iDocID=27&amp;IsSdiv_responsible?='+ias.orders.div_responsible+'">'+ias.orders.div_responsible+'</a>'),
  ias.division.division_name
FROM
  ias.revenue_detail,
  ias.orders,
  ias.division
WHERE
  ( ias.orders.uid_ord=ias.revenue_detail.uid_ord  )
  AND  ( ias.revenue_detail.division_id=ias.orders.div_responsible  )
  AND  ( ias.orders.div_responsible=ias.division.division_id  )
  AND  (
  ias.orders.date_initial_pickup  >=  date('today')
  AND  ( ias.orders.status = '2'  )
  AND  ( (abs(ias.revenue_detail.revenue_amount) > 0)  )
  AND  ( (ias.revenue_detail.p_comp_override_flag = ('N')  OR (ias.revenue_detail.p_comp_override_flag = ('Y')  AND ias.revenue_detail.power_comp_pct   != 100.0))  )
  )
GROUP BY
  ias.orders.div_responsible, 
  ias.division.division_name
 having sum(ias.revenue_detail.revenue_amount) > 0

:hb:

[added bbc CODE formatting - Andreas]

larryc (BOB member since 2004-04-15)

2

If you create a condition on a measure it will be part of to having clause.

reemagupta (BOB member since 2002-09-18)

3

I created the condition directly on the measure object (in the where clause) and all it did was add it to my where clause in the resulting sql. :frowning:

I also added a separate condition object and added it to the report and it did the same thing?

What am I doing wrong… man am I glad it’s Friday!!

larryc (BOB member since 2004-04-15)

4

Is your measure object defined as: [b]SUM/b ??

Anita Craig :us: (BOB member since 2002-06-17)

5

yep…

Measure defined as follows:

sum(ias.revenue_detail.revenue_amount)

Also, I am using version 6.1A

larryc (BOB member since 2004-04-15)

6

anymore sugguestions?

larryc (BOB member since 2004-04-15)

7

You need to create the condition in the query in reporter, not designer. To your conditions box add your measure then add >0.

Lisa

Lisa Bates :us: (BOB member since 2002-08-15)

8

Thank You… That did it…

It’s the little things that really cause you to refill your prescriptions!

larryc (BOB member since 2004-04-15)