Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123

    Unanswered: Join with a Having clause -- having problems too

    Could someone please help me with a query that I am trying to create or suggest a better way?

    What I am trying to do is is sum the production information (tbl_ProductionInfo) that is greater than the last date a particular task was done (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) and the production has met the quanity ran (Sum(tbl_ProductionInfo.Production)>=Max(tbl_Merte ring.lifecycle)).

    When I put this critera (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) in the Where clause I get an error "An aggregate may not appear in a Where unless it is in a subquery contained in a Having...etc."



    SELECT DISTINCT tbl_ProductionInfo.LineNum, tbl_ProductionInfo.Dept, tbl_ProductionInfo.EquipType, Sum(tbl_ProductionInfo.Production) AS SumOfProduction, tbl_Mertering.PMType

    FROM tbl_ProductionInfo LEFT JOIN tbl_Mertering ON tbl_ProductionInfo.EquipType = tbl_Mertering.EquipType

    WHERE tbl_Mertering.DateOfChange>=tbl_ProductionInfo.Ent ryDate AND tbl_Mertering.UD2=0 AND tbl_ProductionInfo.LineNum= tbl_Mertering.LineNum AND tbl_ProductionInfo.EquipType= tbl_Mertering.EquipType

    GROUP BY tbl_ProductionInfo.LineNum, tbl_ProductionInfo.Dept, tbl_ProductionInfo.EquipType, tbl_Mertering.PMType

    HAVING Sum(tbl_ProductionInfo.Production)>=Max(tbl_Merter ing.lifecycle)

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Join with a Having clause -- having problems too

    Can you post the DDL for the tables?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    Sorry for the late reply as I have been in meetings all afternoon and please excuse my ingorance, but I am not sure what the DDL is.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    DDL: Data Definition Language.

    The SQL Statements that can be used to create the tables and objects involved in your problem, or at least the relevant parts.

    Brett is asking for more information on your table design.

    blindman

  5. #5
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123

    more history and table structure

    Here is a little more history.
    The production table already existed. I am attempting to create a "meterting" scheduler for our home grown CMMS. We want to create workorders (table that already exists) based on the amount of production that has occurred. I created the metering table to holds the date that work is done. I want to keep each record for historical data.

    The flow of the program is
    User completes the existing work order; this action creates a record in the metering table; DateOfChange is populated UD2 defaults to 0.

    Each day the job runs that looks for Metering records with UD2 = 0 and sums the production for that line, equiptype, dept, ItemDesc. If the sum is greater >= LifeCycle then I write a new work order and change the UD2 =1.

    I run this job as an active X (because I am weak in SQL). The writing of the work order and the changing of UD2 works fine. I just can't get the sum right because it is not picking up the >= Max(DateOfChange).

    Here is the table structure.


    tbl_ProductionInfo Columns
    Name Type Size
    ProdID int (autonumber) 4
    EntryDate Date/Time 8
    LineNum Text 10
    Shift Text 10
    SubEmp Text 35
    Dept Text 10
    EquipType Text 35
    ProductType Text 10
    ContNum int 4
    Production int 4
    ScheduledTime int 4
    OnHold int 4
    Speed int 4
    Potential int 4
    PM int 35
    EditedBy Text 35
    DateCode Text 15
    Employee Text 35
    UtilTime int 4
    Util float 8

    tbl_metering Name Type Size
    Id int (autonumber) 4
    Dept Text 35
    EquipType Text 35
    LineNum Text 2
    Station Text 35
    ItemDesc Text 35
    LifeCycle int 4
    DateOfChange smalldate 8
    Comments Text 250
    PMType Text 40
    UD2 int 4
    CreateWo int 4

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    To start with, rewrite your query like this:

    SELECT tbl_ProductionInfo.LineNum,
    tbl_ProductionInfo.Dept,
    tbl_ProductionInfo.EquipType,
    Sum(tbl_ProductionInfo.Production) AS SumOfProduction,
    tbl_Mertering.PMType
    FROM tbl_ProductionInfo
    inner join tbl_Mertering
    ON tbl_ProductionInfo.EquipType = tbl_Mertering.EquipType
    and tbl_ProductionInfo.EntryDate <= tbl_Mertering.DateOfChange
    AND tbl_ProductionInfo.LineNum = tbl_Mertering.LineNum
    AND tbl_ProductionInfo.EquipType= tbl_Mertering.EquipType
    WHERE tbl_Mertering.UD2=0
    GROUP BY tbl_ProductionInfo.LineNum,
    tbl_ProductionInfo.Dept,
    tbl_ProductionInfo.EquipType,
    tbl_Mertering.PMType
    HAVING Sum(tbl_ProductionInfo.Production)>=Max(tbl_Merter ing.lifecycle)

    DISTINCT is not need in GROUP BY queries, and your LEFT JOIN is superfluous when you are matching records in the WHERE clause.

    Now to your problem...
    Does tbl_Mertering hold a history of values, differentiated by DateOfChange, or is DateOfChange just updated every time a record is modified?

    blindman

  7. #7
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    A new record is written and DateOfChange is added for each record so that I maintain a record of the date the PM was done. The reason for this is that there may be delay in when work is actually done. With the historic data, we can tell what the average production and/or actual production between PM's is.
    Last edited by clinel; 10-21-03 at 19:51.

  8. #8
    Join Date
    Oct 2003
    Posts
    706

    Lightbulb Re: Join with a Having clause -- having problems too


    What I am trying to do is is sum the production information (tbl_ProductionInfo) that is greater than the last date a particular task was done (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) and the production has met the quanity ran (Sum(tbl_ProductionInfo.Production)>=Max(tbl_Merte ring.lifecycle)).
    [I can't speak to whether the above Max() code, etc, is appropriate or correct. I simply include it as part of a quote. In fact I don't think it is...]

    I suggest that you first construct a query which returns "the production information that is greater than the last date..." This query will return all rows.

    Then build a second query which is based on the first (i.e. it takes input from the first), and does the sum.

    That combination is clear, easy to understand, and also easy to prove/audit by desk checking. Furthermore, when you go to run the combined query, the DBMS will automatically consider both queries in combination when building the overall execution plan.
    Last edited by sundialsvcs; 10-21-03 at 22:30.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  9. #9
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    Yes! I do need to approach it differently as I continue to get an incorrect sum of production.

    I had thought that I could return a recordset with all the records in the metering table with UD2 = 0 and then with active x loop through the recordset with a second query that would sum the production based on the critera (line, date, equiptype, etc) and sum >= LifeCycle.

    I had just hoped that I could learn a cleaner way.
    Thanks,
    Lee

  10. #10
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123

    It appears to be working!!

    My BAD!

    I missed the signing
    tbl_ProductionInfo.EntryDate <= tbl_Mertering.DateOfChange to
    tbl_ProductionInfo.EntryDate >= tbl_Mertering.DateOfChange
    In the INNER JOIN critera.

    Also I found that that collects the production data was not putting the right equipment type in the column. I corrected that as well and now IT SEEMS to be working fine.



    Thanks to all for your help!
    You are the best!

Posting Permissions

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