INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Table design - virtual column question(?)

Table design - virtual column question(?)

(OP)
I have my PROJECT as a concatenation of some information and it looks like this:
2016-Alaska-3(507)--AL-XYZ
2017-California-7(70)--CA-ABC
 
So let’s say I have a table with US_States:

ID State       Abbrv
1  Alabama     AL 
2  Alaska      AK 
3  Arizona     AZ 
4  Arkansas    AR 
5  California  CA 
6  Colorado    CO 
... 
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:

ID YEAR STATE NO1 NO2 ED  PROJECT
1  2016   2    3  507 XYZ 2016-Alaska-3(507)--AL-XYZ
2  2017   5    7   70 ABC 2017-California-7(70)--CA-ABC
 
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.

RE: Table design - virtual column question(?)

Materialized view?

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity


RE: Table design - virtual column question(?)

Quote:

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.
No, you just create a view something like this:

CODE

SELECT a.*,
       a.year||'-'||s.state||'-'||a.no1||'('||a.no2||')--'||s.abbrv||'-'||a.ed
FROM   TableA a
INNER JOIN US_States s   -- or OUTER JOIN if a.state is nullable
ON s.id = a.state 
And then just use this view wherever you would have used TableA.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close