Home » SQL & PL/SQL » SQL & PL/SQL » Adding missing values in external table (Oracle 11g R2, pl/sql)
icon5.gif  Adding missing values in external table [message #662558] Fri, 05 May 2017 07:40 Go 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, 

"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 #662559 is a reply to message #662558] Fri, 05 May 2017 07:54 Go to previous messageGo to next message
BlackSwan
Messages: 26182
Registered: January 2009
Location: SoCal
Senior Member
If/when PROD_ID is missing, how to decide or determine what is correct value for any specific row?
Re: Adding missing values in external table [message #662560 is a reply to message #662559] Fri, 05 May 2017 08:17 Go to previous messageGo to next message
cookiemonster
Messages: 13263
Registered: September 2008
Location: Rainy Manchester
Senior Member
If prod_alias is always present and is unique in product then you just need to join the external table to product on prod_alias
Re: Adding missing values in external table [message #662571 is a reply to message #662560] Fri, 05 May 2017 12:27 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
@blackswan you can identify using the alias.
@cookiemonster I join it in a view? And then fetch the values from there using a procedure?
Re: Adding missing values in external table [message #662572 is a reply to message #662571] Fri, 05 May 2017 12:41 Go to previous messageGo to next message
BlackSwan
Messages: 26182
Registered: January 2009
Location: SoCal
Senior Member
kaos.tissue wrote on Fri, 05 May 2017 10:27
@blackswan you can identify using the alias.
@cookiemonster I join it in a view? And then fetch the values from there using a procedure?
Above seems to contradict below

>This way there might be the price missing some values and the alias missing in some.
Besides what enforces that alias is UNIQUE?

It would be most helpful if you posted both CREATE TABLE statements plus INSERT statements populating both tables.
Next post expected/desired results in addition to explanation why those are only correct results.
Re: Adding missing values in external table [message #662576 is a reply to message #662572] Fri, 05 May 2017 14:59 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
Solomon Yakobson
Messages: 2804
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

Previous Topic: Get Return Value from Function without SELECT
Next Topic: how to add to the right side of my table, the colums and data of 2 other tables .
Goto Forum:
  


Current Time: Mon Oct 15 05:17:17 CDT 2018