Syntax Error

• JimS-Indy

  SSCarpal Tunnel

  Points: 4514

  More actions

  October 5, 2011 at 9:30 am

  #249252

  I just can't identify the syntax error in the last line here (WHERE clause) Can anyone help?

  INSERT INTO tblBoMDetail

  (BoMID,

  BoMItemDescription,

  BoMEItemQty,

  BoMEItemfactor,

  BoMEItemUOM,

  BoMItemUnitMaterialCost,

  BoMItemUnitLaborHrs,

  BoMlaborComplexity,

  BoMvendorName,

  BoMvendorPartNbr,

  BoMItemID)

  SELECT @NewBoMID,

  ModelBoM.BoMItemDescription,

  ModelBoM.BoMEItemQty,

  ModelBoM.BoMEItemfactor,

  ModelBoM.BoMEItemUOM,

  ModelBoM.BoMItemUnitMaterialCost,

  ModelBoM.BoMItemUnitLaborHrs,

  ModelBoM.BoMlaborComplexity,

  ModelBoM.BoMvendorName,

  ModelBoM.BoMvendorPartNbr,

  ModelBoM.BoMItemID

  FROM (SELECT tblBoMDetail.* FROM tblBoMDetail JOIN qryActiveItemMaster ON tblBomDetail.BoMItemID=qryActiveItemMaster.ItemID WHERE tblBoMDetail.BoMID=@ModelBoMID) AS ModelBoM

  LEFT OUTER JOIN (SELECT tblBoMDetail.* FROM tblBoMDetail WHERE tblBoMDetail.BoMID=@NewBoMID) AS NewBoM

  WHERE NewBoM.BoMDetailID IS NULL

  Jim

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  October 5, 2011 at 9:37 am

  #1390771

  Rather than a select * in each of the subqueries, identify the specific columns and make sure the columns are aliased appropriately if necessary.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• JimS-Indy

  SSCarpal Tunnel

  Points: 4514

  More actions

  October 5, 2011 at 9:41 am

  #1390774

  Possibly good advice, but not the problem. When I changed it to select exactly that field (tblBoMDetail.BoMDetailID) that I needed, it still fails with the same syntax error

  Jim

• bkubicek

  SSChampion

  Points: 10948

  More actions

  October 5, 2011 at 9:41 am

  #1390776

  JimS-Indy (10/5/2011)

  ---

  I just can't identify the syntax error in the last line here (WHERE clause) Can anyone help?

  INSERT INTO tblBoMDetail

  (BoMID,

  BoMItemDescription,

  BoMEItemQty,

  BoMEItemfactor,

  BoMEItemUOM,

  BoMItemUnitMaterialCost,

  BoMItemUnitLaborHrs,

  BoMlaborComplexity,

  BoMvendorName,

  BoMvendorPartNbr,

  BoMItemID)

  SELECT @NewBoMID,

  ModelBoM.BoMItemDescription,

  ModelBoM.BoMEItemQty,

  ModelBoM.BoMEItemfactor,

  ModelBoM.BoMEItemUOM,

  ModelBoM.BoMItemUnitMaterialCost,

  ModelBoM.BoMItemUnitLaborHrs,

  ModelBoM.BoMlaborComplexity,

  ModelBoM.BoMvendorName,

  ModelBoM.BoMvendorPartNbr,

  ModelBoM.BoMItemID

  FROM (SELECT tblBoMDetail.* FROM tblBoMDetail JOIN qryActiveItemMaster ON tblBomDetail.BoMItemID=qryActiveItemMaster.ItemID WHERE tblBoMDetail.BoMID=@ModelBoMID) AS ModelBoM

  LEFT OUTER JOIN (SELECT tblBoMDetail.* FROM tblBoMDetail WHERE tblBoMDetail.BoMID=@NewBoMID) AS NewBoM

  WHERE NewBoM.BoMDetailID IS NULL

  It seems your left join is missing an "on" keyword

  I am guessing it should be something like on modelbom.bomdetailid = newbom.bomdetailid

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  October 5, 2011 at 9:45 am

  #1390781

  bkubicek (10/5/2011)

  ---

  JimS-Indy (10/5/2011)

  ---

  I just can't identify the syntax error in the last line here (WHERE clause) Can anyone help?

  INSERT INTO tblBoMDetail

  (BoMID,

  BoMItemDescription,

  BoMEItemQty,

  BoMEItemfactor,

  BoMEItemUOM,

  BoMItemUnitMaterialCost,

  BoMItemUnitLaborHrs,

  BoMlaborComplexity,

  BoMvendorName,

  BoMvendorPartNbr,

  BoMItemID)

  SELECT @NewBoMID,

  ModelBoM.BoMItemDescription,

  ModelBoM.BoMEItemQty,

  ModelBoM.BoMEItemfactor,

  ModelBoM.BoMEItemUOM,

  ModelBoM.BoMItemUnitMaterialCost,

  ModelBoM.BoMItemUnitLaborHrs,

  ModelBoM.BoMlaborComplexity,

  ModelBoM.BoMvendorName,

  ModelBoM.BoMvendorPartNbr,

  ModelBoM.BoMItemID

  FROM (SELECT tblBoMDetail.* FROM tblBoMDetail JOIN qryActiveItemMaster ON tblBomDetail.BoMItemID=qryActiveItemMaster.ItemID WHERE tblBoMDetail.BoMID=@ModelBoMID) AS ModelBoM

  LEFT OUTER JOIN (SELECT tblBoMDetail.* FROM tblBoMDetail WHERE tblBoMDetail.BoMID=@NewBoMID) AS NewBoM

  WHERE NewBoM.BoMDetailID IS NULL

  It seems your left join is missing an "on" keyword

  I am guessing it should be something like on modelbom.bomdetailid = newbom.bomdetailid

  Nice catch -

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• JimS-Indy

  SSCarpal Tunnel

  Points: 4514

  More actions

  October 5, 2011 at 9:45 am

  #1390782

  Perfect, SSChasing Mays, thanks. I'd have beaten on that for hours without you!

  I really appreciate the help!

  Jim

  Jim