SQL Server Incorrect Syntax error

Question

Thank for your help and reading, I have the below query and I don't understand why this error message occurs:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Price'

I am able to query this data column (Wholesale Price) in a standalone query. I'm using SQL Server 2005 Management Studio .

SELECT 
    ILE.[Location Code],
    ILE.SUM(Quantity) AS "Transfer Qty",
    PP.SUM(Wholesale Price) AS "Transfer Cost (HK)"
FROM 
    [DB].[dbo].[Company$Item Ledger Entry] ILE
INNER JOIN 
    [DB].[dbo].[Company$Purchase Price] PP ON ILE.[Item No_] = PP.[Item No_] 
                                           AND ILE.[Variant Code] = PP.[Variant Code]
WHERE
    ILE.[Entry Type] = '4' 
    AND ILE.[Location Code] NOT LIKE '%STAFF%' 
    AND ILE.[Location Code] NOT LIKE 'WHSPACKAGE' 
    AND ILE.[Location Code] NOT LIKE 'WHS'
GROUP BY
    ILE.[Location Code], ILE.[Quantity], PP.[Wholesale Price]
ORDER BY 
    [Location Code]

Thanks!

Regards, Patrick

You are missing square braces around Wholesale Price.

Gordon Linoff
– Gordon Linoff

2016-11-30 02:07:07 +00:00
Commented Nov 30, 2016 at 2:07 — Gordon Linoff
– Gordon Linoff, Commented Nov 30, 2016 at 2:07

Gordon Linoff · Accepted Answer · 2016-11-30 02:09:56Z

In SQL, you have to escape names that contain special characters -- and spaces are special characters. Because reading and writing code that has lots of square braces is cumbersome, the general advice is to avoid using such names.

In your case, you are missing the square braces:

SELECT ILE.[Location Code], ILE.Sum(Quantity) as "Transfer Qty",
       Sum(PP.[Wholesale Price]) as "Transfer Cost (HK)"
FROM [DB].[dbo].[Company$Item Ledger Entry] ILE INNER JOIN
     [DB].[dbo].[Company$Purchase Price] PP
     ON ILE.[Item No_] = PP.[Item No_] AND
        ILE.[Variant Code] = PP.[Variant Code]
Where ILE.[Entry Type] = '4' AND
      ILE.[Location Code] NOT LIKE '%STAFF%' AND
      ILE.[Location Code] NOT LIKE 'WHSPACKAGE' AND
      ILE.[Location Code] NOT LIKE 'WHS'
Group by ILE.[Location Code], ILE.[Quantity]
Order by [Location Code];

In addition:

PP.SUM() doesn't make sense. The table alias goes with the column name.
Putting the wholesale price in the GROUP BY doesn't make sense. You want to aggregate the value, so it usually wouldn't go there.

Mahesh.K · Accepted Answer · 2016-11-30 06:14:38Z

0

Check this select statement for your error

SELECT 
    ILE.[Location Code],
    ILE.SUM(Quantity) AS "Transfer Qty",
    PP.SUM([Wholesale Price]) AS "Transfer Cost (HK)"--Error line

answered Nov 30, 2016 at 6:14

Mahesh.K

9016 silver badges16 bronze badges

Comments

Prabhat-VS · Accepted Answer · 2016-12-02 08:10:57Z

0

Thank you so much! Gordon!

Both SUM statement should be wording this format, Sum(ILE.[Quantity] and Sum(PP.[Wholesale Price])

    SELECT ILE.[Location Code], Sum(ILE.[Quantity]) as "Transfer Qty",
           Sum(PP.[Wholesale Price]) as "Transfer Cost (HK)"
      FROM [Dummy-28-Oct-2016].[dbo].[TEST ENV$Item Ledger Entry] ILE       
INNER JOIN [Dummy-28-Oct-2016].[dbo].[TEST ENV$Purchase Price] PP
        ON ILE.[Item No_] = PP.[Item No_] 
       AND ILE.[Variant Code] = PP.[Variant Code]
     Where ILE.[Entry Type] = '4' 
       AND ILE.[Location Code] NOT LIKE '%STAFF%' 
       AND ILE.[Location Code] NOT LIKE 'WHSPACKAGE' 
       AND ILE.[Location Code] NOT LIKE 'WHS'
  Group by ILE.[Location Code], ILE.[Quantity]
  Order by [Location Code];

edited Dec 2, 2016 at 8:10

Prabhat-VS

1,5301 gold badge21 silver badges32 bronze badges

answered Dec 2, 2016 at 6:16

Patrick Yeung

35 bronze badges

Collectives™ on Stack Overflow

SQL Server Incorrect Syntax error

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related