Dynamics Community Forum Thread Details

SQL Query

Mabuhay! Hi! I just wondering why this code doesnt work on SQL but if i run this one in memorized report it will show the query;

SELECT PurchaseOrder.LastUpdated, PurchaseOrder.POTitle, PurchaseOrder.POType, PurchaseOrder.StoreID, PurchaseOrder.ID, PurchaseOrder.PONumber,
PurchaseOrder.DateCreated, PurchaseOrder.[To], PurchaseOrderEntry.ItemDescription, PurchaseOrderEntry.StoreID AS Expr2, PurchaseOrderEntry.ID AS Expr3,
PurchaseOrderEntry.PurchaseOrderID, PurchaseOrderEntry.ItemID, PurchaseOrderEntry.Price, PurchaseOrder.ShipTo, PurchaseOrderEntry.OrderNumber
FROM PurchaseOrder LEFT OUTER JOIN
PurchaseOrderEntry ON PurchaseOrder.ID = PurchaseOrderEntry.PurchaseOrderID
GROUP BY PurchaseOrder.ID, PurchaseOrder.ShipTo, PurchaseOrderEntry.OrderNumber

Error:

Msg 8120, Level 16, State 1, Line 1
Column 'PurchaseOrder.LastUpdated' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I would like to run in my DataGridVIew on VIsual Studio application and this error doesnt help me to work on this. Any help guy :).

Thanks,

Chris

And this code works

SELECT max (PurchaseOrder.LastUpdated), max (PurchaseOrder.POTitle), max (PurchaseOrder.POType), max (PurchaseOrder.StoreID), max (PurchaseOrder.ID), max (PurchaseOrder.PONumber),

max (PurchaseOrder.DateCreated), max (PurchaseOrder.[To]), max (PurchaseOrderEntry.ItemDescription), max (PurchaseOrderEntry.StoreID) AS Expr2,max (PurchaseOrderEntry.ID) AS Expr3,

max (PurchaseOrderEntry.PurchaseOrderID), max (PurchaseOrderEntry.ItemID), max (PurchaseOrderEntry.Price), max (PurchaseOrder.ShipTo),max (PurchaseOrderEntry.OrderNumber)

FROM PurchaseOrder LEFT OUTER JOIN

PurchaseOrderEntry ON PurchaseOrder.ID = PurchaseOrderEntry.PurchaseOrderID

GROUP BY PurchaseOrder.ID

just adding the aggregiate code thanks Sir Nicco

Quick Links

Leaderboard