1

Is it possible to create a virtual column based off a query to another table? - something like the following

DateTable               
DateDt   StatusCd           
7/31     
8/1      CURRENT
8/2      
8/5
8/6
8/7 

OtherTable
Column1
Column2
VIRTUALDate = Select DateDt from DateTable where DateTable.StatusCd = 'CURRENT'

I can go more in depth in the example if needed,

MT0
  • 143,790
  • 11
  • 59
  • 117
user2210179
  • 73
  • 1
  • 7

1 Answers1

2

No, it isn't possible.

From the documentation:

The column_expression in the AS clause has the following restrictions:

  • It cannot refer to another virtual column by name.

  • Any columns referenced in column_expression must be defined on the same table.

  • It can refer to a deterministic user-defined function, but if it does, then you cannot use the virtual column as a partitioning key column.

So it can't refer to another table; and you can't have a function that does a look up on another table because that would not be deterministic.

You can use a view to achieve the effect you're looking for.

Steven
  • 1,564
  • 1
  • 22
  • 34
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • In the user-defined function, if I add some querying(from another table) and returns the result. Is it feasible? – meadlai May 18 '17 at 07:01
  • @meadlai - but then the function wouldn't be deterministic, so no. (I guess if the query was a simple look-up against a static table you could still abuse this by declaring the function as deterministic anyway, but nothing is truly static so it's dangerous, and better solutions exist - use a view or just join when querying). – Alex Poole May 18 '17 at 07:14
  • Thanks @Alex Poole I add extra column to solve this problem. – meadlai Jun 13 '17 at 08:22