Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: Does HAVING clause accept wildcards?

    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?

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    Which two of the following statements are true about the HAVING clause?

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Jun 2009
    Posts
    3
    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.

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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)

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jun 2009
    Posts
    3
    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.

  9. #9
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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:
    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.

  10. #10
    Join Date
    Oct 2009
    Posts
    5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •