Title: CTE error

Question

I am getting these errors while executing the following SQL please help me

;WITH myCTE AS
(Select mcisi.* from
coke_packaged_item AS spi
JOIN coke_item AS si
  ON si.coke_id = spi.coke_id
  AND si.coke_item_id = spi.coke_item_id
  AND si.shipper_flag = 'n'
JOIN merch_cat_import_coke_item AS mcisi
  ON mcisi.coke_id = si.coke_id
  AND mcisi.resolved_coke_item_id = si.coke_item_id
  AND mcisi.cat_import_event_id = @cat_import_event_id
  AND mcisi.accept_flag = 'y')


UPDATE coke_packaged_item
SET packaged_in_uom_id = (select resolved_packaged_unit_of_measure_id from myCTE where myCTE.coke_id = coke_id) 
  priced_in_uom_id = COALESCE((select resolved_weight_unit_of_measure_idfrom myCTE.coke_id = coke_id), @each_uom_id), 
  name = (select packaged_item_name from myCTE where myCTE.coke_id = coke_id), 
  package_weight = (select package_weight from myCTE where myCTE.coke_id = coke_id) ,
  status_code = (select status_code from myCTE where myCTE.coke_id = coke_id) ,
  last_modified_user_id = (select CASE WHEN last_modified_user_id = 42 THEN @posting_user_id ELSE last_modified_user_id END from myCTE where myCTE.coke_id = coke_id),
  last_modified_timestamp = CURRENT_TIMESTAMP
and exists (select coke_id from coke_item AS si
  where si.coke_id = spi.coke_id
  AND si.coke_item_id = spi.coke_item_id
  AND si.shipper_flag = 'n')
and exists (select coke_id from merch_cat_import_coke_item AS mcisi)
  where mcisi.coke_id = si.coke_id
  AND mcisi.resolved_coke_item_id = si.coke_item_id
  AND mcisi.cat_import_event_id = @cat_import_event_id
  AND mcisi.accept_flag = 'y'

The error message is:

Msg 102, Level 15, State 1, Procedure Sp_Name, Line 44
Incorrect syntax near 'priced_in_uom_id'.
Msg 102, Level 15, State 1, Procedure Sp_Name, Line 44
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Procedure Sp_Name, Line 45
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure Sp_Name, Line 46
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure Sp_Name, Line 47
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure Sp_Name, Line 48
Incorrect syntax near ','.
Msg 156, Level 15, State 1, Procedure Sp_Name, Line 54
Incorrect syntax near the keyword 'and'.
Msg 156, Level 15, State 1, Procedure Sp_Name, Line 55
Incorrect syntax near the keyword 'where'.

Answer 1

Looks like you're missing a comma at the end of the first SET line

SET packaged_in_uom_id = (select resolved_packaged_unit_of_measure_id from myCTE where myCTE.coke_id = coke_id),

Answer 2

You're missing a Comma at line 44 after myCTE.coke_id = coke_id)

Answer 3

You're missing a comma here

SET packaged_in_uom_id = (select resolved_packaged_unit_of_measure_id from myCTE where myCTE.coke_id = coke_id) ,

Also on the next line down you're missing a space before the from and a where

select resolved_weight_unit_of_measure_idfrom myCTE.coke_id = coke_id

there might be other errors I'll leave you to find them

@SmartestVEGA You've posted loads of these sort of questions today. I suggest getting hold of a copy of SQL2008 Management Studio with its syntax checking. I pasted your query in and could see the issue very quickly.

Answer 4

My friend, I've been looking @ your code, and so far, all I've really found are typing errors.

For instance, * in the set clause, there is no comma between the set for packaged_in_uom_id and priced_in_uom_id * in the subquery for priced_in_uom_id, there is no space in front of the from clause.

My advice is to adopt a stylized pattern for how you write you sql. The pattern I use says that * each clause goes on its own line - select, from where, order by, group by * each column goes on its own line * list commas go BEFORE the item

when I began to format your code using these patterns, I began to see the problems.

I enforce these patterns using RedGate's SQL Refactor. It's the best SQL code formatter I've found.