 Using Virtual Column [message #662528] |
Fri, 05 May 2017 03:44  |
 |
kaos.tissue
Messages: 94
Registered: May 2017
|
Member |
|
|
CREATE TABLE ORDER_LINE
(
ORDER_ID, PROD_ID (PK),
QTY,
PRICE,
LINE_AMOUNT (VIRTUAL:(QTY*PRICE)),
PROD_NAME,
PROD_ALIAS
)
I want to write a procedure to check weather the given prod_id or prod_alias exists in the 'product' table. If it does then take the prod_name and price according to the prod_id or the prod_alias provided.
Then insert the values into the 'order_line' table.
Also, I need the "line_amount" for each order_id in order to calculate the total order value.
[Updated on: Fri, 05 May 2017 03:55] Report message to a moderator |
|
|
|
| Re: Using Virtual Column [message #662530 is a reply to message #662528] |
Fri, 05 May 2017 03:53  |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Please read How to use [code] tags and make your code easier to read You have benn asked to do this before.
Your CREATE TABLE statement is full of bugs:orclz>
orclz> CREATE TABLE ORDER_LINE
2 (
3 ORDER_ID, PROD_ID (PK),
4 QTY,
5 PRICE,
6 LINE_AMOUNT (VIRTUAL:(QTY*PRICE)),
7 PROD_NAME,
8 PROD_ALIAS
9 )
10 /
ORDER_ID, PROD_ID (PK),
*
ERROR at line 3:
ORA-00902: invalid datatype
orclz> |
|
|
|
|
|
|
|
|
|
|
|
| Re: Using Virtual Column [message #662537 is a reply to message #662535] |
Fri, 05 May 2017 04:08 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:Please read How to use [code] tags and make your code easier to read You have benn asked to do this before.
You have defined product_id as a foreign key, so this requirement
Quote:I want to write a procedure to check weather the given prod_id or prod_alias exists in the 'product' table.
is meaningless: the row cannot be inserted unless the prod_id exists. It begins to look as though you have jumped into coding before doing your data analysis. Perhaps start by drawing an entity relationship diagram that follows third normal form.
[Updated on: Fri, 05 May 2017 04:09] Report message to a moderator |
|
|
|
| Re: Using Virtual Column [message #662538 is a reply to message #662537] |
Fri, 05 May 2017 04:24 |
|
kaos.tissue
Messages: 94
Registered: May 2017
|
Member |
|
|
I have done that and I know what you are saying but the data comes in from a text file. There are two keys in the PRODUCT table (PROD_CODE & PROD_ALIAS). I chose PROD_ID as the PK. I agree to the fact that a row can't be inserted without a valid PROD_ID but what if the PROD_ID is not entered in the external file, instead, the PROD_ALIAS is written. If that's the case then I want to fetch the PROD_ID, PRICE according to the PROD_ALIAS since there will be cases wen the PROD_ID is not entered in the text file because of some reason, maybe the person doesn't know the id but knows the alias.
EX: For Mineral Water (Product), the PROD_ID is 37 and the ALIAS is 'MW'. If the person enters only 'MW' then I should be able to fetch the ID associated with that ALIAS. Also, the price for the same and then enter these values into the ORDER_LINE table.
[Updated on: Fri, 05 May 2017 04:25] Report message to a moderator |
|
|
|
| Re: Using Virtual Column [message #662539 is a reply to message #662538] |
Fri, 05 May 2017 04:29 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:but what if the PROD_ID is not entered in the external file, You cannot have NULL as a primary key. This is very basic relational engineering, you know.
Attempting to code something before you have done the data analysis (which, as this example shows, you have not done) is unlikely to be productive. Your posts demonstrate this perfectly.
|
|
|
|
|
|
| Re: Using Virtual Column [message #662541 is a reply to message #662539] |
Fri, 05 May 2017 04:35 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
External file != product table.
You have a product table, it has a prod id. In order to populate the product table from the file you will need to assign prod_ids. So the problem needs to be fixed at that point.
Once you get to the point of populating order_lines there will always be a prod_id. The user may not know what it is, they'll probably be entering a name, but it will be there.
|
|
|
|
| Re: Using Virtual Column [message #662542 is a reply to message #662541] |
Fri, 05 May 2017 04:38 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
ID should be the primary key and that's what you should use on the other tables that point to product. Alias may also be a unique key.
You don't need the temp table, sqlldr could just load into product and if necessary have a before insert trigger on product to assign a value to product_id if it's null.
|
|
|
|
|
|
| Re: Using Virtual Column [message #662544 is a reply to message #662543] |
Fri, 05 May 2017 04:42 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You're populating all the tables from text files?
Use external tables - it'll give you much more flexibilty.
Then you can write a procedure that'll select from the external table, filling in the missing ids as part of the select, and insert it into order lines.
|
|
|
|
|
|
| Re: Using Virtual Column [message #662548 is a reply to message #662547] |
Fri, 05 May 2017 04:54 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
This is a question of data normalization. You should not be storing the order total value: you can calculate it whenever it is needed. Similarly, you do not need a column for price*quantity. Do the data analysis BEFORE you start to code!
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
