KrisSimonis
November 27th, 2007, 05:08 AM
Ok, this is a 'funny' problem I'm running into with a query on an SQL 2000 server. I have a select, with a union, and in both parts of the union, one of the fields in the select is a subselect on another table, where I use a TOP 1 and an order by clause on one of the fields, however, the data I want from this table is in another field, so this is the one that is in the subselect.
Now, this works fine, until you put this in a query with a union, then the UNION begins to mope about needing a field that is in an ORDER BY clause to be in the fields list of the select.
The union and the subselect however aren't on the same level.. is there any way I can avoid this problem?
This works fine:
SELECT AllotmentID
, Element
, CustomerID
, phone = (SELECT TOP 1 Phonenumber
FROM CustomerPhoneNumebrs
WHERE CustomerID = A.CustomerID
ORDER BY Description)
FROM Allotments A
WHERE Date BETWEEN @StartDate AND @EndDate
However, when I do this.. I get an error
SELECT AllotmentID
, Element
, CustomerID
, Phone = (SELECT TOP 1 Phonenumber
FROM CustomerPhoneNumbers
WHERE CustomerID = A.CustomerID
ORDER BY Description)
FROM Allotments A
WHERE Date BETWEEN @StartDate AND @EndDate
UNION
SELECT AllotmentID
, Element
, Customer2ID
, Phone = (SELECT TOP 1 Phonenumber
FROM CustomerPhoneNumbers
WHERE CustomerID = A.Customer2ID
ORDER BY Description)
FROM Allotments A
WHERE Date BETWEEN @StartDate AND @EndDate
The error I get is:
erver: Msg 104, Level 15, State 1, Procedure spDTOperSelectArrivalTourleaders, Line 29
ORDER BY items must appear in the select list if the statement contains a UNION operator.
Which is correct, I do not have the description field in the top 1 select, however, this subselect is not part of the union select, so why on earth is this the UNION badgering me about this?
Now, this works fine, until you put this in a query with a union, then the UNION begins to mope about needing a field that is in an ORDER BY clause to be in the fields list of the select.
The union and the subselect however aren't on the same level.. is there any way I can avoid this problem?
This works fine:
SELECT AllotmentID
, Element
, CustomerID
, phone = (SELECT TOP 1 Phonenumber
FROM CustomerPhoneNumebrs
WHERE CustomerID = A.CustomerID
ORDER BY Description)
FROM Allotments A
WHERE Date BETWEEN @StartDate AND @EndDate
However, when I do this.. I get an error
SELECT AllotmentID
, Element
, CustomerID
, Phone = (SELECT TOP 1 Phonenumber
FROM CustomerPhoneNumbers
WHERE CustomerID = A.CustomerID
ORDER BY Description)
FROM Allotments A
WHERE Date BETWEEN @StartDate AND @EndDate
UNION
SELECT AllotmentID
, Element
, Customer2ID
, Phone = (SELECT TOP 1 Phonenumber
FROM CustomerPhoneNumbers
WHERE CustomerID = A.Customer2ID
ORDER BY Description)
FROM Allotments A
WHERE Date BETWEEN @StartDate AND @EndDate
The error I get is:
erver: Msg 104, Level 15, State 1, Procedure spDTOperSelectArrivalTourleaders, Line 29
ORDER BY items must appear in the select list if the statement contains a UNION operator.
Which is correct, I do not have the description field in the top 1 select, however, this subselect is not part of the union select, so why on earth is this the UNION badgering me about this?