Post reply

Whats wrong with my SQL code?HELP Please!!!

  • November 9, 2008 at 11:38 am

    #383337

    Hello

    I am trying to get a table attribute to calculate the result of 2 attribute in another table. I am using SQL Server 2005. I got another table to do this and it works fine, but the only difference is is that the attributes are in the same table. My code is posted below but it does not Work. I get the message:

    -------------------------------------------------------------------------

    The multi-part identifier "ITEM_PURCHASE.LocalCurrencyAmt" could not be bound.

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "ITEM_PURCHASE.ExchangeRate" could not be bound.

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "ITEM_PURCHASE.LocalCurrencyAmt" could not be bound.

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "ITEM_PURCHASE.ExchangeRate" could not be bound.

    ------------------------------------------------------------------------

    CREATE TABLE WAREHOUSE(

    ItemNumberSK int NOT NULL,

    ItemName char(50) NOT NULL,

    ItemUnitPrice as CASE

    WHEN ITEM_PURCHASE.LocalCurrencyAmt > 0 AND ITEM_PURCHASE.ExchangeRate > 0 Then ITEM_PURCHASE.LocalCurrencyAmt * ITEM_PURCHASE.ExchangeRate * 1.2

    END,

    ItemQty numeric(7, 0) NOT NULL,

  • November 9, 2008 at 12:12 pm

    #896066

    A calculated column can only refer to columns within the same table.

    From Books online

    A computed column is a virtual column that is not physically stored in the table but is computed from an expression that uses other columns in the same table. For example, a computed column could have the definition: cost AS price * qty. The expression can be a noncomputed column name, constant, function, variable, and any combination of these connected by one or more operators. The expression cannot be a subquery or include an alias data type.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 10, 2008 at 3:43 pm

    #896624

    You could, however, create a view that would join the two tables and present the calculated results in the form you want.

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply