I keep getting the following error with my WHERE clause.
Msg 102, Level 15, State 1, Line 47
Incorrect syntax near '-'.
We've found that the code works if the segment " -
(CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder))" is removed. So
it's clear that the error is within this segment.
The object "dbo.ReorderItemsTotals" is a view and uses a UNION command
in the SELECT statement. It joins two fields and outputs it as one
field - "OnPurchaseOrder". The two original fields are smallints and
the resulting UNION'd field is a varchar 10 - however I don't know why
this is.
Can anyone help fix this error? My code is as follows.
Many thanks in advance,
Rype
WHERE
(dbo.ProductAssembly.SubProduct IS NULL) AND
((((0.5 * dbo.SoldLast30TotalWith0Total.Despatched +
dbo.ReorderItemsTotals.SumOfSalesBackOrder) + (0.5 / 4 *
dbo.SoldLast120TotalWith0Total.Despatched) *
dbo.ReorderItemsTotals.ReorderLevel * 120) - (CONVERT(Int,
dbo.ReorderItemsTotals.Stock)) -
(CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder)) +
dbo.ReorderItemsTotals.SumOfSalesBackOrder) > 0) OR
(dbo.ProductAssembly.SubProduct IS NULL) AND
(dbo.ProductDetails.Product1 < dbo.ReorderItemsTotalsSets.StockCode)
AND
(dbo.ProductDetails.Product1 + dbo.productdetails.product2 <
dbo.ReorderItemsTotalsSets.ReorderQuantity)This is a multi-part message in MIME format.
--=_NextPart_000_0007_01C804CC.9AEE5810
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Everything seems to check out using SSMS to balance the parentheses. I =do question if the logic is as you intend, given the groupings. Having =the OR out by itself like that is often not what is desired. Also, do =yourself a favor and start using aliases!! :-)
WHERE dbo.ProductAssembly.SubProduct IS NULL
AND ((((0.5 * dbo.SoldLast30TotalWith0Total.Despatched + =dbo.ReorderItemsTotals.SumOfSalesBackOrder) + (0.5 / 4 * dbo.SoldLast120TotalWith0Total.Despatched) * =dbo.ReorderItemsTotals.ReorderLevel * 120) - (CONVERT(Int, dbo.ReorderItemsTotals.Stock)) - =(CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder)) +
dbo.ReorderItemsTotals.SumOfSalesBackOrder) > 0)
OR
(dbo.ProductAssembly.SubProduct IS NULL) AND (dbo.ProductDetails.Product1 < =dbo.ReorderItemsTotalsSets.StockCode)
AND (dbo.ProductDetails.Product1 + dbo.productdetails.product2 < =dbo.ReorderItemsTotalsSets.ReorderQuantity)
<ryanwpenfold@.gmail.com> wrote in message =news:1191330035.815038.230250@.n39g2000hsh.googlegroups.com...
> Hi All,
> > I keep getting the following error with my WHERE clause.
> > Msg 102, Level 15, State 1, Line 47
> Incorrect syntax near '-'.
> > We've found that the code works if the segment " -
> (CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder))" is removed. So
> it's clear that the error is within this segment.
> > The object "dbo.ReorderItemsTotals" is a view and uses a UNION command
> in the SELECT statement. It joins two fields and outputs it as one
> field - "OnPurchaseOrder". The two original fields are smallints and
> the resulting UNION'd field is a varchar 10 - however I don't know why
> this is.
> > Can anyone help fix this error? My code is as follows.
> > Many thanks in advance,
> > Rype
> > > > WHERE
> (dbo.ProductAssembly.SubProduct IS NULL) AND
> ((((0.5 * dbo.SoldLast30TotalWith0Total.Despatched +
> dbo.ReorderItemsTotals.SumOfSalesBackOrder) + (0.5 / 4 *
> dbo.SoldLast120TotalWith0Total.Despatched) *
> dbo.ReorderItemsTotals.ReorderLevel * 120) - (CONVERT(Int,
> dbo.ReorderItemsTotals.Stock)) -
> (CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder)) +
> dbo.ReorderItemsTotals.SumOfSalesBackOrder) > 0) OR
> (dbo.ProductAssembly.SubProduct IS NULL) AND
> (dbo.ProductDetails.Product1 < dbo.ReorderItemsTotalsSets.StockCode)
> AND
> (dbo.ProductDetails.Product1 + dbo.productdetails.product2 <
> dbo.ReorderItemsTotalsSets.ReorderQuantity)
>
--=_NextPart_000_0007_01C804CC.9AEE5810
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Everything seems to check out using SSMS to balance the =parentheses. I do question if the logic is as you intend, given the groupings. =Having the OR out by itself like that is often not what is desired. Also, =do yourself a favor and start using aliases!! :-)
WHERE dbo.ProductAssembly.SubProduct IS NULL AND ((((0.5 * dbo.SoldLast30TotalWith0Total.Despatched + dbo.ReorderItemsTotals.SumOfSalesBackOrder) + (0.5 / 4 * dbo.SoldLast120TotalWith0Total.Despatched) * =dbo.ReorderItemsTotals.ReorderLevel * 120) - =(CONVERT(Int, dbo.ReorderItemsTotals.Stock)) - (CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder)) + dbo.ReorderItemsTotals.SumOfSalesBackOrder) > =0) OR (dbo.ProductAssembly.SubProduct IS =NULL) AND (dbo.ProductDetails.Product1 < dbo.ReorderItemsTotalsSets.StockCode) AND =(dbo.ProductDetails.Product1 + dbo.productdetails.product2 < dbo.ReorderItemsTotalsSets.ReorderQuantity)
--=_NextPart_000_0007_01C804CC.9AEE5810--
I would recommend using command parameters to avoid occasional string concatenation mishaps and possible SQL injection attacks on your code. If the tag variable is a string, it may contain a quote and it will break the syntax.