Prev: Dead Lock Again
Next: IO is thawed/frozen
From: Aleks on 16 Feb 2005 10:21
I have a query but when I order by one of the elements I get this error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must appear in
the select list if SELECT DISTINCT is specified.
/BlueDot2005Beta/Intranet/Reports/Advanced/ReportsResultsattsnosteps.asp,
line 153

(This is when I try to order by 'f.MaidenNm')

This is my query:

SELECT distinct a.id,a.caseid, b.userid as AlienUserId, b.FirstNm, b.LastNm,
e.Processcatalog, b.I94DateD, a.ExpDate,a.maincase,a.caserelationship,
f.MaidenNm AS EmployerName, a.approvaldate, i.userid,i.MailStr, i.MaidenNm
FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid INNER
JOIN Processcatalog AS E ON A.process = E.processcatalogid INNER JOIN
Atts AS J ON A.id = J.caseid INNER JOIN Users AS I ON J.userid =
I.Userid LEFT JOIN Users AS F ON A.empid = F.userid LEFT JOIN
Activities AS G ON A.id = G.caseid AND G.acttype = 'history' AND
G.lastmodified IS NOT NULL AND G.dateinitiated IS NULL
WHERE A.firmid = MMColParam AND a.archived LIKE 'MMColParam3'
ORDER BY i.userid,MMColParam2

I am trying to order by f.MaidenNm, but that is when I get the error.

Any ideas ?

Aleks


From: Adam Machanic on 16 Feb 2005 10:34
Same reply as the first time you posted this:


Just as the error says -- you need to include the column you're ordering by
in the select list if you use the DISTINCT keyword. If this ruins your
distinctness, you will have to re-write query so as not to use DISTINCT. If
you need help with that, please post DDL and sample data. See this link for
more info:

http://www.aspfaq.com/etiquette.asp?id=5006


--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--


"Aleks" <arkark2004(a)hotmail.com> wrote in message
news:%23OmWrsDFFHA.2568(a)TK2MSFTNGP10.phx.gbl...
> I have a query but when I order by one of the elements I get this error:
>
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must appear
in
> the select list if SELECT DISTINCT is specified.
> /BlueDot2005Beta/Intranet/Reports/Advanced/ReportsResultsattsnosteps.asp,
> line 153
>
> (This is when I try to order by 'f.MaidenNm')
>
> This is my query:
>
> SELECT distinct a.id,a.caseid, b.userid as AlienUserId, b.FirstNm,
b.LastNm,
> e.Processcatalog, b.I94DateD, a.ExpDate,a.maincase,a.caserelationship,
> f.MaidenNm AS EmployerName, a.approvaldate, i.userid,i.MailStr, i.MaidenNm
> FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid
INNER
> JOIN Processcatalog AS E ON A.process = E.processcatalogid INNER
JOIN
> Atts AS J ON A.id = J.caseid INNER JOIN Users AS I ON J.userid =
> I.Userid LEFT JOIN Users AS F ON A.empid = F.userid LEFT JOIN
> Activities AS G ON A.id = G.caseid AND G.acttype = 'history'
AND
> G.lastmodified IS NOT NULL AND G.dateinitiated IS NULL
> WHERE A.firmid = MMColParam AND a.archived LIKE 'MMColParam3'
> ORDER BY i.userid,MMColParam2
>
> I am trying to order by f.MaidenNm, but that is when I get the error.
>
> Any ideas ?
>
> Aleks
>
>


From: Aleks on 16 Feb 2005 12:12
Ill send this over this afternoon, thanks


"Adam Machanic" <amachanic(a)hotmail._removetoemail_.com> wrote in message
news:eQ0kP0DFFHA.1932(a)TK2MSFTNGP14.phx.gbl...
> Same reply as the first time you posted this:
>
>
> Just as the error says -- you need to include the column you're ordering
> by
> in the select list if you use the DISTINCT keyword. If this ruins your
> distinctness, you will have to re-write query so as not to use DISTINCT.
> If
> you need help with that, please post DDL and sample data. See this link
> for
> more info:
>
> http://www.aspfaq.com/etiquette.asp?id=5006
>
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>
> "Aleks" <arkark2004(a)hotmail.com> wrote in message
> news:%23OmWrsDFFHA.2568(a)TK2MSFTNGP10.phx.gbl...
>> I have a query but when I order by one of the elements I get this error:
>>
>> Error Type:
>> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
>> [Microsoft][ODBC SQL Server Driver][SQL Server]ORDER BY items must appear
> in
>> the select list if SELECT DISTINCT is specified.
>> /BlueDot2005Beta/Intranet/Reports/Advanced/ReportsResultsattsnosteps.asp,
>> line 153
>>
>> (This is when I try to order by 'f.MaidenNm')
>>
>> This is my query:
>>
>> SELECT distinct a.id,a.caseid, b.userid as AlienUserId, b.FirstNm,
> b.LastNm,
>> e.Processcatalog, b.I94DateD, a.ExpDate,a.maincase,a.caserelationship,
>> f.MaidenNm AS EmployerName, a.approvaldate, i.userid,i.MailStr,
>> i.MaidenNm
>> FROM Cases AS A INNER JOIN Users AS B ON A.alienid = B.userid
> INNER
>> JOIN Processcatalog AS E ON A.process = E.processcatalogid INNER
> JOIN
>> Atts AS J ON A.id = J.caseid INNER JOIN Users AS I ON J.userid =
>> I.Userid LEFT JOIN Users AS F ON A.empid = F.userid LEFT JOIN
>> Activities AS G ON A.id = G.caseid AND G.acttype = 'history'
> AND
>> G.lastmodified IS NOT NULL AND G.dateinitiated IS NULL
>> WHERE A.firmid = MMColParam AND a.archived LIKE 'MMColParam3'
>> ORDER BY i.userid,MMColParam2
>>
>> I am trying to order by f.MaidenNm, but that is when I get the error.
>>
>> Any ideas ?
>>
>> Aleks
>>
>>
>
>


 | 
Pages: 1
Prev: Dead Lock Again
Next: IO is thawed/frozen