Home » SQL & PL/SQL » SQL & PL/SQL » Inserts based on column values (Oracle 10.1.0.2)
Inserts based on column values [message #563584] Wed, 15 August 2012 08:31 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

I have a data in one table with 6 columns where user may be updating values in all of these 6 columns or he may enter 3 or 4 columns based on that inserts should take place, this is similar to my previous thread , i am using if condition to check column for null if its not null then i will make a insert , but is there any other easier way to do this.


CREATE TABLE ot_inspect_head 
(inh_sys NUMBER,i_txn_code VARCHAR2(12),i_no NUMBER,i_ref_txn VARCHAR2(20),i_ref_no NUMBER);

CREATE SEQUENCE inh_sys START WITH 1;

CREATE TABLE ot_inspect_item 
(ii_inh_sys NUMBER , ii_pi_sys NUMBER,ii_sys NUMBER,ii_item_cd VARCHAR2(12),
ii_grade VARCHAR2(12),ii_qty number, ii_flex_01 VARCHAR2(12),ii_flex01_qty number,
ii_flex_02 VARCHAR2(12),ii_flex02_qty number,ii_flex_03 VARCHAR2(12),ii_flex03_qty number);

CREATE SEQUENCE ii_sys START WITH 1;

insert into ot_po values ('ss-po',1,ph_sys.nextval);

insert into ot_inspect_head values (inh_sys.nextval,'ss-ins',1,'ss-po',1);

commit;

select * from ot_inspect_item 

II_INH_SYS II_PI_SYS II_SYS II_ITE II_GRADE II_QTY II_FLEX_01 II_FLEX01_QTY II_FLEX_02 II_FLEX02_QTY
---------- --------- ------ ------ -------- ------ ---------- ------------- ---------- -------------
         2         1      2 HEA100 A           100 


--Now if the inspection user issues the update statement , it will delete this row 
--from ot_inspect_item and reinserts the values with values based on 
--ii_flex_01,ii_flex_02,ii_flex_03

update ot_inspect_item 
set ii_flex_01='X',II_FLEX01_QTY=25,ii_flex_02='Y',II_FLEX02_QTY=25,ii_flex_03='Z',II_FLEX03_QTY=50
WHERE II_INH_SYS=2;

commit;

--it will create three records if he updates all ii_flex_01,ii_flex_02,ii_flex_03 , 
--if he inserts 2 fields then 2 records i want output like below.


II_INH_SYS II_PI_SYS II_SYS II_ITE II_GRADE II_QTY II_FLEX_01 II_FLEX01_QTY II_FLEX_02 II_FLEX02_QTY
---------- --------- ------ ------ -------- ------ ---------- ------------- ---------- -------------
         2         1      2 HEA100 X           25 
         2         1      2 HEA100 Y           25
         2         1      2 HEA100 Z           50

--If he updates like below

update ot_inspect_item set ii_flex_01='X',II_FLEX01_QTY=50,ii_flex_02='Y',II_FLEX02_QTY=50
WHERE II_INH_SYS=2;

commit;

--then the output should be like or what i want is
II_INH_SYS II_PI_SYS II_SYS II_ITE II_GRADE II_QTY II_FLEX_01 II_FLEX01_QTY II_FLEX_02 II_FLEX02_QTY
---------- --------- ------ ------ -------- ------ ---------- ------------- ---------- -------------
         2         1      2 HEA100 X           50 
         2         1      2 HEA100 Y           50


--I have written a proceedure by taking the backup of existing table into a temporary 
-- table and then inserting the new values as in procedure but i don't know whether 
-- my approach is right or i am following a right method, please correct me.

CREATE TABLE ot_inspect_item_bk AS SELECT * FROM ot_inspect_item WHERE 1=2

CREATE OR REPLACE PROCEDURE aft_upd_poN (p_h_sys IN NUMBER)
AS
   CURSOR c1
   IS
      SELECT ii_inh_sys, ii_pi_sys, ii_sys, ii_item_cd, ii_grade, ii_qty,
             ii_flex_01, ii_flex01_qty, ii_flex_02, ii_flex02_qty,
             ii_flex_03, ii_flex03_qty
        FROM ot_inspect_item
       WHERE ii_inh_sys = p_h_sys;

   CURSOR c2
   IS
      SELECT 1
        FROM ot_inspect_item
       WHERE     ii_inh_sys = p_h_sys
             AND NVL (ii_grade, 'X') <> NVL (ii_flex_01, 'X')
          OR NVL (ii_grade, 'X') <> NVL (ii_flex_02, 'X')
          OR NVL (ii_grade, 'X') <> NVL (ii_flex_03, 'X');

   CURSOR c3
   IS
      SELECT *
        FROM ot_inspect_item_bk
       WHERE ii_inh_sys = p_h_sys;

   m_ri_sys_id    NUMBER (20);
   m_fnd          NUMBER (1);
   m_req_qty_bu   NUMBER (20);
BEGIN
   INSERT INTO ot_inspect_item_bk
      SELECT *
        FROM ot_inspect_item
       WHERE ii_inh_sys = p_h_sys;

   IF c2%ISOPEN
   THEN
      CLOSE c2;
   END IF;

   m_fnd := NULL;

   OPEN c2;

   FETCH c2
    INTO m_fnd;

   IF c2%FOUND
   THEN
      DELETE      ot_inspect_item
            WHERE ii_inh_sys = p_h_sys;
   END IF;

   CLOSE c2;

   FOR j IN c3
   LOOP
      IF     j.ii_flex_01 IS NOT NULL THEN
         INSERT INTO ot_inspect_item
                     (ii_inh_sys, ii_pi_sys, ii_sys, ii_item_cd,
                      ii_grade, ii_qty, ii_flex_01,
                      ii_flex01_qty, ii_flex_02, ii_flex02_qty,
                      ii_flex_03, ii_flex03_qty
                     )
              VALUES (j.ii_inh_sys, j.ii_pi_sys, ii_sys.nextval, j.ii_item_cd,
                      j.ii_flex_01, j.ii_flex01_qty, j.ii_flex_01,
                      j.ii_flex01_qty, j.ii_flex_02, j.ii_flex02_qty,
                      j.ii_flex_03, j.ii_flex03_qty
                     );
     END IF;

     IF  j.ii_flex_02 IS NOT NULL THEN

         INSERT INTO ot_inspect_item
                     (ii_inh_sys, ii_pi_sys, ii_sys, ii_item_cd,
                      ii_grade, ii_qty, ii_flex_01,
                      ii_flex01_qty, ii_flex_02, ii_flex02_qty,
                      ii_flex_03, ii_flex03_qty
                     )
              VALUES (j.ii_inh_sys, j.ii_pi_sys, ii_sys.nextval, j.ii_item_cd,
                      j.ii_flex_02, j.ii_flex02_qty, j.ii_flex_01,
                      j.ii_flex01_qty, j.ii_flex_02, j.ii_flex02_qty,
                      j.ii_flex_03, j.ii_flex03_qty
                     );
      END IF;
      
      IF  j.ii_flex_03 IS NOT NULL THEN

         INSERT INTO ot_inspect_item
                     (ii_inh_sys, ii_pi_sys, ii_sys, ii_item_cd,
                      ii_grade, ii_qty, ii_flex_01,
                      ii_flex01_qty, ii_flex_02, ii_flex02_qty,
                      ii_flex_03, ii_flex03_qty
                     )
              VALUES (j.ii_inh_sys, j.ii_pi_sys, ii_sys.nextval, j.ii_item_cd,
                      j.ii_flex_03, j.ii_flex03_qty, j.ii_flex_01,
                      j.ii_flex01_qty, j.ii_flex_02, j.ii_flex02_qty,
                      j.ii_flex_03, j.ii_flex03_qty
                     );
     END IF;
      
   END LOOP;
END;
/

exec aft_upd_po (2)


SQL> select * from ot_inspect_item_bk
  2  /

II_INH_SYS II_PI_SYS II_SYS II_ITE II_GRADE II_QTY II_FLEX_01 II_FLEX01_QTY II_FLEX_02 II_FLEX02_QTY ii_flex_03 ii_flex03_qty 
---------- --------- ------ ------ -------- ------ ---------- ------------- ---------- ------------------------
         2         1      2 HEA100 A           100 X                     25 Y                     25 Z   50

SQL> select * from ot_inspect_item;

II_INH_SYS II_PI_SYS II_SYS II_ITE II_GRADE II_QTY II_FLEX_01 II_FLEX01_QTY II_FLEX_02 II_FLEX02_QTY ii_flex_03 ii_flex03_qty
---------- --------- ------ ------ -------- ------ ---------- ------------- ---------- ------------- ----------
         2         1      3 HEA100 X            25 X                     25 Y                     25 Z   50
         2         1      4 HEA100 Y            25 X                     25 Y                     25 Z   50
         2         1      5 HEA100 Z            50 X                     25 Y                     25 Z   50







Re: Inserts based on column values [message #563585 is a reply to message #563584] Wed, 15 August 2012 08:42 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
Senior Member
> IF j.ii_flex_01 IS NOT NULL THEN
> IF j.ii_flex_02 IS NOT NULL THEN
> IF j.ii_flex_03 IS NOT NULL THEN

what about II_FLEX_04, II_FLEX_05, & II_FLEX_06?

>I have a data in one table with 6 columns where user may be updating values in all of these 6 columns
if "updating" as stated above, then why doing INSERT?
Re: Inserts based on column values [message #563607 is a reply to message #563585] Wed, 15 August 2012 16:56 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks for the repsonse blackswan , actually i have to check the conditions in combination using and operator like

> IF j.ii_flex_01 IS NOT NULL AND j.ii_flex_02 IS NOT NULL THEN (ii_flex_01 will have heatcode and ii_Flex_02 its value)
> IF j.ii_flex_03 IS NOT NULL AND j.ii_flex_04 IS NOT NULL THEN (ii_flex_03 will have heatcode and ii_Flex_04 its value)
> IF j.ii_flex_05 IS NOT NULL AND j.ii_flex_06 IS NOT NULL THEN (ii_flex_05 will have heatcode and ii_Flex_06 its value)

The problem is user may enter any one of these columns like fillin j.ii_flex_03 and j.ii_flex_04 and leave other blank and they are all columns.
Re: Inserts based on column values [message #563632 is a reply to message #563585] Thu, 16 August 2012 02:25 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Dear Blackswan

Quote:
>I have a data in one table with 6 columns where user may be updating values in all of these 6 columns
if "updating" as stated above, then why doing INSERT?


Meaning user will update the columns ii_flex_01 to ii_flex_06 based on that my procedure will do the delete and insert into the same table.Number of inserts depend on data updated in ii_flex_01,ii_flex_02,ii_flex_03,ii_flex_04,ii_flex_05 and ii_flex_06,originally there will be one line with ii_grade_code_1 as A and qty , User will update all the columns with new grades or heat numbers in ii_flex_01,ii_flex_03 and ii_flex_05 with total qty distributed as 25 in ii_flex_02, 25 in ii_flex_04 and 50 in ii_flex_06 after update original line will get deleted and 3 rows will be inserted since three columns are updated.if user updated 2 and put qty as 50 and 50 2 rows will be inserted.

Re: Inserts based on column values [message #563637 is a reply to message #563632] Thu, 16 August 2012 03:07 Go to previous messageGo to next message
John Watson
Messages: 4523
Registered: January 2010
Location: Global Village
Senior Member
How far down the system development lifecycle are you? The problem is clear: your data model is not correctly normalized. You need to break down your ot_inspect_item into a table with rows for each grade and quantity ordered, and then insert more rows for each grade and quantity received. Something like
CREATE TABLE ot_inspect_item
  (
     ii_inh_sys            NUMBER,
     ii_pi_sys             NUMBER,
     ii_sys                NUMBER,
     ii_item_cd            VARCHAR2(12),
     order_or_receipt_flag VARCHAR2(1),
     grade                 VARCHAR2(12),
     qty                   NUMBER
  ); 

In my experience, trying to cover up errors made in the systems analysis phase with code is often disastrous.
Re: Inserts based on column values [message #563758 is a reply to message #563637] Thu, 16 August 2012 17:58 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks john, unfortunately table was like this since before in Erp and this is a new requirement and i cant do much in changing design
Previous Topic: character translation
Next Topic: Searching for a string in the whole database (splitted from "How does DBMS_SQL differ from Native Dynamic Code")
Goto Forum:
  


Current Time: Tue Sep 02 02:57:39 CDT 2014

Total time taken to generate the page: 0.12251 seconds