Results 1 to 8 of 8

Thread: LIKE problem

  1. #1
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178

    Question Unanswered: LIKE problem

    Is it possible to use LIKE in HAVING instead of WHERE?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oracle's SQL allows it ... For example,
    Code:
    select p.zone, count(*)
    from customer p
    group by p.zone
    having p.zone like '2%';
    
        ZONE   COUNT(*)
    ---------- -------------
             2            5503

  3. #3
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    Originally posted by Littlefoot
    Oracle's SQL allows it ... For example,
    Code:
    select p.zone, count(*)
    from customer p
    group by p.zone
    having p.zone like '2%';
    
        ZONE   COUNT(*)
    ---------- -------------
             2            5503
    thanks i actually got it to work using:
    '*Health*'
    instead of
    '%Health%'

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What is the SQL statement you are using this with? Since HAVING is meant for use on aggregate functions it is unusual to find it being used with a character string.

  5. #5
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    Originally posted by andrewst
    What is the SQL statement you are using this with? Since HAVING is meant for use on aggregate functions it is unusual to find it being used with a character string.
    SELECT Count(MetricEval.AlternateIn) AS CountOfAlternateIn FROM (MetricEval INNER JOIN (Targets INNER JOIN Employee ON Targets.EmpName = Employee.EmpName) ON MetricEval.EmpName = Employee.EmpName) INNER JOIN Metrics ON (Targets.Measure = Metrics.Measure) AND (MetricEval.Measure = Metrics.Measure) HAVING ((MetricEval.Measure Like '*House*') AND (MetricEval.Month = #" & EndOfMonth & "#) AND (Employee.Supervisor = '" & supName & "') AND ((MetricEval.AlternateIn = 'G') OR (MetricEval.AlternateIn = 'E')));

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    That could be modified to:

    SELECT Count(MetricEval.AlternateIn) AS CountOfAlternateIn FROM (MetricEval INNER JOIN (Targets INNER JOIN Employee ON Targets.EmpName = Employee.EmpName) ON MetricEval.EmpName = Employee.EmpName) INNER JOIN Metrics ON (Targets.Measure = Metrics.Measure) AND (MetricEval.Measure = Metrics.Measure) WHERE ((MetricEval.Measure Like '*House*') AND (MetricEval.Month = #" & EndOfMonth & "#) AND (Employee.Supervisor = '" & supName & "') AND ((MetricEval.AlternateIn = 'G') OR (MetricEval.AlternateIn = 'E')));

    This may not matter with your DBMS, but I know that in Oracle the WHERE clause can perform much better than the HAVING, because WHERE is evaluated on the rows before they are grouped and counted, HAVING afterwards.

  7. #7
    Join Date
    Jan 2004
    Location
    South Africa
    Posts
    178
    Originally posted by andrewst
    That could be modified to:

    SELECT Count(MetricEval.AlternateIn) AS CountOfAlternateIn FROM (MetricEval INNER JOIN (Targets INNER JOIN Employee ON Targets.EmpName = Employee.EmpName) ON MetricEval.EmpName = Employee.EmpName) INNER JOIN Metrics ON (Targets.Measure = Metrics.Measure) AND (MetricEval.Measure = Metrics.Measure) WHERE ((MetricEval.Measure Like '*House*') AND (MetricEval.Month = #" & EndOfMonth & "#) AND (Employee.Supervisor = '" & supName & "') AND ((MetricEval.AlternateIn = 'G') OR (MetricEval.AlternateIn = 'E')));

    This may not matter with your DBMS, but I know that in Oracle the WHERE clause can perform much better than the HAVING, because WHERE is evaluated on the rows before they are grouped and counted, HAVING afterwards.
    thanks
    i am using Access

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by actionant
    thanks
    i am using Access
    Actually I'm surprised it even works. Since HAVING applies to the data after it has been grouped, all those columns in your HAVING clause don't even exist at that point, since they weren't selected and used in a GROUP BY. Oracle rejects such a query altogether:

    SQL> select count(*) from emp
    2 having deptno=10;
    having deptno=10
    *
    ERROR at line 2:

    ORA-00979: not a GROUP BY expression

    SQL> select count(*) from emp
    2 where deptno=10;

    COUNT(*)
    ----------
    3

Posting Permissions

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