The multi-part identifier "si.shi" could not be bound.

Question

i am getting the error as shown below :

The multi-part identifier "si.shi" could not be bound.

while executing this SQL:

;WITH myCTE AS
(Select mci.* from
view_name AS si
JOIN merch_ctlg_ipt_view_name AS mci
  ON mci.view_id = si.view_id
  AND mci.resolved_view_name_id = si.view_name_id
  AND mci.ctg_ipt_event_id = @ctg_ipt_event_id
  AND mci.accept_flag = 'y')

UPDATE view_name
SET name = (select mci.name from myCTE where myCTE.view_id = view_id)
,view_name_ctg_id = (select mci.resolved_view_name_ctg_id from myCTE where myCTE.view_id = view_id)
,xref_value = (select mci.xref_value from myCTE where myCTE.view_id = view_id)
,availability_start_date = (select mci.availability_start_date from myCTE where myCTE.view_id = view_id)
,availability_end_date = (select mci.availability_end_date from myCTE where myCTE.view_id = view_id)
,status_code = (select mci.status_code from myCTE where myCTE.view_id = view_id)
,last_modified_user_id = (select CASE WHEN mci.last_modified_user_id = 42 THEN @posting_user_id ELSE mci.last_modified_user_id END from myCTE where myCTE.view_id = view_id)
,last_modified_timestamp = CURRENT_TIMESTAMP
WHERE si.shi_flag = 'n'
and exists (select view_id from merch_ctlg_ipt_view_name AS mci
  Where mci.view_id = view_name.view_id
  AND mci.resolved_view_name_id = view_name.view_name_id
  AND mci.ctg_ipt_event_id = @ctg_ipt_event_id
  AND mci.accept_flag = 'y')

Below is the structures for view and table:

CREATE TABLE [dbo].[Merch_ctlg_ipt_View_Name](
    [ctlg_ipt_event_id] [int] NOT NULL,
    [supplier_id] [int] NOT NULL,
    [View_Name_id] [int] NOT NULL,
    [name] [nvarchar](50) COLLATE SQL_AltDiction_Pref_CP850_CI_AS NOT NULL,
    [resolved_View_Name_id] [int] NULL,
    [packaged_item_name] [nvarchar](50) COLLATE SQL_AltDiction_Pref_CP850_CI_AS NULL,
    [packaged_item_id] [int] NULL,
    [View_Name_code] [nvarchar](50) COLLATE SQL_AltDiction_Pref_CP850_CI_AS NOT NULL,
    [status_code] [nchar](1) COLLATE SQL_AltDiction_Pref_CP850_CI_AS NOT NULL,
    [action_code] [nchar](1) COLLATE SQL_AltDiction_Pref_CP850_CI_AS NOT NULL,
    [xref_value] [nvarchar](50) COLLATE SQL_AltDiction_Pref_CP850_CI_AS NULL,
    [View_Name_ctg_name] [nvarchar](50) COLLATE SQL_AltDiction_Pref_CP850_CI_AS NULL,
    [resolved_View_Name_ctg_id] [int] NULL,
    [packaged_unit_of_measure_name] [nvarchar](50) COLLATE SQL_AltDiction_Pref_CP850_CI_AS NOT NULL,
    [packaged_unit_of_measure_factor] [numeric](14, 6) NULL,
    [resolved_packaged_unit_of_measure_id] [int] NULL,
    [package_weight] [numeric](16, 8) NULL,
    [availability_start_date] [datetime] NULL,
    [availability_end_date] [datetime] NULL,
    [client_id] [int] NOT NULL,
    [last_mod_user_id] [int] NOT NULL,
    [weight_unit_of_measure_name] [nvarchar](50) COLLATE SQL_AltDiction_Pref_CP850_CI_AS NULL,
    [last_mod_timestamp] [datetime] NOT NULL,
    [resolved_weight_unit_of_measure_id] [int] NULL,
    [accept_flag] [nchar](1) COLLATE SQL_AltDiction_Pref_CP850_CI_AS NOT NULL DEFAULT ('y'),
    [View_Name_ctg_description] [nvarchar](255) COLLATE SQL_AltDiction_Pref_CP850_CI_AS NULL,
    [changed_catch_weight_flag] [nchar](1) COLLATE SQL_AltDiction_Pref_CP850_CI_AS NOT NULL DEFAULT ('n'),
 CONSTRAINT [PK_Merch_ctlg_ipt_View_Name] PRIMARY KEY CLUSTERED 
(
    [ctlg_ipt_event_id] ASC,
    [supplier_id] ASC,
    [View_Name_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


SELECT     l.supplier_id, l.View_Name_id, b.View_Name_ctg_id, b.name, b.description, b.brand_id, b.status_code, l.last_mod_user_id, 
                      l.last_mod_timestamp, l.client_id, b.manufacturer_id, b.mfr_id_edit_flag, b.usage, b.xref_value, b.ingredients, b.shi_flag, 
                      b.country_of_origin_type_code, b.availability_start_date, b.availability_end_date, b.country_of_origin_required_flag, b.exception_status_code, 
                      b.resolved_View_Name_id, b.maximum_order_quantity, b.item_id, b.item_hierarchy_id, l.first_order_multiplier, l.second_order_multiplier, 
                      l.first_order_round_rule, l.second_order_round_rule, b.original_supplier_id
FROM         dbo.View_Name_Su_List AS l LEFT OUTER JOIN
                      dbo.View_Name_Base AS b ON b.View_Name_id = l.View_Name_id

Please help

Answer 1

Your update uses si.shi.

This alias is only defined in the CTE. Removing the alias from the update should resolve the error.

;WITH myCTE AS
(Select mci.* from
view_name AS si
JOIN merch_ctlg_ipt_view_name AS mci
  ON mci.view_id = si.view_id
  AND mci.resolved_view_name_id = si.view_name_id
  AND mci.ctg_ipt_event_id = @ctg_ipt_event_id
  AND mci.accept_flag = 'y')

UPDATE view_name
SET name = (select mci.name from myCTE where myCTE.view_id = view_id)
,view_name_ctg_id = (select mci.resolved_view_name_ctg_id from myCTE where myCTE.view_id = view_id)
,xref_value = (select mci.xref_value from myCTE where myCTE.view_id = view_id)
,availability_start_date = (select mci.availability_start_date from myCTE where myCTE.view_id = view_id)
,availability_end_date = (select mci.availability_end_date from myCTE where myCTE.view_id = view_id)
,status_code = (select mci.status_code from myCTE where myCTE.view_id = view_id)
,last_modified_user_id = (select CASE WHEN mci.last_modified_user_id = 42 THEN @posting_user_id ELSE mci.last_modified_user_id END from myCTE where myCTE.view_id = view_id)
,last_modified_timestamp = CURRENT_TIMESTAMP
WHERE shi_flag = 'n'
and exists (select view_id from merch_ctlg_ipt_view_name AS mci
  Where mci.view_id = view_name.view_id
  AND mci.resolved_view_name_id = view_name.view_name_id
  AND mci.ctg_ipt_event_id = @ctg_ipt_event_id
  AND mci.accept_flag = 'y')

Answer 2

Why the CTE at all? Would this not acheive the same thing more clearly?

Edit: Updated with correct alias for update statement

UPDATE si
SET  name = mci.name
    ,view_name_ctg_id = mci.resolved_view_name_ctg_id 
    ,xref_value = mci.xref_value 
    ,availability_start_date = mci.availability_start_date 
    ,availability_end_date = mci.availability_end_date 
    ,status_code = mci.status_code 
    ,last_modified_user_id = CASE WHEN mci.last_modified_user_id = 42 THEN @posting_user_id ELSE mci.last_modified_user_id END 
    ,last_modified_timestamp = CURRENT_TIMESTAMP
FROM    view_name as si
        INNER JOIN
            merch_ctlg_ipt_view_name as mci
            ON mci.view_id = si.view_id
            AND mci.resolved_view_name_id = si.view_name_id
WHERE   mci.ctg_ipt_event_id = @ctg_ipt_event_id
AND     mci.accept_flag = 'y'
AND     si.shi_flag = 'n'

Answer 3

The problem is with this line:

WHERE si.shi_flag = 'n'

There is no si alias defined in the update query.

Is should probably be:

WHERE shi_flag = 'n'

Update (following comment)

You should also change this line:

SET name = (select mci.name from myCTE where myCTE.view_id = view_id)

As the sub-query is referencing an alias that it doesn't declare:

SET name = (select name from myCTE where myCTE.view_id = view_id)