syntax error in select distinct

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server Newbies » syntax error in select distinct

syntax error in select distinct

Rate Topic

Display Mode

Topic Options

Author

Message

michael.valentino

Posted Wednesday, October 08, 2008 10:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 17, 2009 1:31 PM
Points: 1, Visits: 14

Hi, I'm relatively new to sql server and asp, and am having an issue with a particular piece of code.
basically I'm getting a syntax error at line 498 of the code below, if someone could take a glance and tell me if they see the error that would be great, if I need to provide more info please mention it and I will submit a more descriptive post

SQLstmt=("SELECT DISTINCT Sale_Prodname.part_name, Sale_partnumber.*, SCompanies.Customer,SCompanies.customerID,Regions.Region, Regions.RegionID,"_
& " Territories.Territories, Territories.ID, Territories.Abbreviation, " _
& " E.First, E.Last, " & Table & ".*, SCompanies.Account, Sale_Prodname.Category, Distributor.Name AS DName, " _
& " " & SplitsTable & ".TicketID AS Spl_TicketID, " & SplitsTable & ".Percentage AS Spl_Percentage, ES.First AS Spl_First, ES.Last AS Spl_Last, OEMCustomers.OEMName " _
& " FROM Sale_Prodname INNER JOIN Sale_Partnumber ON Sale_Prodname.id = Sale_Partnumber.prodid"_
& " INNER JOIN " & Table & " ON Sale_Partnumber.ID = " & Table & ".ProductID"_
& " INNER JOIN Territories ON " & Table & ".TerritoryID = Territories.ID"_
& " INNER JOIN SCompanies ON SCompanies.CustomerID = " & Table & ".DivisionID"_
& " INNER JOIN Regions ON Regions.RegionID = Territories.RegionID "_
& " LEFT JOIN txhelpdesk.dbo.Engineers E ON E.EngineerID = " & Table & ".EngID "_
& " LEFT JOIN OEMCustomers ON OEMCustomers.OEMID = SCompanies.OEMID " _
& " LEFT JOIN Distributor ON Distributor.ID = " & Table & ".Distributor "_
& " LEFT JOIN " & SplitsTable & " ON DesignWin.TicketID = " & SplitsTable & ".TicketID " _
& " LEFT JOIN txhelpdesk.dbo.Engineers ES ON ES.EngineerID = " & SplitsTable & ".EngID " _
& " " & InnerJoinCond & " " & MyDate & " " & MyCondition & " " & MyStatus & " " & MyCustomer & " " & MyAccess & " " & RSM_Condition & " " & MyDistributor & MyOEMCustomer & MyTimeFrame & MyTier & MySptf & " ORDER BY " & Table & ".TicketID" )

'response.write Disposition & "

"
'response.write SQLStmt & "

"
'response.write AccessLevel & "

"

if Len(SavedSearch) > 0 then
set rs = conntemp.execute("SELECT SQLStmt, DateEntered FROM SavedSearch WHERE ID=" & SavedSearch)
SQLStmt = rs(0)
SavedDate = rs(1)
SQLStmt = Replace(SQLStmt, SavedDate, Date)
end if

session("SaveSQL") = SQLstmt
session("InnerJoinCond") = InnerJoinCond

set rstemp = conntemp.execute(SQLstmt) <--SYNTAX ERROR at ")"
'if EngineerID=154 then response.write SQLStmt

IF rstemp.eof then
'if viewfrom=1 then
response.write "no matches found"
response.end
'end if

Thanks in advance fro any help

Post #582768

steveb.

Posted Wednesday, October 08, 2008 11:20 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:42 AM
Points: 2,802, Visits: 7,103

From a quick look it looks like this statement may be casuing you some problems

set rs = conntemp.execute("SELECT SQLStmt, DateEntered FROM SavedSearch WHERE ID=" & SavedSearch)

As you SQLstmnt already has the FROM Clause it will error when you try and combine it with another FROM clause.

Are you to provide the SQL statement that is sent to the sever? (with actual values and not variables in it)

Post #582793

« Prev Topic | Next Topic »

Permissions