CR 8.5 + Having Clause 1

[mastertorr]

I need to use a having clause in 8.5. I went to the Database > Show SQL Query, modified the select statement, but it wont save my changes. Is there another way to use a sql query in CR 8.5?

 

[lbass]

In 8.5 you can't modify the select and from clauses directly, but you could try creating a union statement like this:

select field1, field2, some number field
from table
where 1 = 0
union all
select field1, field2, sum(field3)
from table
where //etc.
group by //etc.
having // etc.

I'm not absolutely sure you can use "having" with a union all, but it might be worth a try.

However, I think you can get the same functionality just by using a group selection formula, e.g., go to report->edit selection formula->GROUP and enter:

sum({table.field},{table.group}) > 25000

-LB

 

[mastertorr]

Thank you LB for your quick reply, it is greatly appreciated. How would one go about adding a SQL statement in 8.5? I believe in Crystal 2008 you have the 'Add Statement/Command' option under Database explorer? But i dont see this in 8.5..

Here is my current query:
SELECT field1, count(field2)
FROM table
GROUP BY field1
HAVING COUNT(field2) > 1

 

[lbass]

I meant for you to use the existing "show SQL query" area for a union all--that means you have to have fields in the original report that correspond to fields in your actual new query. The where clause (1=0) cancels out the first half of the union all. But as I say, I'm not sure you can use a having clause with a union all.

-LB

 

[mastertorr]

Thanks again LB for your time and help. Happy holidays and all the best in 2010.