Home » SQL & PL/SQL » SQL & PL/SQL » HAVING Clause
HAVING Clause [message #276660] Thu, 25 October 2007 21:22 Go to next message
someone_somebody
Messages: 5
Registered: September 2007
Junior Member
Hi,

When I run my SQL statement without the 'HAVING' clause, it returns a result. However, if I include the 'HAVING' clause, no result is returned.

I hope you could provide me with some assistance if my SQL statement is wrong. I am using Oracle 10g.

Thank you.


My Statement:

SELECT
b.course_code, a.tutgrp, a1.name, b.adm_no, b.pgm_code, b.basket_code, b.module_code, c.module_abbr, b.module_grp, b.repeat_cnt, b.module_status, count(b.module_code)

FROM
student_courses a, student_particulars a1,
student_study_pgms b, modules c, module_offered d

WHERE
c.module_code = d.module_code
and c.module_version = d.module_version
and d.module_code = b.module_code
and d.acad_yr = b.acad_yr
and d.semester = b.semester
and a.studstatus = 'E'
and b.adm_no = a.adm_no
and a.course_code = b.course_code
and a1.adm_no = b.adm_no
and &P_where

GROUP BY
b.course_code, a.tutgrp, a1.name, b.adm_no, b.pgm_code, b.basket_code, b.module_code, c.module_abbr, b.module_grp, b.repeat_cnt, b.module_status

--HAVING count(b.module_code) >= :P_REG
Re: HAVING Clause [message #276661 is a reply to message #276660] Thu, 25 October 2007 21:39 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You REALLY should read & FOLLOW posting guidelines as found in following URL:
http://www.orafaq.com/forum/t/88153/0/

>When I run my SQL statement without the 'HAVING' clause, it returns a result.

But you decided that we did not need to see or know the result.

>However, if I include the 'HAVING' clause, no result is returned.

OK, I believe you.

>I hope you could provide me with some assistance if my SQL statement is wrong.

SQL always returns a result set that meets SELECT criteria.

How is anyone to conclude if the SQL is wrong or not?
We don't know the data in the table.
We don't know what value "P_REG" had.
We don't know what result you expected.

It is YOUR data & your SELECT and YOU get to decide if the result set contains the records you expect.
If not, then changes the SQL until you get what you desire.

P.S.
>, module_offered d
Why is line above part of FROM clause when it contributes NO data to the SELECT clause?
It can & should be eliminated out of the FROM clause.
If filtering the result set depends upon table MODULE_OFFERED,
then it should be subordinated into the WHERE clause.

[Updated on: Thu, 25 October 2007 22:05] by Moderator

Report message to a moderator

Previous Topic: dbms_scheduler - procedure with parameters
Next Topic: how to display the columnname with values from the table (merged)
Goto Forum:
  


Current Time: Fri Dec 09 15:41:01 CST 2016

Total time taken to generate the page: 0.08623 seconds