Incorrect syntax when there appears to be no syntax errors.

I keep receiving the following error whenever I try and call this function to update my database.

The code was working before, all I added was an extra field to update.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'

Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)

Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String

strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text

Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @.strProjectName, " _
& "txtProjectDescription = @.txtProjectDescription, " _
& "strProjectState = @.strProjectState, " _
& "intEstDuration = @.intEstDuration, " _
& "dtmCreationDate = @.dtmCreationDate, " _
& "strCreatedBy = @.strCreatedBy, " _
& "strProjectLead = @.strProjectLead, " _
& "dtmEstCompletionDate = @.dtmEstCompletionDate, " _
& "WHERE intProjectID = @.intProjectID"

Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)

cmdSQL.Parameters.Add(new SqlParameter("@.strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@.strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@.txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@.txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@.strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@.strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@.intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@.intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@.dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@.dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@.strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@.strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@.strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@.strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@.dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@.dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@.intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@.intProjectID").Value = intProjectID

myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()

MasterList.EditItemIndex = -1
BindMasterList()

End Sub

Thankyou in advance.> cmdSQL.Parameters.Add(new SqlParameter("@.intProjectID", SqlDbType.NChar, 5))

why wouldintProjectID be an NChar? or is that just misleading?|||You have an extra comma.

"dtmEstCompletionDate = @.dtmEstCompletionDate, " _
"WHERE ... "

that would result in:

dtmEstCompletionDate = '1/1/2003', WHERE

there's an extra comma before the WHERE clause. That is causing the syntax error.

Cheers
Ken|||Good one!sql

Incorrect syntax near the keyword ''WHERE''

Hello,
I'm receiving this error: Msg 156, Level 15, State 1, Procedure pnpcart_GetCustomer_Details, Line 50
Incorrect syntax near the keyword 'WHERE'.

Code Snippet

ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@.inSearchBy varchar(20),
@.inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @.SQL varchar(500)
if (@.inSearchBy='Email' or @.inSearchBy='HomePhone')
begin
set @.SQL = 'SELECT * FROM dbo.pnpcart_Customer_Details WHERE ' + @.inSearchBy + ' = ''' + @.inSearchFor + ''''
exec ( @.SQL )
end
else
begin
SELECT
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
FROM
dbo.pnpcart_Customer_Details INNER JOIN dbo.aspnet_Users
ON
dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName
GROUP BY
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
WHERE dbo.aspnet_Users.UserName = ''+@.inSearchFor+''
end
END

My logic behind this code is utilize .NET membership database tables with one of my tables called pnpcart_Customer_Details. Instead of recreating the wheel I decided to INNER JOIN the columns dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName, and do a search for the value of @.inSearchFor. Every time I want to search for a value of a variable I always end up creating dynamic sql code. I'm trying to stay away from dynamic sql. Any help with my error and how to stay away from dynamic sql is greatly appreciated. Thanks!

-Rich

The where clause should present before the Group By clause,

Code Snippet

SELECT

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

FROM

dbo.pnpcart_Customer_Details

INNER JOIN dbo.aspnet_Users

ON dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE

dbo.aspnet_Users.UserName = @.inSearchFor

GROUP BY

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

Or

Code Snippet

Set @.SQL = 'SELECT

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

FROM

dbo.pnpcart_Customer_Details

INNER JOIN dbo.aspnet_Users

ON dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE

dbo.aspnet_Users.UserName in (' +@.inSearchFor + ')

GROUP BY

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName'

|||

You can do this:

Code Snippet

ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@.inSearchBy varchar(20),
@.inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @.SQL nvarchar(500)
if (@.inSearchBy='Email' or @.inSearchBy='HomePhone')
begin
set @.SQL = 'SELECT * FROM dbo.pnpcart_Customer_Details

WHERE ' + @.inSearchBy + ' = @.inSearchFor'

exec sp_executesql @.SQL, N'@.inSearchFor varchar(100)', @.inSearchFor

end
else
begin
SELECT
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
FROM
dbo.pnpcart_Customer_Details INNER JOIN dbo.aspnet_Users
ON
dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE dbo.aspnet_Users.UserName = @.inSearchFor
GROUP BY
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
end
END

sql

Incorrect syntax near the keyword ''WHERE''

Hello,
I'm receiving this error: Msg 156, Level 15, State 1, Procedure pnpcart_GetCustomer_Details, Line 50
Incorrect syntax near the keyword 'WHERE'.

Code Snippet

ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@.inSearchBy varchar(20),
@.inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @.SQL varchar(500)
if (@.inSearchBy='Email' or @.inSearchBy='HomePhone')
begin
set @.SQL = 'SELECT * FROM dbo.pnpcart_Customer_Details WHERE ' + @.inSearchBy + ' = ''' + @.inSearchFor + ''''
exec ( @.SQL )
end
else
begin
SELECT
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
FROM
dbo.pnpcart_Customer_Details INNER JOIN dbo.aspnet_Users
ON
dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName
GROUP BY
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
WHERE dbo.aspnet_Users.UserName = ''+@.inSearchFor+''
end
END

My logic behind this code is utilize .NET membership database tables with one of my tables called pnpcart_Customer_Details. Instead of recreating the wheel I decided to INNER JOIN the columns dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName, and do a search for the value of @.inSearchFor. Every time I want to search for a value of a variable I always end up creating dynamic sql code. I'm trying to stay away from dynamic sql. Any help with my error and how to stay away from dynamic sql is greatly appreciated. Thanks!

-Rich

The where clause should present before the Group By clause,

Code Snippet

SELECT

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

FROM

dbo.pnpcart_Customer_Details

INNER JOIN dbo.aspnet_Users

ON dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE

dbo.aspnet_Users.UserName = @.inSearchFor

GROUP BY

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

Or

Code Snippet

Set @.SQL = 'SELECT

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

FROM

dbo.pnpcart_Customer_Details

INNER JOIN dbo.aspnet_Users

ON dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE

dbo.aspnet_Users.UserName in (' +@.inSearchFor + ')

GROUP BY

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName'

|||

You can do this:

Code Snippet

ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@.inSearchBy varchar(20),
@.inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @.SQL nvarchar(500)
if (@.inSearchBy='Email' or @.inSearchBy='HomePhone')
begin
set @.SQL = 'SELECT * FROM dbo.pnpcart_Customer_Details

WHERE ' + @.inSearchBy + ' = @.inSearchFor'

exec sp_executesql @.SQL, N'@.inSearchFor varchar(100)', @.inSearchFor

end
else
begin
SELECT
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
FROM
dbo.pnpcart_Customer_Details INNER JOIN dbo.aspnet_Users
ON
dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE dbo.aspnet_Users.UserName = @.inSearchFor
GROUP BY
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
end
END

Incorrect syntax near the keyword ''WHERE''

Hello,
I'm receiving this error: Msg 156, Level 15, State 1, Procedure pnpcart_GetCustomer_Details, Line 50
Incorrect syntax near the keyword 'WHERE'.

Code Snippet

ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@.inSearchBy varchar(20),
@.inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @.SQL varchar(500)
if (@.inSearchBy='Email' or @.inSearchBy='HomePhone')
begin
set @.SQL = 'SELECT * FROM dbo.pnpcart_Customer_Details WHERE ' + @.inSearchBy + ' = ''' + @.inSearchFor + ''''
exec ( @.SQL )
end
else
begin
SELECT
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
FROM
dbo.pnpcart_Customer_Details INNER JOIN dbo.aspnet_Users
ON
dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName
GROUP BY
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
WHERE dbo.aspnet_Users.UserName = ''+@.inSearchFor+''
end
END

My logic behind this code is utilize .NET membership database tables with one of my tables called pnpcart_Customer_Details. Instead of recreating the wheel I decided to INNER JOIN the columns dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName, and do a search for the value of @.inSearchFor. Every time I want to search for a value of a variable I always end up creating dynamic sql code. I'm trying to stay away from dynamic sql. Any help with my error and how to stay away from dynamic sql is greatly appreciated. Thanks!

-Rich

The where clause should present before the Group By clause,

Code Snippet

SELECT

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

FROM

dbo.pnpcart_Customer_Details

INNER JOIN dbo.aspnet_Users

ON dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE

dbo.aspnet_Users.UserName = @.inSearchFor

GROUP BY

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

Or

Code Snippet

Set @.SQL = 'SELECT

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName

FROM

dbo.pnpcart_Customer_Details

INNER JOIN dbo.aspnet_Users

ON dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE

dbo.aspnet_Users.UserName in (' +@.inSearchFor + ')

GROUP BY

dbo.pnpcart_Customer_Details.UserID

, dbo.aspnet_Users.UserName'

|||

You can do this:

Code Snippet

ALTER PROCEDURE [dbo].[pnpcart_GetCustomer_Details]
-- Add the parameters for the stored procedure here
@.inSearchBy varchar(20),
@.inSearchFor varchar(100)

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @.SQL nvarchar(500)
if (@.inSearchBy='Email' or @.inSearchBy='HomePhone')
begin
set @.SQL = 'SELECT * FROM dbo.pnpcart_Customer_Details

WHERE ' + @.inSearchBy + ' = @.inSearchFor'

exec sp_executesql @.SQL, N'@.inSearchFor varchar(100)', @.inSearchFor

end
else
begin
SELECT
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
FROM
dbo.pnpcart_Customer_Details INNER JOIN dbo.aspnet_Users
ON
dbo.pnpcart_Customer_Details.UserID = dbo.aspnet_Users.UserName

WHERE dbo.aspnet_Users.UserName = @.inSearchFor
GROUP BY
dbo.pnpcart_Customer_Details.UserID, dbo.aspnet_Users.UserName
end
END

Incorrect syntax near ')' SQL 2000

Hi

I keep receiving the error message Incorrect syntax near ')' whilst trying to save a stored procedure from within visual studio, I get the same error from within enterprise manager. The procedure is incomplete but valid, any idea appreciated. The procedure follows:

CREATE PROCEDURE dbo.ProcessComment
@.source AS VARCHAR(50) = NULL

AS
SET NOCOUNT ON

DECLARE @.sourceID AS INT
DECLARE @.counter AS INT
DECLARE @.Sources TABLE
(
sourceid AS INT,
lastconversation AS DATETIME
)

SET @.sourceID = -1
SET @.counter = -1

/*We need to see if this is a new or existing source*/
IF NOT @.source = NULL
BEGIN

INSERT @.Sources SELECT sourceID, lastconversation
FROM sources
WHERE name = @.source
ORDER BY lastconversation

--Do we have any matching sources or is this a new source?

--We need to pick the most likely source from the table
--The most likely source will be either one with an open conversation
--or the latest conversation or if we are lucky the only
--one in the result set

END

Thanks

Gav:

These two lines:

sourceid AS INT,
lastconversation AS DATETIME

need to be changed to:

sourceid AS INT,
lastconversation AS DATETIME

The compiler "thinks" that you are defining a computed column when you include the "AS" keyword.

Dave

|||Thanks Mugambo, that sorted me out :)

I wonder where I picked up that 'AS' syntax from in a table declaration, it just rolled off my fingertips onto the keyboard.

Thanks a lot.sql

Incorrect syntax error

Greetings
I am receiving an incorrect syntax error on line 26 of this SQL. This is not the complete SQL. Any help is appreciated
thanks.
SELECT DISTINC
rtrim(t_Mfg_Order.Mfg_Order_Nbr) "Mfg_Order_Nbr"
t_Mfg_Order_Output.MMM_Id_Nbr "BOM_MMM_Id_Nbr"
t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr "BOM_Component_MMM_Id_Nbr"
t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr
t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr
MIN(upper (t_Item_Master_b.Inventory_UOM)) "Parent_Mfg_Invt_Unit"
MIN(t_Mfg_Order.BOM_Id) "BOM_Id"
MIN(t_Mfg_Order_Output.BOM_Qty) "BOM_Qty"
MIN( upper (t_Item_Master_a.Inventory_UOM)) "Component_Mfg_Invt_Unit"
planned_qty = MAX(qty
FROM (SELECT ISNULL((SELECT SUM(t_Mfg_Order_BOM_Component_b.Planned_Input_Qty
FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_
WHER
t_Mfg_Order_BOM_Component_a.MMM_Facility_Code = t_Mfg_Order_BOM_Component_b.MMM_Facility_Code AN
t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr = t_Mfg_Order_BOM_Component_b.Mfg_Order_Nbr AN
t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr = t_Mfg_Order_BOM_Component_b.Mfg_Order_Operation_Seq_Nbr AN
t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr = t_Mfg_Order_BOM_Component_b.MMM_Id_Nbr AN
((t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr = t_Mfg_Order_BOM_Component_b.BOM_Component_Seq_Nbr) o
(t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr = t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr) o
(t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr = t_Mfg_Order_BOM_Component_b.BOM_Component_Seq_Nbr) O
(t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr = t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr))),1
FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b) AS X(qty)
Load_Usage_qty = MIN(qty
FROM (SELECT ISNULL((SELECT SUM(ISNULL(t_Load_Usage_c.Load_Usage_Qty,0)
FROM t_Load_Usage t_Load_Usage_c
t_Load t_Load_
WHERE t_Mfg_Order_BOM_Component_a.MMM_Facility_Code = t_Load_Usage_c.MMM_Facility_Code AN
t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr = t_Load_Usage_c.Mfg_Order_Nbr AN
t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr = t_Load_Usage_c.Mfg_Order_Operation_Seq_Nbr AN
t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr = t_Load_c.MMM_Id_Nbr AN
t_Load_c.Load_Id = t_Load_Usage_c.Load_Id),0
FROM t_Load_Usage t_Load_Usage_c, t_Load t_Load_c) AS Y(qty)
MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Phase_Out_Code) "Phase_In_Phase_Out_Code"
MIN(t_Mfg_Order_BOM_Component_a.Component_Desc) "Component_Desc"
MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Datetime) "Start_Eff_Date"
MIN(t_Mfg_Order_BOM_Component_a.Phase_Out_Datetime) "End_Eff_Date"
MIN(t_Mfg_Order_BOM_Component_a.Alternate_Priority_Nbr) "Alternate_Priority_Nbr"
MIN(t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr) "Alt_For_Component_Seq_Nbr",It seems like instead of ... planned_qty = MAX(qty) FROM ...
you should have ... planned_qty = (SELECT MAX(qty) FROM ... )
Igor
"CraigG" <anonymous@.discussions.microsoft.com> wrote in message
news:99CE603F-EC7B-4060-A986-9C1A1E8F9617@.microsoft.com...
> Greetings;
> I am receiving an incorrect syntax error on line 26 of this SQL. This is
not the complete SQL. Any help is appreciated.
> thanks..
> SELECT DISTINCT
> rtrim(t_Mfg_Order.Mfg_Order_Nbr) "Mfg_Order_Nbr",
> t_Mfg_Order_Output.MMM_Id_Nbr "BOM_MMM_Id_Nbr",
> t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr "BOM_Component_MMM_Id_Nbr",
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr,
> t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr,
> MIN(upper (t_Item_Master_b.Inventory_UOM)) "Parent_Mfg_Invt_Unit",
> MIN(t_Mfg_Order.BOM_Id) "BOM_Id",
> MIN(t_Mfg_Order_Output.BOM_Qty) "BOM_Qty",
> MIN( upper (t_Item_Master_a.Inventory_UOM))
"Component_Mfg_Invt_Unit",
> planned_qty = MAX(qty)
> FROM (SELECT ISNULL((SELECT
SUM(t_Mfg_Order_BOM_Component_b.Planned_Input_Qty)
> FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b
> WHERE
> t_Mfg_Order_BOM_Component_a.MMM_Facility_Code =t_Mfg_Order_BOM_Component_b.MMM_Facility_Code AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr =t_Mfg_Order_BOM_Component_b.Mfg_Order_Nbr AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr
= t_Mfg_Order_BOM_Component_b.Mfg_Order_Operation_Seq_Nbr AND
> t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr =t_Mfg_Order_BOM_Component_b.MMM_Id_Nbr AND
> ((t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr =t_Mfg_Order_BOM_Component_b.BOM_Component_Seq_Nbr) or
> (t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr =t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr) or
> (t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr =t_Mfg_Order_BOM_Component_b.BOM_Component_Seq_Nbr) OR
> (t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr =t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr))),1)
> FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b) AS
X(qty),
> Load_Usage_qty = MIN(qty)
> FROM (SELECT ISNULL((SELECT
SUM(ISNULL(t_Load_Usage_c.Load_Usage_Qty,0))
> FROM t_Load_Usage t_Load_Usage_c,
> t_Load t_Load_c
> WHERE t_Mfg_Order_BOM_Component_a.MMM_Facility_Code =t_Load_Usage_c.MMM_Facility_Code AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr =t_Load_Usage_c.Mfg_Order_Nbr AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr
= t_Load_Usage_c.Mfg_Order_Operation_Seq_Nbr AND
> t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr =t_Load_c.MMM_Id_Nbr AND
> t_Load_c.Load_Id = t_Load_Usage_c.Load_Id),0)
> FROM t_Load_Usage t_Load_Usage_c, t_Load t_Load_c) AS Y(qty),
> MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Phase_Out_Code)
"Phase_In_Phase_Out_Code",
> MIN(t_Mfg_Order_BOM_Component_a.Component_Desc) "Component_Desc",
> MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Datetime)
"Start_Eff_Date",
> MIN(t_Mfg_Order_BOM_Component_a.Phase_Out_Datetime) "End_Eff_Date",
> MIN(t_Mfg_Order_BOM_Component_a.Alternate_Priority_Nbr)
"Alternate_Priority_Nbr",
> MIN(t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr)
"Alt_For_Component_Seq_Nbr",sql

Incorrect syntax error

Greetings;
I am receiving an incorrect syntax error on line 26 of this SQL. This is no
t the complete SQL. Any help is appreciated.
thanks..
SELECT DISTINCT
rtrim(t_Mfg_Order.Mfg_Order_Nbr) "Mfg_Order_Nbr",
t_Mfg_Order_Output.MMM_Id_Nbr "BOM_MMM_Id_Nbr",
t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr "BOM_Component_MMM_Id_Nbr",
t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr,
t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr,
MIN(upper (t_Item_Master_b.Inventory_UOM)) "Parent_Mfg_Invt_Unit",
MIN(t_Mfg_Order.BOM_Id) "BOM_Id",
MIN(t_Mfg_Order_Output.BOM_Qty) "BOM_Qty",
MIN( upper (t_Item_Master_a.Inventory_UOM)) "Component_Mfg_Invt_Unit",
planned_qty = MAX(qty)
FROM (SELECT ISNULL((SELECT SUM(t_Mfg_Order_BOM_Component_b.Planned_Input_Qt
y)
FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b
WHERE
t_Mfg_Order_BOM_Component_a.MMM_Facility_Code = t_Mfg_Order_BOM_Component_b.
MMM_Facility_Code AND
t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr = t_Mfg_Order_BOM_Component_b.Mfg_
Order_Nbr AND
t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr = t_Mfg_Order_BOM_Co
mponent_b.Mfg_Order_Operation_Seq_Nbr AND
t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr = t_Mfg_Order_BOM_Component_b.MMM_Id_
Nbr AND
((t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr = t_Mfg_Order_BOM_Compon
ent_b.BOM_Component_Seq_Nbr) or
(t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr = t_Mfg_Order_BOM_Compone
nt_b.Alt_For_Component_Seq_Nbr) or
(t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr = t_Mfg_Order_BOM_Com
ponent_b.BOM_Component_Seq_Nbr) OR
(t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr = t_Mfg_Order_BOM_Com
ponent_a.Alt_For_Component_Seq_Nbr))),1)
FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b) AS X(qty),
Load_Usage_qty = MIN(qty)
FROM (SELECT ISNULL((SELECT SUM(ISNULL(t_Load_Usage_c.Load_Usage_Qty,0))
FROM t_Load_Usage t_Load_Usage_c,
t_Load t_Load_c
WHERE t_Mfg_Order_BOM_Component_a.MMM_Facility_Code = t_Load_Usage_c.MMM_Fac
ility_Code AND
t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr = t_Load_Usage_c.Mfg_Order_Nbr AND
t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr = t_Load_Usage_c.Mfg
_Order_Operation_Seq_Nbr AND
t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr = t_Load_c.MMM_Id_Nbr AND
t_Load_c.Load_Id = t_Load_Usage_c.Load_Id),0)
FROM t_Load_Usage t_Load_Usage_c, t_Load t_Load_c) AS Y(qty),
MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Phase_Out_Code) "Phase_In_Phase_Out
_Code",
MIN(t_Mfg_Order_BOM_Component_a.Component_Desc) "Component_Desc",
MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Datetime) "Start_Eff_Date",
MIN(t_Mfg_Order_BOM_Component_a.Phase_Out_Datetime) "End_Eff_Date",
MIN(t_Mfg_Order_BOM_Component_a.Alternate_Priority_Nbr) "Alternate_Priority_
Nbr",
MIN(t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr) "Alt_For_Componen
t_Seq_Nbr",It seems like instead of ... planned_qty = MAX(qty) FROM ...
you should have ... planned_qty = (SELECT MAX(qty) FROM ... )
Igor
"CraigG" <anonymous@.discussions.microsoft.com> wrote in message
news:99CE603F-EC7B-4060-A986-9C1A1E8F9617@.microsoft.com...
> Greetings;
> I am receiving an incorrect syntax error on line 26 of this SQL. This is
not the complete SQL. Any help is appreciated.
> thanks..
> SELECT DISTINCT
> rtrim(t_Mfg_Order.Mfg_Order_Nbr) "Mfg_Order_Nbr",
> t_Mfg_Order_Output.MMM_Id_Nbr "BOM_MMM_Id_Nbr",
> t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr "BOM_Component_MMM_Id_Nbr",
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr,
> t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr,
> MIN(upper (t_Item_Master_b.Inventory_UOM)) "Parent_Mfg_Invt_Unit",
> MIN(t_Mfg_Order.BOM_Id) "BOM_Id",
> MIN(t_Mfg_Order_Output.BOM_Qty) "BOM_Qty",
> MIN( upper (t_Item_Master_a.Inventory_UOM))
"Component_Mfg_Invt_Unit",
> planned_qty = MAX(qty)
> FROM (SELECT ISNULL((SELECT
SUM(t_Mfg_Order_BOM_Component_b.Planned_Input_Qty)
> FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b
> WHERE
> t_Mfg_Order_BOM_Component_a.MMM_Facility_Code =
t_Mfg_Order_BOM_Component_b.MMM_Facility_Code AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr =
t_Mfg_Order_BOM_Component_b.Mfg_Order_Nbr AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr
= t_Mfg_Order_BOM_Component_b.Mfg_Order_Operation_Seq_Nbr AND
> t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr =
t_Mfg_Order_BOM_Component_b.MMM_Id_Nbr AND
> ((t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr =
t_Mfg_Order_BOM_Component_b.BOM_Component_Seq_Nbr) or
> (t_Mfg_Order_BOM_Component_a.BOM_Component_Seq_Nbr =
t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr) or
> (t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr =
t_Mfg_Order_BOM_Component_b.BOM_Component_Seq_Nbr) OR
> (t_Mfg_Order_BOM_Component_b.Alt_For_Component_Seq_Nbr =
t_Mfg_Order_BOM_Component_a. Alt_For_Component_Seq_Nbr))),1)rkred">
> FROM t_Mfg_Order_BOM_Component t_Mfg_Order_BOM_Component_b) AS
X(qty),
> Load_Usage_qty = MIN(qty)
> FROM (SELECT ISNULL((SELECT
SUM(ISNULL(t_Load_Usage_c.Load_Usage_Qty,0))
> FROM t_Load_Usage t_Load_Usage_c,
> t_Load t_Load_c
> WHERE t_Mfg_Order_BOM_Component_a.MMM_Facility_Code =
t_Load_Usage_c.MMM_Facility_Code AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Nbr =
t_Load_Usage_c.Mfg_Order_Nbr AND
> t_Mfg_Order_BOM_Component_a.Mfg_Order_Operation_Seq_Nbr
= t_Load_Usage_c.Mfg_Order_Operation_Seq_Nbr AND
> t_Mfg_Order_BOM_Component_a.MMM_Id_Nbr =
t_Load_c.MMM_Id_Nbr AND
> t_Load_c.Load_Id = t_Load_Usage_c.Load_Id),0)
> FROM t_Load_Usage t_Load_Usage_c, t_Load t_Load_c) AS Y(qty),
> MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Phase_Out_Code)
"Phase_In_Phase_Out_Code",
> MIN(t_Mfg_Order_BOM_Component_a.Component_Desc) "Component_Desc",
> MIN(t_Mfg_Order_BOM_Component_a.Phase_In_Datetime)
"Start_Eff_Date",
> MIN(t_Mfg_Order_BOM_Component_a.Phase_Out_Datetime) "End_Eff_Date",
> MIN(t_Mfg_Order_BOM_Component_a.Alternate_Priority_Nbr)
"Alternate_Priority_Nbr",
> MIN(t_Mfg_Order_BOM_Component_a.Alt_For_Component_Seq_Nbr)
"Alt_For_Component_Seq_Nbr",