SELECT DISTINCT throws crystal rpt database error

[santosh1]

I want to get unique records in my crystal report, hence I used DISTINCT Clause in the SQL query, but I get error message:

ODBC error:[Microsoft][ODBC SQL Server Driver][SQL Server]
ORDER BY items must appear in the in the select list is SELECT DISTINCT is specified.

I checked my query if the ORDER BY items is in the select statement and it is there. Removing DISTINCT clause works but there are duplicate records which I want to remove from the report. How can I fix this problem?
Thanks.

Here's my SQL:

SELECT DISTINCT
tsoSOLineDist."QtyOpenToShip",
tsoPickListDetWrk."TranType", tsoPickListDetWrk."SOLineDistKey", tsoPickListDetWrk."PickListNo", tsoPickListDetWrk."WhseBinID", tsoPickListDetWrk."Location", tsoPickListDetWrk."ItemID", tsoPickListDetWrk."ItemDescription", tsoPickListDetWrk."UOM", tsoPickListDetWrk."QtyToPick", tsoPickListDetWrk."TranNoRelChngOrd", tsoPickListDetWrk."CustID", tsoPickListDetWrk."CustName", tsoPickListDetWrk."ShipPriority", tsoPickListDetWrk."ShipDate", tsoPickListDetWrk."ShipMethKey", tsoPickListDetWrk."ShipMethID", tsoPickListDetWrk."ShipToAddrName", tsoPickListDetWrk."ShipToAddrLine1", tsoPickListDetWrk."ShipToAddrLine2", tsoPickListDetWrk."ShipToAddrLine3", tsoPickListDetWrk."ShipToAddrLine4", tsoPickListDetWrk."ShipToAddrLine5", tsoPickListDetWrk."ShipToAddrCity", tsoPickListDetWrk."ShipToAddrStateID", tsoPickListDetWrk."ShipToAddrPsCode", tsoPickListDetWrk."ShipToAddrCount", tsoPickListDetWrk."Comments", tsoPickListDetWrk."KitShipLineKey", tsoPickListDetWrk."DefltShipPriority", tsoPickListDetWrk."DefltShipDate", tsoPickListDetWrk."DefltShipMethID", tsoPickListDetWrk."HeaderCmnt", tsoPickListDetWrk."ReqDelvDate", tsoPickListDetWrk."RcvgWhseID", tsoPickListDetWrk."RcvgWhseName",
tsoPickListDetWrk_NMDP2."itemDescription", tsoPickListDetWrk_NMDP2."QtyShipped", tsoPickListDetWrk_NMDP2."QtyOpentoShip", tsoPickListDetWrk_NMDP2."UOM", tsoPickListDetWrk_NMDP2."Location", tsoPickListDetWrk_NMDP2."itemID"
FROM
{ oj ("SES_app"."dbo"."tsoSOLineDist" tsoSOLineDist INNER JOIN "Jul09_app"."dbo"."tsoPickListDetWrk" tsoPickListDetWrk ON
tsoSOLineDist."SOLineDistKey" = tsoPickListDetWrk."SOLineDistKey&quot
LEFT OUTER JOIN "SES_app"."dbo"."tsoPickListDetWrk_NMDP2" tsoPickListDetWrk_NMDP2 ON
tsoPickListDetWrk."SessionID" = tsoPickListDetWrk_NMDP2."SessionID"}
ORDER BY
tsoPickListDetWrk."TranType" ASC,
tsoPickListDetWrk."TranNoRelChngOrd" ASC,
tsoPickListDetWrk."WhseBinID" ASC,
tsoPickListDetWrk."CustName" ASC,
tsoPickListDetWrk."ShipMethKey" ASC,
tsoPickListDetWrk."ItemID" ASC

 

[MColeman]

It appears that tsoPickListDetWrk is referenced in two databases.

Did you try putting the databasename.dbo on the front of the field names in the order by clause?

 

[santosh1]

"Jul09_app"."dbo"."tsoPickListDetWrk should be

"SES_app"."dbo"."tsoPickListDetWrk

I tried changing it, still gives me the error message
as above.