logo
Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
Tom Van Overbeke  
#1 Posted : Tuesday, November 5, 2019 4:17:12 PM(UTC)
Tom Van Overbeke

Rank: Member

Groups: Registered
Joined: 10/23/2019(UTC)
Posts: 10
Belgium

Hi,

I have this quite complicated SQL query that checks out ok in the report designer (when I click 'check sql' in the SQL editor), but when I close the window, I get this error:

"Error when building columns from the SQL select statement:
The column 'Quantity was specified multiple times for 'a'"

This is the SQL query:


SELECT
OD.OrderLineDescription,OD.Quantity, OD.SessionId,
OL.Quantity,
OSD.DeliveryNumber,
C.CustomerName
FROM dbo.OutgoingDetails OD
LEFT OUTER JOIN (
SELECT OutgoingDetailsId, SUM(Quantity) AS Quantity
FROM dbo.OutgoingLocation
GROUP BY OutgoingDetailsId
) AS OL on OD.Id = OL.OutgoingDetailsId
LEFT OUTER JOIN dbo.OutgoingSessionDelivery OSD on OD.SessionId = OSD.SessionId
LEFT OUTER JOIN dbo.Customer C on OSD.CustomerId = C.Id
WHERE OD.Quantity <> ISNULL(OL.Quantity, 0)

Can someone help me to modify it so I gets accepted?

Thanks,
Tom
Alex Keil  
#2 Posted : Tuesday, November 5, 2019 11:50:44 PM(UTC)
Alex Keil

Rank: Newbie

Groups: Registered
Joined: 2/1/2019(UTC)
Posts: 4
Australia

Hello
The issue is that you have OD.Quantity as well as OL.Quantity.
Perhaps change them to something like OutgoingQuantity and LocationQuantity?

SELECT
OD.OrderLineDescription,
OD.Quantity AS OutgoingDetailsQuantity
OD.SessionId,
OL.Quantity AS LocationQuantity,
OSD.DeliveryNumber,
C.CustomerName
FROM
dbo.OutgoingDetails OD
LEFT OUTER JOIN (
SELECT
OutgoingDetailsId,
SUM(Quantity) AS Quantity
FROM
dbo.OutgoingLocation
GROUP BY
OutgoingDetailsId
) AS OL
on
OD.Id = OL.OutgoingDetailsId
LEFT OUTER JOIN
dbo.OutgoingSessionDelivery OSD
on OD.SessionId = OSD.SessionId
LEFT OUTER JOIN
dbo.Customer C
on OSD.CustomerId = C.Id
WHERE OD.Quantity <> ISNULL(OL.Quantity, 0)
Tom Van Overbeke  
#3 Posted : Thursday, November 7, 2019 1:49:26 PM(UTC)
Tom Van Overbeke

Rank: Member

Groups: Registered
Joined: 10/23/2019(UTC)
Posts: 10
Belgium

Hi,

Thanks for your reply.
This is the query as it is accepted by sealreport:

SELECT
OD.OrderLineDescription,OD.Quantity, OD.SessionId,
Aantal_gepickt,
OSD.DeliveryNumber,
C.CustomerName
FROM dbo.OutgoingDetails OD
LEFT OUTER JOIN (
SELECT OutgoingDetailsId, SUM(Quantity) AS Aantal_gepickt --<--
FROM dbo.OutgoingLocation
GROUP BY OutgoingDetailsId
) AS OL on OD.Id = OL.OutgoingDetailsId
LEFT OUTER JOIN dbo.OutgoingSessionDelivery OSD on OD.SessionId = OSD.SessionId
LEFT OUTER JOIN dbo.Customer C on OSD.CustomerId = C.Id
WHERE OD.Quantity <> ISNULL(Aantal_gepickt, 0)
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.