I have my PROJECT as a concatenation of some information and it looks like this:
[pre]
2016-Alaska-3(507)--AL-XYZ
2017-California-7(70)--CA-ABC
[/pre]
So let’s say I have a table with US_States:[pre]
ID State Abbrv
1 Alabama AL
2 Alaska AK
3 Arizona AZ
4 Arkansas AR
5 California CA
6 Colorado CO
...[/pre]
And I want to keep all the data for my PROJECT in a TableA, and create a lot of other tables that would refer to this table by PK-FK relation.
Intrigued by virtual columns in Oracle 11 I hoped I can employ them into concatenated PROJECT column:
[pre]
ID YEAR STATE NO1 NO2 ED PROJECT
1 2016 [blue]2[/blue] 3 507 XYZ 2016-[blue]Alaska[/blue]-3(507)--[blue]AL[/blue]-XYZ
2 2017 [blue]5[/blue] 7 70 ABC 2017-[blue]California[/blue]-7(70)--[blue]CA[/blue]-ABC
[/pre]
But turns out with virtual columns I cannot call data from other table(s), I have to use data from the same table where my virtual column is.
So what would be the best way to have TableA with all the information I need along with the full PROJECT field? I don’t really want to copy State and its abbreviation to my TableA from US_STATES table, I would rather use FK (TableA.STATE) to PK (US_STATES.ID) relation.
I know I can create a View with the data from TableA and US_STATES and have all the data I need, but then I need to connect all other tables with TableA.ID, but get the PROJECT column from this View.
Any suggestions?
Have fun.
---- Andy
There is a great need for a sarcasm font.
[pre]
2016-Alaska-3(507)--AL-XYZ
2017-California-7(70)--CA-ABC
[/pre]
So let’s say I have a table with US_States:[pre]
ID State Abbrv
1 Alabama AL
2 Alaska AK
3 Arizona AZ
4 Arkansas AR
5 California CA
6 Colorado CO
...[/pre]
And I want to keep all the data for my PROJECT in a TableA, and create a lot of other tables that would refer to this table by PK-FK relation.
Intrigued by virtual columns in Oracle 11 I hoped I can employ them into concatenated PROJECT column:
[pre]
ID YEAR STATE NO1 NO2 ED PROJECT
1 2016 [blue]2[/blue] 3 507 XYZ 2016-[blue]Alaska[/blue]-3(507)--[blue]AL[/blue]-XYZ
2 2017 [blue]5[/blue] 7 70 ABC 2017-[blue]California[/blue]-7(70)--[blue]CA[/blue]-ABC
[/pre]
But turns out with virtual columns I cannot call data from other table(s), I have to use data from the same table where my virtual column is.
So what would be the best way to have TableA with all the information I need along with the full PROJECT field? I don’t really want to copy State and its abbreviation to my TableA from US_STATES table, I would rather use FK (TableA.STATE) to PK (US_STATES.ID) relation.
I know I can create a View with the data from TableA and US_STATES and have all the data I need, but then I need to connect all other tables with TableA.ID, but get the PROJECT column from this View.
Any suggestions?
Have fun.
---- Andy
There is a great need for a sarcasm font.