incorrect syntax near the keyword group

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

incorrect syntax near the keyword group

Author

Topic

actsql
Starting Member

34 Posts

Posted - 2010-10-15 : 13:15:31

My subquery works fine, however, when I attempt to consolidate the subquery data with a group by statement on ITEMNO and WHOUSE, I get the "incorrect syntax near the keyword group" error. Code is:

SELECT ITEMNO, WHOUSE, Sum(QTY) AS SUMQTY

FROM

(
(SELECT t.item_id AS ITEMNO, dbo.inventory_receipts_d.warehouse_no AS WHOUSE, ISNULL(dbo.inventory_receipts_d.received_quantity,0)AS QTY
FROM dbo.inventory_receipts_d
JOIN dbo.items t ON dbo.inventory_receipts_d.item_id= t.item_id
WHERE t.primary_item_category_no='INVEN' AND t.record_status='A')

UNION

(SELECT u.item_id, dbo.inventory_activity_transfer_d.warehouse_no, (
CASE
WHEN dbo.inventory_activity_transfer_d.transfer_type = 'F'
AND dbo.inventory_activity_transfer_d.move_from_to='W'
AND dbo.inventory_activity_d.item_id = u.item_id
THEN (-1)*isnull(dbo.inventory_activity_transfer_d.stocking_quantity,0)
WHEN dbo.inventory_activity_transfer_d.transfer_type = 'T'
AND dbo.inventory_activity_transfer_d.move_from_to='W'
AND dbo.inventory_activity_d.item_id = u.item_id
THEN (1)*isnull(dbo.inventory_activity_transfer_d.stocking_quantity,0)
ELSE 0
END) AS QTY

FROM dbo.items u
JOIN dbo.inventory_activity_d ON u.item_id = dbo.inventory_activity_d.item_id
JOIN dbo.inventory_activity_transfer_d ON dbo.inventory_activity_d.transaction_no = dbo.inventory_activity_transfer_d.transaction_no
AND dbo.inventory_activity_d.line_no = dbo.inventory_activity_transfer_d.line_no
WHERE u.primary_item_category_no='INVEN' AND u.record_status='A')
)

GROUP BY ITEMNO, WHOUSE

ORDER BY ITEMNO

Thank you. I am a beginning level t-sql programmer and I realize this is probably a quick fix.

A.C.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2010-10-15 : 13:59:10

You simply need to name your subquery:


SELECT ITEMNO, WHOUSE, Sum(QTY) AS SUMQTY

FROM

(
(SELECT t.item_id AS ITEMNO, dbo.inventory_receipts_d.warehouse_no AS WHOUSE, ISNULL(dbo.inventory_receipts_d.received_quantity,0)AS QTY
FROM dbo.inventory_receipts_d
JOIN dbo.items t ON dbo.inventory_receipts_d.item_id= t.item_id
WHERE t.primary_item_category_no='INVEN' AND t.record_status='A')

UNION

(SELECT u.item_id, dbo.inventory_activity_transfer_d.warehouse_no, (
CASE
WHEN dbo.inventory_activity_transfer_d.transfer_type = 'F'
AND dbo.inventory_activity_transfer_d.move_from_to='W'
AND dbo.inventory_activity_d.item_id = u.item_id
THEN (-1)*isnull(dbo.inventory_activity_transfer_d.stocking_quantity,0)
WHEN dbo.inventory_activity_transfer_d.transfer_type = 'T'
AND dbo.inventory_activity_transfer_d.move_from_to='W'
AND dbo.inventory_activity_d.item_id = u.item_id
THEN (1)*isnull(dbo.inventory_activity_transfer_d.stocking_quantity,0)
ELSE 0
END) AS QTY

FROM dbo.items u
JOIN dbo.inventory_activity_d ON u.item_id = dbo.inventory_activity_d.item_id
JOIN dbo.inventory_activity_transfer_d ON dbo.inventory_activity_d.transaction_no = dbo.inventory_activity_transfer_d.transaction_no
AND dbo.inventory_activity_d.line_no = dbo.inventory_activity_transfer_d.line_no
WHERE u.primary_item_category_no='INVEN' AND u.record_status='A')
) A

GROUP BY ITEMNO, WHOUSE

ORDER BY ITEMNO

Some days you're the dog, and some days you're the fire hydrant.

actsql
Starting Member

34 Posts

Posted - 2010-10-15 : 14:01:46

Thanks! That fixed it. I tried that earlier but I think I had a couple issues going and I assumed it had not worked when really another issue was to blame.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2010-10-15 : 14:18:22

You're welcome :)

Some days you're the dog, and some days you're the fire hydrant.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources