ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

AS doesn't seem to be working

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • AS doesn't seem to be working

    I have this sql query string, but it says that ACTHRSSUM is not in any of the specified tables... Can't I used a summed column in a where clause?

    Code:
    select sum(acthrs) as acthrssum, workdate, lcrwc, hperday from    
    bmartin/lcrdump join bmartin/wcdump on lcrwc = workcenter where   
    workdate between '1968-11-21' and '2008-11-29' and acthrssum > hperday 
    group by workdate, lcrwc, hperday order by lcrwc, workdate
    I want to know where the sum of acthrs is greater than a workcenter's 'hperday'.
    Your future President
    Bryce

    ---------------------------------------------
    http://www.bravobryce.com

  • #2
    Re: AS doesn't seem to be working

    Hi,

    Correlation Names (in your case acthrssum) cannot be used within the same sub-query. A sub-query may consist of SELECT, FROM, WHERE, GROUP BY and HAVING clauses. ORDER BY is outside the sub-query, that's why correlation names can be used in an ORDER BY clause.

    Aggregat functions are not allowed with in a where clause, instead a having clause must be used.

    In this way rewrite your query as follows:
    PHP Code:
    select   sum(acthrs) as acthrssumworkdatelcrwchperday 
       from  bmartin
    /lcrdump join bmartin/wcdump on lcrwc workcenter 
       where workdate between 
    '1968-11-21' and '2008-11-29' 
       
    having sum(acthrs) > hperday 
       group by workdate
    lcrwchperday 
       order by lcrwc
    workdate 
    Birgitta

    Comment


    • #3
      Re: AS doesn't seem to be working

      It doesn't like the group by after the Having clause. I get an SQL Syntax error that says... Keyword GROUP not expected. Valid tokens: FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE.

      What am I missing?
      Your future President
      Bryce

      ---------------------------------------------
      http://www.bravobryce.com

      Comment


      • #4
        Re: AS doesn't seem to be working

        The HAVING has to come AFTER the GROUP BY Fixed it, working like a charm. Thanks again B
        Your future President
        Bryce

        ---------------------------------------------
        http://www.bravobryce.com

        Comment


        • #5
          Re: AS doesn't seem to be working

          I want to now count the number of rows that are returned by this query... How would I do that? A simple Count() won't work will it? I tried it, but it didn't work...
          Your future President
          Bryce

          ---------------------------------------------
          http://www.bravobryce.com

          Comment


          • #6
            Re: AS doesn't seem to be working

            Hi,

            try the following (this time with group and having in the correct sequence):

            PHP Code:
            With x as (select   sum(acthrs) as acthrssumworkdatelcrwchperday 
                         from  bmartin
            /lcrdump join bmartin/wcdump on lcrwc workcenter 
                         where workdate between 
            '1968-11-21' and '2008-11-29' 
                         
            group by workdatelcrwchperday
                         having sum
            (acthrs) > hperday )
            Select count(*)
            from x 
            Birgitta

            Comment


            • #7
              Re: AS doesn't seem to be working

              Brilliant. Thank Birgitta.
              Your future President
              Bryce

              ---------------------------------------------
              http://www.bravobryce.com

              Comment

              Working...
              X