I have following the syntax error "Incorrect syntax near '*='."

Question

I tried to fix that code many times, i don't know that syntax is not valid for SQL Server 2012. So please fix it

WHERE 
    invoice_register.trans_type_id IN (1, 2, 3, 6, 7, 8, 9) AND
    invoice_register.store_id = invoice_detail.store_id AND
    invoice_register.invoice_register_id = invoice_detail.invoice_register_id AND
    ii.item_id = invoice_detail.item_id AND
    ii.style_master_id *= sm.style_master_id AND /*Incorrect Syntax*/
    (@a_category_id = -9999 OR ii.category_id = @a_category_id ) AND
    (@a_store_id is NULL OR invoice_register.store_id = @a_store_id ) AND
    (invoice_register.trans_date between @a_Start_Date and @a_End_Date)) A
GROUP BY 
    A.store_id, A.category_id,
    A.type_id, A.item_id, A.item_code,
    A.name, A.cost, A.price_sold,
    A.manufacturer, A.style_number,
    A.comments, A.UPC

Please fix my syntax. I think my syntax is old

That is very, very old JOIN syntax, I suggest you go learn current methods — DavidG, Jul 19 '18 at 22:33
Please add your `FROM` / `JOIN` clause to the example. The replacement for `*=` goes there. — John Wu, Jul 19 '18 at 22:40
"I think my syntax is old" - you'd be right. Explicit ANSI join syntax was added to the standard in 1992. That's more than a quarter of a century ago! You should have had a chance to learn it by now, since it's also been supported in SQL Server for all of this millennium, at least. — Damien_The_Unbeliever, Jul 20 '18 at 06:25

score 0 · Answer 1 · answered Jul 19 '18 at 22:47

Three points here:

Firstly, You are showing us part of an subquery and the outer query - you should indent the subquery for readability.

Secondly, also on the topic of readability, you should use a more meaningful name than A for your subquery.

The main issue, as pointed out in the comments, is that you are using old non-ANSI-standard syntax to join your tables. Instead of putting that information in the WHERE clause, you should do that in the JOIN clause and leave the WHERE clause for actual record-level filter conditions.

Your from clause should look like this:

FROM
    invoice_register
    INNER JOIN invoice_detail ON invoice_register.invoice_register_id = invoice_detail.invoice_register_id
    INNER JOIN ii ON ii.item_id = invoice_detail.item_id
    LEFT JOIN sm ON ii.style_master_id = sm.style_master_id

You then remove all these conditions from the WHERE clause.

Presumably the ii and sm are aliases for inventory_item and style_master - adjust your FROM clause to suit.

Don't underestimate the point about the conceptual separation of filter conditions (WHERE) from join conditions (FROM) - and aggregate conditions (HAVING). It will be clearer for a reader when you indicate your intent.

I have following the syntax error "Incorrect syntax near '*='."

1 Answers1