Adding missing values in external table [message #662558] |
Fri, 05 May 2017 07:40 |
|
kaos.tissue
Messages: 94 Registered: May 2017
|
Member |
|
|
CREATE TABLE "ORDER_LINE"
(
"ORDER_ID" NUMBER,
"PRODUCT_ID" NUMBER,
"QUANTITY" NUMBER,
"PRICE" NUMBER,
"PROD_NAME" VARCHAR2(50 BYTE),
"PROD_ALIAS" VARCHAR2(10 BYTE),
CONSTRAINT "OL_PK" PRIMARY KEY ("ORDER_ID", "PRODUCT_ID")
CONSTRAINT "PROD_ORDER_FK" FOREIGN KEY ("PRODUCT_ID")
REFERENCES "PRODUCT" ("PROD_CODE")
)
Data in this table comes from an external table and there are some missing values in the data.
Eg: (1 (order_id),'No Id', 2 (quantity), 100 (price),'No Name ', MW (alias))
There are two missing values (prod_id, prod_name). These values are present in the PRODUCT table. This way there might be the price missing some values and the alias missing in some.
How can I get the missing values from the PRODUCT table and then insert the data into the ORDER_LINE table?
There are two keys in the PRODUCT table (product_id, product_alias). Product_id is the chosen primary key.
I suppose I have to write a procedure to fill in the missing values and insert it into order lines. But how? help!
|
|
|
|
|
|
|
Re: Adding missing values in external table [message #662576 is a reply to message #662572] |
Fri, 05 May 2017 14:59 |
|
kaos.tissue
Messages: 94 Registered: May 2017
|
Member |
|
|
Alias is a unique key. And yes there could be chances where the alias is missing too. So can't join on alias.
But either of the two (prod_id or alias) will be present. I will post the create table for product. The product table is like a menu.
The values are already there. I posted in the question itself that the alias is a unique or alternate key. If its not possible then I'll make sure that the alias is definitely there. But can't make sure about the prod
[Updated on: Fri, 05 May 2017 15:04] Report message to a moderator
|
|
|
Re: Adding missing values in external table [message #662578 is a reply to message #662576] |
Sat, 06 May 2017 03:27 |
|
kaos.tissue
Messages: 94 Registered: May 2017
|
Member |
|
|
CREATE TABLE "PRODUCT"
( "PROD_CODE" NUMBER(5,0) NOT NULL,
"PROD_NAME" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"PROD_DESC" VARCHAR2(50 BYTE),
"DEP_ID" NUMBER(4,0),
"CAT_ID" NUMBER(3,0),
"PROD_COST" NUMBER(5,0) NOT NULL ENABLE,
"PROD_ALIAS" VARCHAR2(26 BYTE) NOT NULL ENABLE,
CONSTRAINT "PROD_PK" PRIMARY KEY ("PROD_CODE")
CONSTRAINT "CAT_FK" FOREIGN KEY ("CAT_ID")
REFERENCES "CATEGORY" ("CAT_ID") ENABLE,
CONSTRAINT "DEP_FK" FOREIGN KEY ("DEP_ID")
REFERENCES "DEPARTMENT" ("DEP_ID") ENABLE
)
The alias is not mentioned as unique but it is in actual.
|
|
|
Re: Adding missing values in external table [message #662597 is a reply to message #662578] |
Sat, 06 May 2017 07:20 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
And how ORDER_LINE.PRODUCT_ID maps into PRODUCT table? I'll assume it maps into PRODUCT.PROD_CODE. Anyway, something like:
INSERT
INTO ORDER_LINE
SELECT ORDER_ID,
NVL(
PROD_ID,
(
SELECT PROD_CODE
FROM PRODUCT P
WHERE P.PROD_NAME = E.PROD_NAME
OR P.PROD_ALIAS = E.PROD_ALIAS
)
) PRODUCT_ID,
QUANTITY,
PRICE,
NVL(
PROD_NAME,
(
SELECT PROD_NAME
FROM PRODUCT P
WHERE P.PROD_ALIAS = E.PROD_ALIAS
)
) PROD_NAME,
NVL(
PROD_ALIAS,
(
SELECT PROD_ALIAS
FROM PRODUCT P
WHERE P.PROD_NAME = E.PROD_NAME
)
) PROD_ALIAS
FROM EXTERNAL_TABLE E
/
SY.
[Updated on: Sat, 06 May 2017 07:21] Report message to a moderator
|
|
|