The multi-part identifier could not be bound when altering a table

Question

I'm running into an error when altering a table in an SQL server, the query is simply this:

ALTER TABLE t_OrderDetail DROP COLUMN [TotalCost];
GO
ALTER TABLE t_OrderDetail ADD TotalCost 
AS (((t_OrderDetail.[ItemQuant] * t_Item.[ItemCost]) * 17.5) / 100);

I wanted to apply the formula to the column - it is taking the cost of the item, multiplying that by the quantity bought, then adding 17.5% VAT.

This is how the tables are set up: Relationship Diagram

The error reads:

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "t_Item.ItemCost" could not be bound.

Any suggestions? I've looked around for duplicate threads but most of what I can find are based on SELECT statements rather than altering a column formula. Thanks in advance.

what is `t_Item`?? the error is very clear, the DB engine doesn't know what it is (and neither do I) — Amit, May 03 '16 at 23:33
t_Item if you'd seen the image linked in the post is another table. — RyanHx, May 03 '16 at 23:37
Disregard the fact that this can't be done, try to imagine how that would even work, and see what you get. When you do that, pretend you're the actual computer, running an actual DB engine, "don't cheat" by using what you "know" about the result you're trying to achieve. — Amit, May 03 '16 at 23:39
It's possible to reference external columns in the criteria of a field is it not? It's simply a calculation between two values held within the database — RyanHx, May 03 '16 at 23:43
Did you try to imagine what I suggested you to do? Did you pick a value from the t_Item table? which row did you pick? Why *that* row? do you see where this is going? — Amit, May 03 '16 at 23:50
It's impossible to be using both MySQL and SQL Server at the same time. One is an Oracle product, and the other a Microsoft product. Please make up your mind about which one you're using, and use **only** that tag. Tags have specific meaning here. Don't just randomly add those that seem familiar or start with the same word, as they're not the same thing. If you're not sure what a tag means, read it's description. If you're still not sure, don't use it; if it's needed, someone here will add it for you. — Ken White, May 04 '16 at 00:32
This issue was discussed on http://stackoverflow.com/questions/9696687/define-a-computed-column-reference-another-table — Alex Kudryashev, May 04 '16 at 01:49
Hi, according to the code you gave (and the table structure) there is a way of doing this but it would not be via a computed column. The TotalCost column would have to be created and then the data selected in. The problem with just slinging out an answer would be that it would not be very efficient unless you were to add unique identities to each row in the OrderDetail table. If you wish I can code it with or with out the unique id's... — russ, May 04 '16 at 17:00
Mind you, incorporating a function as described by Alex above might mean the required select query could be incorporated in a computed column howeve4r it would be an inefficient loop style thing unless the unique id's were added. A lot depends on whether you want to append new information or drop and recreate the column each time. I would suggest not to bother with the computed column. A lot depends on the expected number of rows in the OrderDetail table. — russ, May 04 '16 at 17:21
@RyanHx , re: "It's possible to reference external columns in the criteria of a field is it not?" No. It is not. — Tab Alleman, May 04 '16 at 19:58

score 0 · Answer 1 · answered May 03 '16 at 23:53

The issue is that at that point in time the database doesn't know what you are talking about.

Even if it was to figure out you where referencing another table which row would it get itemCost from?

Another issue is that isn't valid syntax; you need a datatype:

ALTER TABLE t_OrderDetail ADD TotalCost decimal(13,2);

I'd suggest building the column in one statement then populating with a join in another.

score 0 · Answer 2 · answered May 04 '16 at 01:43

The issue here is in how the computed column is calculated. A computed column can only access columns in the table where the computed column is declared -- hence, multi-part names are not appropriate.

So:

ALTER TABLE t_OrderDetail
    ADD TotalCost AS (((ItemQuant * ItemCost) * 17.5) / 100);

The multi-part identifier could not be bound when altering a table

2 Answers2