Does HAVING clause accept wildcards?

un4dbf · #1 (**permalink**) 06-21-09, 22:10

Which two of the following statements are true about the HAVING clause?
A. The HAVING clause is used in place of the WHERE clause.
B. The HAVING clause uses the same syntax as the WHERE clause.
C. The HAVING clause can only be used with the GROUP BY clause.
D. The HAVING clause accepts wildcards.
E. The HAVING clause uses the same syntax as the IN clause.

I would say answer is B,C,D... DBAs do you agree?

I know B and C are 100% true... what abt D, Does HAVING clause accept wildcards?

umayer · #2 (**permalink**) 06-22-09, 01:16

Which two of the following statements are true about the HAVING clause?

Peter.Vanroose · #3 (**permalink**) 06-22-09, 02:41

Quote:

Originally Posted by un4dbf

D. The HAVING clause accepts wildcards.

The following is valid SQL:

Code:

SELECT col1, COUNT(*) FROM tbl GROUP BY col1
HAVING MIN(col2) LIKE 'A%'

(meaning, under some assumptions: only show groups that have at least an entry starting with an "A".)

Quote:

Originally Posted by un4dbf

E. The HAVING clause uses the same syntax as the IN clause.

The following is valid SQL:

Code:

SELECT col1, COUNT(*) FROM tbl GROUP BY col1
HAVING SUM(col3) IN (20, 30, 40)

un4dbf · #4 (**permalink**) 06-22-09, 11:22

So you mean, all below 3 are true?
B. The HAVING clause uses the same syntax as the WHERE clause.
C. The HAVING clause can only be used with the GROUP BY clause.
D. The HAVING clause accepts wildcards.

Peter.Vanroose · #5 (**permalink**) 06-22-09, 12:17

Quote:

Originally Posted by un4dbf

So you mean, all below 3 are true?
B. The HAVING clause uses the same syntax as the WHERE clause.
C. The HAVING clause can only be used with the GROUP BY clause.
D. The HAVING clause accepts wildcards.

Actually, I would personally go for C and D.
B is true, but to a lesser extent than D.

Stealth_DBA · #6 (**permalink**) 06-22-09, 18:39

I would go for B and C.

By 'uses the same syntax as the WHERE clause', that means you can use an IN clause or a Like in a compare

C is a definite. You need a Group By to use a Having clause as it filters the result of the Group By.

(It really is a poorly worded list of answers, however)

n_i · #7 (**permalink**) 06-22-09, 21:07

Quote:

Originally Posted by Stealth_DBA

I would go for B and C.

By 'uses the same syntax as the WHERE clause', that means you can use an IN clause or a Like in a compare

I doubt that. There are quite a few restrictions on what column references and functions you can use in the HAVING clause, especially if GROUP BY is also present. For that reason I would conclude that HAVING does _not_ use the same syntax as WHERE.

un4dbf · #8 (**permalink**) 06-22-09, 22:31

DB2 9 Fundamentals Certification Study Guide by Roger E. Sanders says B and C as correct answers. I totally disagree with this as i have personally coded HAVING clause with wildcards and it worked with out any issues.

Stealth_DBA · #9 (**permalink**) 06-22-09, 22:47

I think the question should either be removed or restructured. While I thought that "C. The HAVING clause can only be used with the GROUP BY clause." was a given (as I have only used or seen it used in that fashion), according to the DB2 V9.5 SQL Reference, Vol.1:

Quote:

When HAVING is used without GROUP BY, the select list can only include column names when they are arguments to an aggregate function, correlated column references, global variables, host variables, literals, special registers, SQL variables, or SQL parameters.

So, in certain situations, you do NOT have to have a GROUP BY when using a HAVING clause.

dinerroll1066 · #10 (**permalink**) 10-08-09, 14:54

I know this thread is old... but i couldn't resist:

C is false, while HAVING is typically used with a GROUP BY, using it without one will still work. The optimizer will treat the whole resultset as one group and do the filtering on that... effectively making the HAVING logically function like a where.

Internet Services

Web Development

Digital Marketing

Consumer Tech