|HAVING Clause [message #276660]
||Thu, 25 October 2007 21:22
Registered: September 2007
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.
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)
student_courses a, student_particulars a1,
student_study_pgms b, modules c, module_offered d
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
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
Registered: January 2009
You REALLY should read & FOLLOW posting guidelines as found in following URL:|
>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.
>, 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