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,
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.