Home » SQL & PL/SQL » SQL & PL/SQL » Using Virtual Column (Oracle 11g R2, pl/sql)
icon5.gif  Using Virtual Column [message #662528] Fri, 05 May 2017 03:44 Go to next message
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 Go to previous messageGo to next message
John Watson
Messages: 7608
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>
Is this a school homework question? To start with, you need to specify a data type for each column.
Re: Using Virtual Column [message #662531 is a reply to message #662530] Fri, 05 May 2017 03:58 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
Yeah I have specified the datatypes in real. This is just to give a basic view of the table and its columns. The problem here is the procedure. Its not a homework problem, I am just trying to learn in order create a database for my own company.
Re: Using Virtual Column [message #662533 is a reply to message #662531] Fri, 05 May 2017 03:59 Go to previous messageGo to next message
John Watson
Messages: 7608
Registered: January 2010
Location: Global Village
Senior Member
So what are your CREATE TABLE statements?
Re: Using Virtual Column [message #662534 is a reply to message #662533] Fri, 05 May 2017 04:01 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
CREATE TABLE ORDER_LINE
( "ORDER_ID" NUMBER,
"PRODUCT_ID" NUMBER,
"QUANTITY" NUMBER,
"PRICE" NUMBER,
"LINE_AMOUNT" NUMBER GENERATED ALWAYS AS ("QUANTITY"*"PRICE") VIRTUAL VISIBLE ,
"PROD_NAME" VARCHAR2(50 BYTE),
"PROD_ALIAS" VARCHAR2(10 BYTE),

CONSTRAINT "COP_PK" PRIMARY KEY ("ORDER_ID", "PRODUCT_ID")

CONSTRAINT "PROD_ORDER_FK" FOREIGN KEY ("PRODUCT_ID")
REFERENCES "PRODUCT" ("PROD_CODE")
)
Re: Using Virtual Column [message #662535 is a reply to message #662534] Fri, 05 May 2017 04:02 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
This is basically a bridge table between "ORDER" and "PRODUCT" tables
Re: Using Virtual Column [message #662537 is a reply to message #662535] Fri, 05 May 2017 04:08 Go to previous messageGo to next message
John Watson
Messages: 7608
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 7608
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 #662540 is a reply to message #662539] Fri, 05 May 2017 04:35 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
Ok here's the thing. There's a text file 'loader.txt'. I use sqlldr to load the file into a temp table WITHOUT the PROD_ID in some of the values. Then I create an AFTER INSERT TRIGGER on the temp table to call the procedure. In the procedure I fetch the PROD_ID in the cases where it is not provided. Once I have the IDs then I insert into the table. Will that be possible? Or I'll have to change the PK of the table itself, because I now realize that the ALIAS is a better key than the ID for this table.
Re: Using Virtual Column [message #662541 is a reply to message #662539] Fri, 05 May 2017 04:35 Go to previous messageGo to next message
cookiemonster
Messages: 13264
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 Go to previous messageGo to next message
cookiemonster
Messages: 13264
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 #662543 is a reply to message #662541] Fri, 05 May 2017 04:39 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
@cookiemonster Yes exactly the prod_ids are there but what happens if the data coming in from the text file to populate the order_line table does not contain the ids? How do the IDs get into the order_line table.
Re: Using Virtual Column [message #662544 is a reply to message #662543] Fri, 05 May 2017 04:42 Go to previous messageGo to next message
cookiemonster
Messages: 13264
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 #662547 is a reply to message #662544] Fri, 05 May 2017 04:51 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
I am only populating the 'order_line' table from a text file. I don't know about external tables but I will definitely now learn about that. The main problem is to get the 'sum(line_amount)' and how to get it into the 'order' table?

The orde_line table can contain values like this for order id:
EX: 1st row: (1(order_id), 40(prod_id), 3(qty), 100(price).....) line_amount=300
2nd row: (1(order_id), 50(prod_id), 1(qty), 50(price)......) line_amount=50

Now in order to get the total value of the order_id=1 how do i get the value into another table?
IF i DO SUM(LINE_AMOUNT) it will add the entire column having all the order ids. If I group it by order_id in a view, how do I get it into the second table with total of that order in the 'order' table?
Re: Using Virtual Column [message #662548 is a reply to message #662547] Fri, 05 May 2017 04:54 Go to previous messageGo to next message
John Watson
Messages: 7608
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!
Re: Using Virtual Column [message #662549 is a reply to message #662548] Fri, 05 May 2017 05:00 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
@John The (qty*price) is a virtual column anyway. What if I want to see all the orders in the past 10 days with their total amount?
I create a view for that? Is that it?
Re: Using Virtual Column [message #662550 is a reply to message #662549] Fri, 05 May 2017 05:01 Go to previous messageGo to next message
John Watson
Messages: 7608
Registered: January 2010
Location: Global Village
Senior Member
Do your data analysis. Then start thinking about processes.
Re: Using Virtual Column [message #662551 is a reply to message #662550] Fri, 05 May 2017 05:06 Go to previous message
kaos.tissue
Messages: 94
Registered: May 2017
Member
Alright thanks bro. I'll do that and external tables. Thanks guys.Cheers!
Previous Topic: ORA-14097
Next Topic: Get Return Value from Function without SELECT
Goto Forum:
  


Current Time: Tue Oct 16 01:52:41 CDT 2018