locked
Lookup Function as Default Value RRS feed

  • Question

  • I am trying to create a default value for a column in a fact table for the ID value that corresponds to a specific column value in the same row. It appears that the =LOOKUP function is what I need to do, but when I create my table with that as a default, I get errors at !. Here is what I am coding:

     

    CREATE TABLE [dbo].[ACCTG_TRAN_Tst](
    	[Tran_Id] [int] IDENTITY(1,1) NOT NULL,
    	[Acctg_Tran_Key] [int] NOT NULL,
    	[Gl_Acct_Id] [int] NULL Lookup(Fields!GL_Acct_NM.Value, 'N/A', Fields!GL_Acct_ID.Value, Bas.dbo.GL_Acct_T) 
    )
    

    So if I did an

    INSERT INTO(Acctg_Tran_Key)
    Values(1)
    

    My result should be:

    Tran_ID            Acctg_Tran_Key              GL_Acct_Id
    ----------           -----------------              ----------------
        1                           1                             2  (2 is the ID where Acct Nm = 'N/A')

     

    Any Advice?

    Saturday, February 5, 2011 12:20 AM

Answers

  • You will run into performance issues calling a function for each inserted record.

    Either replacing with a trigger or obtain the value before inserting either as part of the INSERT statement or some function (performance issue using the function option)

     

    • Marked as answer by Raymond-Lee Monday, February 21, 2011 1:37 AM
    Wednesday, February 9, 2011 2:53 AM

All replies

  • Not a direct answer, sorry.  I'm not familiar with the lookup function, or if you can use it in this fashion.

    What I can suggest is a trigger on inserts.  You can code your own lookup function and populate that vale as you like using standard SQL syntax.

    Overall, however - why do you want such a "complex" default value?  Is the process that's inserting this information not capable of supplying this information itself?


    Todd McDermid's Blog Talk to me now on
    Sunday, February 6, 2011 12:01 AM
  • You will run into performance issues calling a function for each inserted record.

    Either replacing with a trigger or obtain the value before inserting either as part of the INSERT statement or some function (performance issue using the function option)

     

    • Marked as answer by Raymond-Lee Monday, February 21, 2011 1:37 AM
    Wednesday, February 9, 2011 2:53 AM