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
 ORDER BY items must appear in the select list if t

Author  Topic 

ChrisK1506
Starting Member

6 Posts
Posted - 2011-08-22 : 11:36:28
Hi,

I am fairly new to SQL and I am having trouble with the following query, I get the error message "ORDER BY items must appear in the select list if the statement contains a UNION operator".

Can anyone show me how to fix this error?

Thanks



SELECT (SELECT TOP 1 dbo.tblWorkFlowLog.Operation FROM dbo.tblWorkFlowLog WHERE (dbo.tblOrderLine.OrderLineId = dbo.tblWorkFlowLog.OrderLineId)
ORDER BY WorkFlowLogId DESC) AS Operation,
dbo.tblOrderLine.OrderLineStatus,
dbo.tblOrderHeader.DateStamp,
dbo.tblOrderHeader.UserRef,
dbo.tblOrderHeader.SourceCode,
dbo.tblOrderLine.OrderLineId,
dbo.tblCustomer.Firstname,
dbo.tblCustomer.Surname,
CASE
WHEN (dbo.tblCustomer.Firstname <> '') AND
(dbo.tblCustomer.Firstname IS NOT NULL)
THEN COALESCE (dbo.tblCustomer.Firstname + ' ', N'') +
COALESCE (dbo.tblCustomer.Surname, N'') ELSE
COALESCE (dbo.tblCustomer.Surname, N'') END as Name,
dbo.tblCustomer.PostCode, dbo.tblOrderLine.BarCode,
CONVERT (nvarchar(50), dbo.tblOrderLine.Quantity) + ' x ' + dbo.tblWebOrderLines.GroupProduct AS ProductCode,
MAX(dbo.tblOrderLineOptions.CompletedDate) AS Printed,
dbo.tblOrderLine.DespatchStatus,dbo.tblWebOrderLines.Id as WebOrderLineID,
dbo.fnc_OrderLineIsReplacement(dbo.tblOrderLine.OrderLineId) AS IsReplacement
FROM dbo.tblOrderLine
LEFT OUTER JOIN dbo.tblWebOrderLines ON dbo.tblOrderLine.OrderLineId = dbo.tblWebOrderLines.OrderLineId
LEFT OUTER JOIN dbo.tblOrderLineOptions ON dbo.tblOrderLine.OrderLineId = dbo.tblOrderLineOptions.OrderLineId
LEFT OUTER JOIN dbo.tblOrderHeader ON dbo.tblOrderLine.OrderId = dbo.tblOrderHeader.OrderId
LEFT OUTER JOIN dbo.tblCustomer ON dbo.tblOrderHeader.CustId = dbo.tblCustomer.CustId
GROUP BY dbo.tblWebOrderLines.GroupProduct,
dbo.tblOrderLine.OrderLineStatus,
dbo.tblOrderHeader.DateStamp,
dbo.tblOrderHeader.UserRef,
dbo.tblOrderLine.OrderLineId,
dbo.tblCustomer.Firstname,
dbo.tblCustomer.Surname,
dbo.tblCustomer.PostCode,
dbo.tblOrderLine.BarCode,
CONVERT (nvarchar(50),
dbo.tblOrderLine.Quantity) + ' x ' + dbo.tblWebOrderLines.GroupProduct,
dbo.tblOrderLine.DespatchStatus,
dbo.tblOrderLineOptions.WorkFlowId,
dbo.tblOrderHeader.SourceCode,
dbo.tblOrderLine.ProductCode,
dbo.tblWebOrderLines.Id
HAVING (dbo.tblOrderLine.DespatchStatus IS NULL
OR dbo.tblOrderLine.DespatchStatus = '') AND
(dbo.tblOrderLineOptions.WorkFlowId = 3) AND
(dbo.tblWebOrderLines.GroupProduct = 'RBCHC')

union SELECT (SELECT TOP 1 dbo.tblWorkFlowLog.Operation, dbo.tblWorkFlowLog.WorkFlowLogId AS WorkFlowLogId FROM dbo.tblWorkFlowLog WHERE (dbo.tblOrderLine.OrderLineId = dbo.tblWorkFlowLog.OrderLineId)
ORDER BY WorkFlowLogId DESC) AS Operation,
dbo.tblOrderLine.OrderLineStatus,
dbo.tblOrderHeader.DateStamp,
dbo.tblOrderHeader.UserRef,
dbo.tblOrderHeader.SourceCode,
dbo.tblOrderLine.OrderLineId, dbo.tblCustomer.Firstname,
dbo.tblCustomer.Surname,
CASE
WHEN (dbo.tblCustomer.Firstname <> '') AND
(dbo.tblCustomer.Firstname IS NOT NULL)
THEN COALESCE (dbo.tblCustomer.Firstname + ' ', N'') +
COALESCE (dbo.tblCustomer.Surname, N'')
ELSE COALESCE (dbo.tblCustomer.Surname, N'')
END as Name, dbo.tblCustomer.PostCode, dbo.tblOrderLine.BarCode,
CONVERT (nvarchar(50), dbo.tblOrderLine.Quantity) + ' x ' + dbo.tblWebOrderLines.GroupProduct AS ProductCode,
MAX(dbo.tblOrderLineOptions.CompletedDate) AS Printed,
dbo.tblOrderLine.DespatchStatus,dbo.tblWebOrderLines.Id as WebOrderLineID,
dbo.fnc_OrderLineIsReplacement(dbo.tblOrderLine.OrderLineId) AS IsReplacement FROM dbo.tblOrderLine
LEFT OUTER JOIN dbo.tblWebOrderLines ON dbo.tblOrderLine.OrderLineId = dbo.tblWebOrderLines.OrderLineId
LEFT OUTER JOIN dbo.tblOrderLineOptions ON dbo.tblOrderLine.OrderLineId = dbo.tblOrderLineOptions.OrderLineId
LEFT OUTER JOIN dbo.tblOrderHeader ON dbo.tblOrderLine.OrderId = dbo.tblOrderHeader.OrderId
LEFT OUTER JOIN dbo.tblCustomer ON dbo.tblOrderHeader.CustId = dbo.tblCustomer.CustId
GROUP BY dbo.tblWebOrderLines.GroupProduct,
dbo.tblOrderLine.OrderLineStatus,
dbo.tblOrderHeader.DateStamp,
dbo.tblOrderHeader.UserRef,
dbo.tblOrderLine.OrderLineId,
dbo.tblCustomer.Firstname,
dbo.tblCustomer.Surname,
dbo.tblCustomer.PostCode,
dbo.tblOrderLine.BarCode,
CONVERT (nvarchar(50),
dbo.tblOrderLine.Quantity) + ' x ' + dbo.tblWebOrderLines.GroupProduct,
dbo.tblOrderLine.DespatchStatus,
dbo.tblOrderLineOptions.WorkFlowId,
dbo.tblOrderHeader.SourceCode,
dbo.tblOrderLine.ProductCode,
dbo.tblWebOrderLines.Id
HAVING (dbo.tblOrderLine.DespatchStatus IS NULL
OR dbo.tblOrderLine.DespatchStatus = '')
AND (dbo.tblOrderLineOptions.WorkFlowId = 3)
AND (dbo.tblWebOrderLines.GroupProduct = 'RBCIG')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-08-22 : 11:57:29
[code]
SELECT (SELECT TOP 1 dbo.tblWorkFlowLog.Operation,dbo.tblWorkFlowLog.WorkFlowLogId FROM dbo.tblWorkFlowLog WHERE (dbo.tblOrderLine.OrderLineId = dbo.tblWorkFlowLog.OrderLineId)
ORDER BY WorkFlowLogId DESC) AS Operation,
dbo.tblOrderLine.OrderLineStatus,
dbo.tblOrderHeader.DateStamp,
dbo.tblOrderHeader.UserRef,
...
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

ChrisK1506
Starting Member

6 Posts
Posted - 2011-08-22 : 12:01:35
Hi,

Thanks for your reply, unfortunately that doesn't seem to work (I had already tried that)

I now get the error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-08-22 : 12:11:12
why dont you use apply then?


SELECT  t.Operation,
dbo.tblOrderLine.OrderLineStatus, 
dbo.tblOrderHeader.DateStamp,
dbo.tblOrderHeader.UserRef, 
dbo.tblOrderHeader.SourceCode, 
dbo.tblOrderLine.OrderLineId, 
dbo.tblCustomer.Firstname,
dbo.tblCustomer.Surname,
CASE
WHEN (dbo.tblCustomer.Firstname <> '') AND 
(dbo.tblCustomer.Firstname IS NOT NULL) 
THEN COALESCE (dbo.tblCustomer.Firstname + ' ', N'') + 
COALESCE (dbo.tblCustomer.Surname, N'') ELSE
COALESCE (dbo.tblCustomer.Surname, N'') END as Name, 
dbo.tblCustomer.PostCode, dbo.tblOrderLine.BarCode, 
CONVERT (nvarchar(50), dbo.tblOrderLine.Quantity) + ' x ' + dbo.tblWebOrderLines.GroupProduct AS ProductCode,
MAX(dbo.tblOrderLineOptions.CompletedDate) AS Printed, 
dbo.tblOrderLine.DespatchStatus,dbo.tblWebOrderLines.Id as WebOrderLineID, 
dbo.fnc_OrderLineIsReplacement(dbo.tblOrderLine.OrderLineId) AS IsReplacement 
FROM dbo.tblOrderLine 
CROSS APPLY (SELECT TOP 1 dbo.tblWorkFlowLog.Operation FROM dbo.tblWorkFlowLog WHERE (dbo.tblOrderLine.OrderLineId = dbo.tblWorkFlowLog.OrderLineId) 
ORDER BY WorkFlowLogId DESC)t
LEFT OUTER JOIN dbo.tblWebOrderLines ON dbo.tblOrderLine.OrderLineId = dbo.tblWebOrderLines.OrderLineId 
LEFT OUTER JOIN dbo.tblOrderLineOptions ON dbo.tblOrderLine.OrderLineId = dbo.tblOrderLineOptions.OrderLineId 
LEFT OUTER JOIN dbo.tblOrderHeader ON dbo.tblOrderLine.OrderId = dbo.tblOrderHeader.OrderId 
LEFT OUTER JOIN dbo.tblCustomer ON dbo.tblOrderHeader.CustId = dbo.tblCustomer.CustId 
GROUP BY dbo.tblWebOrderLines.GroupProduct,
dbo.tblOrderLine.OrderLineStatus, 
dbo.tblOrderHeader.DateStamp, 
dbo.tblOrderHeader.UserRef, 
dbo.tblOrderLine.OrderLineId, 
dbo.tblCustomer.Firstname, 
dbo.tblCustomer.Surname, 
dbo.tblCustomer.PostCode, 
dbo.tblOrderLine.BarCode, 
CONVERT (nvarchar(50), 
dbo.tblOrderLine.Quantity) + ' x ' + dbo.tblWebOrderLines.GroupProduct, 
dbo.tblOrderLine.DespatchStatus, 
dbo.tblOrderLineOptions.WorkFlowId, 
dbo.tblOrderHeader.SourceCode, 
dbo.tblOrderLine.ProductCode, 
dbo.tblWebOrderLines.Id 
HAVING (dbo.tblOrderLine.DespatchStatus IS NULL 
OR dbo.tblOrderLine.DespatchStatus = '') AND 
(dbo.tblOrderLineOptions.WorkFlowId = 3) AND 
(dbo.tblWebOrderLines.GroupProduct = 'RBCHC') 

union 

SELECT t.Operation, 
dbo.tblOrderLine.OrderLineStatus, 
dbo.tblOrderHeader.DateStamp, 
dbo.tblOrderHeader.UserRef, 
dbo.tblOrderHeader.SourceCode, 
dbo.tblOrderLine.OrderLineId, dbo.tblCustomer.Firstname,
dbo.tblCustomer.Surname,
CASE
WHEN (dbo.tblCustomer.Firstname <> '') AND 
(dbo.tblCustomer.Firstname IS NOT NULL) 
THEN COALESCE (dbo.tblCustomer.Firstname + ' ', N'') + 
COALESCE (dbo.tblCustomer.Surname, N'') 
ELSE COALESCE (dbo.tblCustomer.Surname, N'') 
END as Name, dbo.tblCustomer.PostCode, dbo.tblOrderLine.BarCode, 
CONVERT (nvarchar(50), dbo.tblOrderLine.Quantity) + ' x ' + dbo.tblWebOrderLines.GroupProduct AS ProductCode, 
MAX(dbo.tblOrderLineOptions.CompletedDate) AS Printed, 
dbo.tblOrderLine.DespatchStatus,dbo.tblWebOrderLines.Id as WebOrderLineID, 
dbo.fnc_OrderLineIsReplacement(dbo.tblOrderLine.OrderLineId) AS IsReplacement 
FROM dbo.tblOrderLine 
CROSS APPLY (SELECT TOP 1 dbo.tblWorkFlowLog.Operation 
FROM dbo.tblWorkFlowLog WHERE (dbo.tblOrderLine.OrderLineId = dbo.tblWorkFlowLog.OrderLineId) 
ORDER BY WorkFlowLogId DESC)t 
LEFT OUTER JOIN dbo.tblWebOrderLines ON dbo.tblOrderLine.OrderLineId = dbo.tblWebOrderLines.OrderLineId 
LEFT OUTER JOIN dbo.tblOrderLineOptions ON dbo.tblOrderLine.OrderLineId = dbo.tblOrderLineOptions.OrderLineId 
LEFT OUTER JOIN dbo.tblOrderHeader ON dbo.tblOrderLine.OrderId = dbo.tblOrderHeader.OrderId 
LEFT OUTER JOIN dbo.tblCustomer ON dbo.tblOrderHeader.CustId = dbo.tblCustomer.CustId 
GROUP BY dbo.tblWebOrderLines.GroupProduct,
dbo.tblOrderLine.OrderLineStatus, 
dbo.tblOrderHeader.DateStamp, 
dbo.tblOrderHeader.UserRef, 
dbo.tblOrderLine.OrderLineId, 
dbo.tblCustomer.Firstname, 
dbo.tblCustomer.Surname, 
dbo.tblCustomer.PostCode, 
dbo.tblOrderLine.BarCode, 
CONVERT (nvarchar(50), 
dbo.tblOrderLine.Quantity) + ' x ' + dbo.tblWebOrderLines.GroupProduct, 
dbo.tblOrderLine.DespatchStatus, 
dbo.tblOrderLineOptions.WorkFlowId,
dbo.tblOrderHeader.SourceCode, 
dbo.tblOrderLine.ProductCode, 
dbo.tblWebOrderLines.Id 
HAVING (dbo.tblOrderLine.DespatchStatus IS NULL 
OR dbo.tblOrderLine.DespatchStatus = '') 
AND (dbo.tblOrderLineOptions.WorkFlowId = 3)
AND (dbo.tblWebOrderLines.GroupProduct = 'RBCIG')


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2011-08-22 : 12:15:57
Move the sub-select outside the UNION perhaps perhaps?

SELECT SELECT (SELECT TOP 1 dbo.tblWorkFlowLog.Operation FROM dbo.tblWorkFlowLog WHERE (X.OrderLineId = dbo.tblWorkFlowLog.OrderLineId) 
ORDER BY WorkFlowLogId DESC) AS Operation,
    *
FROM
(
    SELECT dbo.tblOrderLine.OrderLineStatus, 
    ...
    FROM dbo.tblOrderLine 
    ...
    GROUP BY dbo.tblWebOrderLines.GroupProduct,
    ...
    HAVING (dbo.tblOrderLine.DespatchStatus IS NULL 
    ...
    UNION 
    SELECT dbo.tblOrderLine.OrderLineStatus, 
    ...
    FROM dbo.tblOrderLine 
    ...
    GROUP BY dbo.tblWebOrderLines.GroupProduct,
    ...
    HAVING (dbo.tblOrderLine.DespatchStatus IS NULL 
    ...
) AS X
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site