Incorrect syntax near 1 !!

[JohnandSwifty]

Hi,

I'm returning the error Incorrect syntax near '1' when I run a query with the following case statement. It is possible that the orderProduct.sequence does have a value of 1, but I can't see why this would cause an issue...

Any ideas would be great.


CASE
WHEN orderProduct.sequence > 0 AND orderProduct.sequence <= 5 THEN '1-5'
WHEN orderProduct.sequence > 5 AND orderProduct.sequence <= 10 THEN '6-10'
WHEN orderProduct.sequence > 10 AND orderProduct.sequence <= 15 THEN '11-15'
WHEN orderProduct.sequence > 15 AND orderProduct.sequence <= 20 THEN '16-20'
WHEN orderProduct.sequence > 20 AND orderProduct.sequence <= 25 THEN '21-25'
WHEN orderProduct.sequence > 25 AND orderProduct.sequence <= 30 THEN '26-30'
WHEN orderProduct.sequence > 30 AND orderProduct.sequence <= 35 THEN '31-35'
WHEN orderProduct.sequence > 35 AND orderProduct.sequence <= 40 THEN '36-40'
WHEN orderProduct.sequence > 40 AND orderProduct.sequence <= 45 THEN '41-45'
WHEN orderProduct.sequence > 45 AND orderProduct.sequence <= 50 THEN '46-50'
WHEN orderProduct.sequence > 50 AND orderProduct.sequence <= 55 THEN '51-55'
WHEN orderProduct.sequence > 55 AND orderProduct.sequence <= 60 THEN '56-60'
WHEN orderProduct.sequence > 60 AND orderProduct.sequence <= 65 THEN '61-65'
WHEN orderProduct.sequence > 65 AND orderProduct.sequence <= 70 THEN '66-70'
WHEN orderProduct.sequence > 70 AND orderProduct.sequence <= 75 THEN '71-75'
ELSE '75+'
END AS sequenceGroup

 

[bborissov]

The error is not here. It should be somewhere else:

DECLARE @Temp TABLE (sequence int)

SELECT 
CASE
                            WHEN orderProduct.sequence > 0 AND orderProduct.sequence <= 5 THEN '1-5'
                            WHEN orderProduct.sequence > 5 AND orderProduct.sequence <= 10 THEN '6-10'
                            WHEN orderProduct.sequence > 10 AND orderProduct.sequence <= 15 THEN '11-15'
                            WHEN orderProduct.sequence > 15 AND orderProduct.sequence <= 20 THEN '16-20'
                            WHEN orderProduct.sequence > 20 AND orderProduct.sequence <= 25 THEN '21-25'
                            WHEN orderProduct.sequence > 25 AND orderProduct.sequence <= 30 THEN '26-30'
                            WHEN orderProduct.sequence > 30 AND orderProduct.sequence <= 35 THEN '31-35'
                            WHEN orderProduct.sequence > 35 AND orderProduct.sequence <= 40 THEN '36-40'
                            WHEN orderProduct.sequence > 40 AND orderProduct.sequence <= 45 THEN '41-45'
                            WHEN orderProduct.sequence > 45 AND orderProduct.sequence <= 50 THEN '46-50'
                            WHEN orderProduct.sequence > 50 AND orderProduct.sequence <= 55 THEN '51-55'
                            WHEN orderProduct.sequence > 55 AND orderProduct.sequence <= 60 THEN '56-60'
                            WHEN orderProduct.sequence > 60 AND orderProduct.sequence <= 65 THEN '61-65'
                            WHEN orderProduct.sequence > 65 AND orderProduct.sequence <= 70 THEN '66-70'
                            WHEN orderProduct.sequence > 70 AND orderProduct.sequence <= 75 THEN '71-75'
                            ELSE '75+'
                        END AS sequenceGroup 
FROM @Temp orderProduct

If you run the code above you will get no error.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[JohnandSwifty]

We tried this and as you say there is no error. But removing the statement fixes the query, putting it back in causes the error again. There is nothing else that could be causing it - could it be something to do with there being a field with a value of 1(which is not represented by your test)? or a configuration issue?...

Thanks

 

[bborissov]

Post whole select.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[JohnandSwifty]

Here it is...

SELECT 
orderProduct.recordID, 
orderProduct.sequence, 
CASE 
WHEN orderProduct.sequence > 0 AND orderProduct.sequence <= 5 THEN ''1-5'' 
WHEN orderProduct.sequence > 5 AND orderProduct.sequence <= 10 THEN ''6-10'' 
WHEN orderProduct.sequence > 10 AND orderProduct.sequence <= 15 THEN ''11-15'' 
WHEN orderProduct.sequence > 15 AND orderProduct.sequence <= 20 THEN ''16-20'' 
WHEN orderProduct.sequence > 20 AND orderProduct.sequence <= 25 THEN ''21-25'' 
WHEN orderProduct.sequence > 25 AND orderProduct.sequence <= 30 THEN ''26-30'' 
WHEN orderProduct.sequence > 30 AND orderProduct.sequence <= 35 THEN ''31-35'' 
WHEN orderProduct.sequence > 35 AND orderProduct.sequence <= 40 THEN ''36-40'' 
WHEN orderProduct.sequence > 40 AND orderProduct.sequence <= 45 THEN ''41-45'' 
WHEN orderProduct.sequence > 45 AND orderProduct.sequence <= 50 THEN ''46-50'' 
WHEN orderProduct.sequence > 50 AND orderProduct.sequence <= 55 THEN ''51-55'' 
WHEN orderProduct.sequence > 55 AND orderProduct.sequence <= 60 THEN ''56-60'' 
WHEN orderProduct.sequence > 60 AND orderProduct.sequence <= 65 THEN ''61-65'' 
WHEN orderProduct.sequence > 65 AND orderProduct.sequence <= 70 THEN ''66-70'' 
WHEN orderProduct.sequence > 70 AND orderProduct.sequence <= 75 THEN ''71-75'' 
ELSE ''75+'' 
END AS sequenceGroup, 
orderProduct.productID, 
orderProduct.ourCode, 
orderProduct.description, 
orderProduct.quantity, 
orderProduct.handingID, 
orderProduct.height, 
orderProduct.width, 
orderProduct.depth, 
orderProduct.internalDepth, 
orderProduct.productPrice, 
orderProduct.productCost, 
orderProduct.rowVATPrice, 
orderProduct.rowTotalPrice, 
orderProduct.rowTotalCost, 
orderProduct.createdDate, 
LEFT(CONVERT(VARCHAR,CONVERT(DATETIME,orderProduct.createdDate),113),11) AS createdDateName, 
orderProduct.isDeleted 
FROM orderProduct 
LEFT JOIN orderpb ON orderpb.recordID=orderProduct.orderID 
LEFT JOIN product ON product.recordID=orderProduct.productID 
LEFT JOIN doorStyle ON doorStyle.recordID=orderProduct.doorStyleID 
LEFT JOIN carcassStyle ON carcassStyle.recordID=orderProduct.carcassStyleID 
LEFT JOIN drawerStyle ON drawerStyle.recordID=orderProduct.drawerStyleID 
LEFT JOIN unitHanding ON unitHanding.recordID=orderProduct.handingID 
LEFT JOIN organisation ON organisation.recordID = orderpb.customerOrganisationID 
LEFT JOIN person ON person.recordID = orderpb.customerPersonID 
WHERE orderProduct.recordID <> 0 
AND orderProduct.orderID = 109109 
AND orderProduct.parentOrderProductID IS NULL 
AND orderProduct.isDeleted = 0

 

[bborissov]

That:
AS ''1-5''

Should be:
AS '1-5'


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[JohnandSwifty]

The query is run through Coldfusion 8 - it always 'optimises' the query and inturn replaces single quotes with double quotes - you think it is this that is causing the problem?

 

[bborissov]

Yes, but this is not a double quote these are two single quotes.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

use square brackets instead:

AS [1-5]
AS [6-10] etc.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[JohnandSwifty]

Found out in the end that its actually a CF bug thats been around for ages - ended up using the original query but had to preserve single quotes on the SQL (seems obvious now, but god knows why it trashes single quotes anyway).

Thanks for your help.