sql expression field "having clause"

how to use a "having clause" in sql expression fields for crystal report 10(vs.net 2005)?
vielkacarolina1239Asked:
Who is Participating?
 
Kurt ReinhardtConnect With a Mentor Sr. Business Intelligence Consultant/ArchitectCommented:
The reason this query doesn't work is because it returns more than one column.  Once again, because a SQL Expression is a subquery in the SELECT clause of the main query generated by the report, it can only return a single distinct value.  Here's  a sample of the SQL that Crystal generates when a SQL Expression is created:

SELECT
  `ENTITY`.`ENTITY`,
  `PRACENT`.`NAME`,
  `PRACENT`.`PRACNO`,
  (
  SELECT DISTINCT
    'Y'
  FROM
    SUBSTAN
  WHERE
    PRACNO = PRACENT."PRACNO"
    AND PAST = 'N'
    AND ADDR1 <> 'INQY'
  )
FROM
  `database`.`dbo`.`ENTITY` `ENTITY`
  INNER JOIN `database`.`dbo`.`PRACENT` `PRACENT` ON `ENTITY`.`ENTITY`=`PRACENT`.`ENTITY`
ORDER BY
  `ENTITY`.`ENTITY`

The part in parentheses is the SQL Expression field.  Notice, it only returns a single, distinct value for each record.  If you need multiple values, then you need multiple SQL Expression fields.

~Kurt

0
 
ramumorlaCommented:
Hey This is Oracle 10.x group.............
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
If you don't already know, a SQL Expression is essentially a subquery (either correlated or uncorrelated) in the SELECT clause that Crystal generates as part of it's SQL (Database|Show SQL Query).  As such, you must return a single, distinct value for the SQL Expression field.  Here's a sample of syntax I would use (Oracle SQL Server databases require the parentheses around the SQL in the SQL Expression editor):

(
SELECT DISTINCT
  field
FROM
  table
WHERE
  condition
)

If you want a HAVING clause, then you would use a statement like this:

(
SELECT DISTINCT
  field
FROM
  table
WHERE
  condition
GROUP BY
  field
HAVING
  COUNT(field) > 1
)

~Kurt
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
vielkacarolina1239Author Commented:
so let say the first sql statement have been generated by crystal if i want a having clause i just need to write a subquery and include my where clause along with the having clause. I already provided a where clause which is weekending=(select max(weekending) from table ) now I need to include a having clasue after this, I cannot put it together because weekending=........ represent just the where clause, and i tried creating a subquery with the two sql statement and it gives me the error(to many values).

(select sum("DIGITAL_MARKETING"."CURRENT_WEEK_UNITS"),sum("DIGITAL_MARKETING"."PRIOR_WEEK_UNITS")
 from "DIGITAL_MARKETING"
 having sum("DIGITAL_MARKETING"."CURRENT_WEEK_UNITS")>0 or sum("DIGITAL_MARKETING"."PRIOR_WEEK_UNITS")>0)
0
 
frodomanCommented:
vielkacarolina1239,

Can you clarify if this is really a sql expression field?  If so, then as rhinok pointed out you must return a single result.  You're selecting two aggregate values coming back - if you want to do that you'll need to use two sql expression fields and split this into two parts...

frodoman
0
 
RainMan82Commented:
use a group by in order to use a having....
0
 
vielkacarolina1239Author Commented:
I already have a field expression for the weekending=(select max(weekending) from table), so i would have to split the above query in order to get what I want.
0
 
vielkacarolina1239Author Commented:
Thanks for the help really appreciate it.
0
All Courses

From novice to tech pro — start learning today.