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

    Unanswered: Insert Into using subquery

    Please Help!

    I created a select query that joins 4 tables and now I want to wrap it inside an INSERT INTO to append that table with a new work order (I will create a job to run each day to automate the process).
    Thanks,
    Lee


    Here is what the subquery returns
    LineNum Dept EquipType PMType
    5 1234 Cap CarrierChain


    The error message I get is as follows:

    Server: Msg 1046, Level 15, State 1, Line 3
    Subqueries are not allowed in this context. Only scalar expressions are allowed.
    Server: Msg 170, Level 15, State 1, Line 5
    Line 5: Incorrect syntax near 'otbl_o_d_ProductionInfo'.
    Server: Msg 156, Level 15, State 1, Line 7
    Incorrect syntax near the keyword 'Sum'.


    The complete query is as follows:

    INSERT INTO WorkOrderBak
    (LineNum, Dept, EquipType, PMType, Scheduled, ScheduledDate, AssignedDate)
    VALUES (

    (SELECT tbl_Mertering.LineNum, tbl_Mertering.Dept, tbl_Mertering.EquipType, tbl_Mertering.PMType

    FROM (tbl_StationHistory INNER JOIN (tbl_Mertering INNER JOIN tbl_ProductionInfo
    ON (tbl_Mertering.LineNum = tbl_ProductionInfo.LineNum) AND (tbl_Mertering.Dept = tbl_ProductionInfo.Dept))

    ON tbl_StationHistory.EquipType = tbl_Mertering.EquipType) INNER JOIN tbl_WorkOrder ON (tbl_Mertering.PMType = tbl_WorkOrder.PMType) AND (tbl_Mertering.LineNum = tbl_WorkOrder.LineNum)AND tbl_Mertering.EquipType = tbl_WorkOrder.EquipType

    WHERE (((tbl_ProductionInfo.EntryDate)>=[tbl_Mertering].[dateofchange]) AND ((tbl_StationHistory.CreateWO)=1))AND (tbl_WorkOrder.CompletionDate is null)

    GROUP BY tbl_Mertering.PMType, tbl_Mertering.EquipType, tbl_Mertering.ItemDesc, tbl_Mertering.Dept, tbl_Mertering.DateOfChange, tbl_Mertering.LineNum, tbl_Mertering.UD2

    HAVING (((Sum(tbl_ProductionInfo.Production))>(0.05*Max([tbl_StationHistory].[Lifecycle]))) AND ((tbl_Mertering.UD2)=0))
    ),

    'Yes', GETDATE(), GETDATE())

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Can't test this but give it a go...

    Code:
    INSERT INTO WorkOrderBak (LineNum, Dept, EquipType, PMType, Scheduled, ScheduledDate, AssignedDate)
    SELECT tbl_Mertering.LineNum, tbl_Mertering.Dept, tbl_Mertering.EquipType, tbl_Mertering.PMType, 'Yes', GETDATE(), GETDATE()
      FROM tbl_StationHistory 
     INNER JOIN tbl_Mertering ON tbl_StationHistory.EquipType = tbl_Mertering.EquipType
     INNER JOIN tbl_ProductionInfo ON tbl_Mertering.LineNum = tbl_ProductionInfo.LineNum 
                                  AND tbl_Mertering.Dept = tbl_ProductionInfo.Dept
     INNER JOIN tbl_WorkOrder ON tbl_Mertering.PMType = tbl_WorkOrder.PMType
                             AND tbl_Mertering.LineNum = tbl_WorkOrder.LineNum
                             AND tbl_Mertering.EquipType = tbl_WorkOrder.EquipType
     WHERE tbl_ProductionInfo.EntryDate >= tbl_Mertering.dateofchange 
       AND tbl_StationHistory.CreateWO = 1 
       AND tbl_WorkOrder.CompletionDate is null
     GROUP BY tbl_Mertering.PMType, tbl_Mertering.EquipType, tbl_Mertering.ItemDesc, tbl_Mertering.Dept, tbl_Mertering.DateOfChange
         , tbl_Mertering.LineNum, tbl_Mertering.UD2
    HAVING Sum(tbl_ProductionInfo.Production) > (0.05 * Max(tbl_StationHistory.Lifecycle)) 
       AND tbl_Mertering.UD2 = 0
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    Thanks Paul!!

    That did the trick, but it is back to the drawing board as the sub query that I thought was working correctly is not.

    I will try and work that out.

    Thanks again!

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


    I have tried to get the sub query to work with the changes, but it does not work properly with the changes. As long as it has the parenthesis, it works fine.

    The flow for the query should start by
    looking in the StationHistory table to find out what items are metered (measured by production) and what is the lifecycle of the item.

    Then it looks to tbl_Mertering for the last time the item was changed.

    Then it goes to tbl_ProductionInfo and sums up production from that date forward For that line dept and equiptype.

    Then it looks to WorkOrderBak to see if a work order has been created fot this item. It does so by using CompletionDate is Null.
    (this is a test table that I created from tbl_WorkOrder and where I want the test data to go until the program is debugged and yes I failed to change the name is the join script in the original)

    In the original, I used the CompletionDate is Null in the WHERE statement. In your updated one, I tried moving it to the HAVING statement and it still did not work.

    The question: Is there a way to miminc the way that I had the sub query structured and still use it in a update?

    Thanks,
    Lee

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Lee okay, I got carried away with the clean up...

    The closes code I can think of that would be similar to your original post is
    Code:
    INSERT INTO WorkOrderBak (LineNum, Dept, EquipType, PMType, Scheduled, ScheduledDate, AssignedDate)
    select t1.LineNum, t1.Dept, t1.EquipType, t1.PMType, 'Yes', GETDATE(), GETDATE()
      from (SELECT tbl_Mertering.LineNum, tbl_Mertering.Dept, tbl_Mertering.EquipType, tbl_Mertering.PMType
              FROM (tbl_StationHistory INNER JOIN (tbl_Mertering INNER JOIN tbl_ProductionInfo 
                ON (tbl_Mertering.LineNum = tbl_ProductionInfo.LineNum) AND (tbl_Mertering.Dept = tbl_ProductionInfo.Dept)) 
                ON tbl_StationHistory.EquipType = tbl_Mertering.EquipType) INNER JOIN tbl_WorkOrder ON (tbl_Mertering.PMType = tbl_WorkOrder.PMType) AND (tbl_Mertering.LineNum = tbl_WorkOrder.LineNum)AND tbl_Mertering.EquipType = tbl_WorkOrder.EquipType
             WHERE (((tbl_ProductionInfo.EntryDate)>=[tbl_Mertering].[dateofchange]) AND ((tbl_StationHistory.CreateWO)=1))AND (tbl_WorkOrder.CompletionDate is null)
             GROUP BY tbl_Mertering.PMType, tbl_Mertering.EquipType, tbl_Mertering.ItemDesc, tbl_Mertering.Dept, tbl_Mertering.DateOfChange, tbl_Mertering.LineNum, tbl_Mertering.UD2
            HAVING (((Sum(tbl_ProductionInfo.Production))>(0.05*Max([tbl_StationHistory].[Lifecycle]))) AND ((tbl_Mertering.UD2)=0))
           ) as t1
    The error

    Server: Msg 1046, Level 15, State 1, Line 3
    Subqueries are not allowed in this context. Only scalar expressions are allowed.

    refers to the VALUES (Select...), 'YES'... by replacing this with a derived table that error should go away

    Server: Msg 170, Level 15, State 1, Line 5
    Line 5: Incorrect syntax near 'otbl_o_d_ProductionInfo'.

    I think you fixed this one.

    Server: Msg 156, Level 15, State 1, Line 7
    Incorrect syntax near the keyword 'Sum'.

    Not too sure about this one, although I suspect it's taken care of with the above code.

    I personnaly would have solved this with multipule derived tables as I think it is easier to maintain.
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    Paul,
    Thanks for your patience!!

    It still does not like something as it is still not recongizing the newly created work order (CompletionDate is Null).

    My use is limited and I am still trying to learn. Could you briefly explain derived tables once again? I would like to try that if it easier to maintain.

    Thanks,
    Lee

  7. #7
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Lee This is not very well explain in Books Online...

    The last post had a derived table, your query as t1 + 'YES',GETDATE(),GETDATE().

    Derived tables are nothing more than using the result set of a query as a temporary table.

    You could write a query to solve "looking in the StationHistory table to find out what items are metered (measured by production) and what is the lifecycle of the item." wrapped in () aliased as t1 returning only the keys nneded to identify the target rows.

    This would be joined with another derived query to solve "Then it looks to tbl_Mertering for the last time the item was changed.", again returning only the keyes needed to identify the target records.

    This continues until you have a resultset that you can use to identify the records in tbl_Mertering that you are looking for. If you calculate a sum in a derivedx table and need it in the final result set just carry it along in each subsiquent select.

    Clear as mud?

    I can attemp an example based on your code but I suspect it would be buggy.
    Paul Young
    (Knowledge is power! Get some!)

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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