Syntax error in FROM clause

[TxStrob]

Thx all, maybe someone has better eyes than I do. I am receiving a syntax error in FROM clause statements for this SQL statement,

SELECT qry.SelectionRowID_FK, Sum(qry.QtyToSell) AS Qty, ItemsTABLE.StoreDepartment
FROM (SELECT ProductInventoryTABLE.SelectionRowID_FK, ProductInventoryTABLE.QtyToSell
FROM ProductInventoryTABLE
WHERE [SelectionRowID_FK]= GET_SelectionID()
UNION
SELECT qry.SelectionRowID_FK, Sum(qry.QtyToSell) AS Qty, ItemsTABLE.StoreDepartment
FROM (SELECT CustomInventoryTABLE.SelectionRowID_FK, CustomInventoryTABLE.QtyToSell
FROM CustomInventoryTABLE
WHERE [SelectionRowID_FK]= GET_SelectionID()
)  AS qry INNER JOIN ItemsTable ON qry.SelectionRowID_FK = ItemsTable.SelectionRowID
GROUP BY qry.SelectionRowID_FK, ItemsTABLE.StoreDepartment

 

[arnelgp]

why don't you break the query into 2 query.
create one query for the union query.
create another query that will link to the first query you made to ItemTable.

 

[MajP]

SELECT qry.selectionrowid_fk,
       SUM(qry.qtytosell) AS qty,
       itemstable.storedepartment
FROM   (
              SELECT productinventorytable.selectionrowid_fk,
                     productinventorytable.qtytosell
              FROM   productinventorytable
              WHERE  [SelectionRowID_FK]= Get_selectionid()
              UNION
              SELECT     qry.selectionrowid_fk,
                         SUM(qry.qtytosell) AS qty,
                         itemstable.storedepartment
              FROM       (
                                SELECT custominventorytable.selectionrowid_fk,
                                       custominventorytable.qtytosell
                                FROM   custominventorytable
                                WHERE  [SelectionRowID_FK]= Get_selectionid() ) AS qry
              INNER JOIN itemstable
              ON         qry.selectionrowid_fk = itemstable.selectionrowid
              GROUP BY   qry.selectionrowid_fk,
                         itemstable.storedepartment

Not sure but looks like you are missing the closing ")". Either way this is easier to read.

 

[TxStrob]

Thank you MajP, thats what I thought and I am trying to figure out where it needs to go as I am getting an error with or without. It states the number of columns in the two selected tables or queries of a Union query do not match. I double checked both queries and tables, they both match for product and custom.

SELECT qry.selectionrowid_fk,
       SUM(qry.qtytosell) AS qty,
       itemstable.storedepartment
FROM   (
              SELECT productinventorytable.selectionrowid_fk,
                     productinventorytable.qtytosell
              FROM   productinventorytable
              WHERE  [SelectionRowID_FK]= Get_selectionid()
              UNION
              SELECT     qry.selectionrowid_fk,
                         SUM(qry.qtytosell) AS qty,
                         itemstable.storedepartment
              FROM       (
                                SELECT custominventorytable.selectionrowid_fk,
                                       custominventorytable.qtytosell
                                FROM   custominventorytable
                                WHERE  [SelectionRowID_FK]= Get_selectionid() ) AS qry
              INNER JOIN itemstable
              ON         qry.selectionrowid_fk = itemstable.selectionrowid
              GROUP BY   qry.selectionrowid_fk,
                         itemstable.storedepartment

Not sure but looks like you are missing the closing ")". Either way this is easier to read.

 

[isladogs]

I believe the missing bracket should be at the end of the line before UNION

Do you actually need a union query? I think the records in the second section will be included in the first part

 

[TxStrob]

Thank you everyone for your support

 

[Galaxiom]

It states the number of columns in the two selected tables or queries of a Union query do not match. I double checked both queries and tables, they both match for product and custom.

That is exactly the problem. The two subqueries in the union have a different number of fields. Two in the first and three in the second.

It will tell you the other problem after you fix that.

 

[Galaxiom]

I believe the missing bracket should be at the end of the line before UNION

No. Subqueries in Unions don't need to be bracketed.

The missing bracket is before INNER JOIN where aliases are required for both levels of bracketing.

 

[isladogs]

Hi Galaxiom
Thanks...I can see that now!
I'm still not sure that the union query is necessary in this case.
Hoping the OP will check & respond