Forum Stats

  • 3,782,585 Users
  • 2,254,669 Discussions
  • 7,880,131 Comments

Discussions

Retrieving data before insert statement in procedure

Bufoss
Bufoss Member Posts: 123 Red Ribbon
edited Jul 20, 2015 12:51PM in SQL & PL/SQL

Hi,
I am new in plsql programming and I would like to make a procedure.I have tables like the following TABLE1

|COL1 | COL2 | COL3 | COL4 | COL5 | COL6 |COL7|
| 600 | 140 | 2 | 10 | 1300 | 500 | 1 |
| 600 | 140 | 2 | 20 | 1400 | 340 | 4 |
| 600 | 140 | 2 | 15 | 1400 | 230 | 3 |
| 600 | 140 | 2 | 35 | 1700 | 120 | 2 |
| 600 | 150 | 3 | 10 | 1300 | 166 | 6 |
| 600 | 150 | 3 | 15 | 1400 | 435 | 5 |

For the same COL1 and COL2/COL3 , check the select different values from COL4
For instance for COL1=600 , COL2=140/COL3=2 and COL2=150/COL3=3
Return 20 and 35 as not common values


Then insert in this table TABLE1 the rows
600 , 150 , 3, 20 , 1400 , 340, 7
600 , 150 , 3, 35 , 1700 , 120, 8


I am trying to make the procedure like below but I have problem how to retrieve data in insert statement

PROCEDURE COPY_COLUMNS  ( P_COL1        IN  A.COL1%TYPE,
                          P_FROM_COL2   IN  B.COL2%TYPE,
                          P_FROM_COL3   IN  B.COL3%TYPE,
                          P_TO_COL2     IN  B.COL2%TYPE,
                          P_TO_COL3     IN  B.COL3%TYPE,
                          P_FLG1        IN  VARCHAR2,
                          P_FLG2        IN  VARCHAR2,
                          P_FLG3        IN  VARCHAR2                                      
                                     ) IS

CURSOR CFL1 IS select COL4
    FROM TABLE1
    WHERE COL1 = P_COL1 AND COL2 = P_FROM_COL2 AND COL3 = P_FROM_COL3
    MINUS
    select COL4
    FROM TABLE1
    WHERE COL1 = P_COL1 AND COL2 = P_TO_COL2 AND COL3 = P_TO_COL3;


CURSOR CFL2 IS select COL4
    FROM TABLE2
    WHERE COL1 = P_COL1 AND COL2 = P_FROM_COL2 AND COL3 = P_FROM_COL3
    MINUS
    select COL4
    FROM TABLE2
    WHERE COL1 = P_COL1 AND COL2 = P_TO_COL2 AND COL3 = P_TO_COL3;


CURSOR CFL3 IS select COL4
    FROM TABLE3
    WHERE COL1 = P_COL1 AND COL2 = P_FROM_COL2 AND COL3 = P_FROM_COL3
    MINUS
    select COL4
    FROM TABLE3
    WHERE COL1 = P_COL1 AND COL2 = P_TO_COL2 AND COL3 = P_TO_COL3;  


V_REC        CFL1%ROWTYPE;


BEGIN


IF P_FLG1='N' OR P_FLG2='N' OR P_FLG3='N' THEN
    GOTO label; --do nothing
END IF;


IF P_FLG1 = 'Y' THEN

    OPEN CFL1;
    FETCH CFL1 INTO V_REC;
    CLOSE C1;

--    SELECT COL5, COL6
--    FROM TABLE1
--    WHERE COL1 = P_COL1 AND COL2 = P_FROM_COL2 AND COL3 = P_FROM_COL3 AND COL4 = V_REC.COL4;


    FOR REC IN CFL1 LOOP
        INSERT INTO TABLE1 
            SELECT P_COL1, P_TO_COL2, P_TO_COL3, CFL1.COL4, -- COL5 ?? , COL6 ?? -- , SEQname2.NEXTVAL) 


    END LOOP;

END IF;

<<label>>
END;

Could you help me please do it ?
Thanks in advance

Tagged:
Rajesh123

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,018 Red Diamond
    edited Jul 19, 2015 5:33PM Accepted Answer

    And if you want to insert missing COL4 values both ways, use full join, as I already showed (slightly modified):


    SQL> insert
      2    into table1
      3    values(600,150,3,70,1500,567,8)
      4  /

    1 row created.

    SQL> insert
      2    into table1
      3    values(600,150,3,90,1900,789,9)
      4  /

    1 row created.

    SQL> select  *
      2    from  table1
      3  /

          COL1      COL2      COL3      COL4      COL5      COL6      COL7
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
          600        140          2        10      1300        500          1
          600        140          2        20      1400        340          4
          600        140          2        15      1400        230          3
          600        140          2        35      1700        120          2
          600        150          3        10      1300        166          6
          600        150          3        15      1400        435          5
          600        150          3        70      1500        567          8
          600        150          3        90      1900        789          9

    8 rows selected.

    SQL> insert
      2    into table1
      3    with t1 as (
      4                select  *
      5                  from  table1
      6                  where col1 = :P_COL1
      7                    and col2 = :P_FROM_COL2
      8                    and col3 = :P_FROM_COL3
      9                    and :P_FLG1 = 'Y'
    10              ),
    11        t2 as (
    12                select  *
    13                  from  table1
    14                  where col1 = :P_COL1
    15                    and col2 = :P_TO_COL2
    16                  and col3 = :P_TO_COL3
    17                    and :P_FLG1 = 'Y'
    18              )
    19    select  :P_COL1 col1,
    20            nvl2(t1.col4,:P_TO_COL2,:P_FROM_COL2) col2,
    21            nvl2(t1.col4,:P_TO_COL3,:P_FROM_COL3) col3,
    22            nvl(t1.col4,t2.col4) col4,
    23            null,
    24            null,
    25            null
    26      from      t1
    27            full join
    28                t2
    29              on t2.col4 = t1.col4
    30      where t1.col4 is null
    31        or t2.col4 is null
    32  /

    4 rows created.

    SQL> select  *
      2    from  table1
      3  /

          COL1      COL2      COL3      COL4      COL5      COL6      COL7
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
          600        140          2        10      1300        500          1
          600        140          2        20      1400        340          4
          600        140          2        15      1400        230          3
          600        140          2        35      1700        120          2
          600        150          3        10      1300        166          6
          600        150          3        15      1400        435          5
          600        150          3        70      1500        567          8
          600        150          3        90      1900        789          9
          600        140          2        70
          600        140          2        90
          600        150          3        35

          COL1      COL2      COL3      COL4      COL5      COL6      COL7
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
          600        150          3        20

    12 rows selected.

    SQL>

    SY.

    Rajesh123

Answers

  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    edited Jul 19, 2015 12:22PM

    Hi,

    For the same COL1 and COL2/COL3 , check the select different values from COL4
    For instance for COL1=600 , COL2=140/COL3=2 and COL2=150/COL3=3
    Return 20 and 35 as not common values
    
    

    I didn't get you on this logic, how did you selected 20 and 30, can you elaborate

    Update : are you looking for this , just give insert into table  and this query

    SQL> with tab1 as
    (
    select '600' col1, '140' col2, '2' col3, '10' col4,'1300' col5,'500' col6,'1' col7 from dual union all
    select '600' col1, '140' col2, '2' col3, '20' col4,'1400' col5,'340' col6,'1' col7 from dual union all
    select '600' col1, '140' col2, '2' col3, '10' col4,'1400' col5,'230' col6,'1' col7 from dual union all
    select '600' col1, '140' col2, '2' col3, '35' col4,'1700' col5,'120' col6,'1' col7 from dual union all
    select '600' col1, '150' col2, '3' col3, '10' col4,'1300' col5,'166' col6,'1' col7 from dual union all
    select '600' col1, '150' col2, '3' col3, '15' col4,'1400' col5,'435' col6,'1' col7 from dual
    )
    , grp1 as (
    select *
    from tab1
    where col1 = 600
    and   col2=140
    )
    , grp2 as (
    select *
    from tab1
    where col1 = 600
    and   col2=150
    )
    select distinct res.*
    from grp1 res , (
    select p1.*
    from grp1 p1, grp2 p2
    where p1.col1 = p2.col1
    and p1.col4 = p2.col4
    ) ex
    where res.col1 = ex.col1
    and res.col2 = ex.col2
    and res.col4 <> ex.col4  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31  ; COL COL C CO COL5 COL C
    --- --- - -- ---- --- -
    600 140 2 20 1400 340 1
    600 140 2 35 1700 120 1

    - Pavan Kumar N

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,018 Red Diamond
    edited Jul 19, 2015 12:50PM

    No need for procedure. Use, for example, full outer join:


    with t1 as (

                select  *

                  from  table1

                  where col1 = 600

                    and col2 = 140

                    and col3 = 2

               ),

         t2 as (

                select  *

                  from  table1

                  where col1 = 600

                    and col2 = 150

                    and col3 = 3

               )

    select  nvl(t1.col4,t2.col4) col4

      from      t1

            full join

                t2

              on t2.col4 = t1.col4

      where t1.col4 is null

        or t2.col4 is null

    /


          COL4
    ----------
            20
            35

    SQL>


    SY.

  • Eight Six
    Eight Six Member Posts: 411 Bronze Badge
    edited Jul 19, 2015 1:29PM

    or may be like this?

    select col1, col4

    from table1

    group by col1, col4

    having count(distinct col2)=1 and count(distinct col3)=1;

  • Bufoss
    Bufoss Member Posts: 123 Red Ribbon
    edited Jul 19, 2015 2:01PM

    @Pavan Kumar

    For the same COL1 and COL2/COL3 , check the select different values from COL4
    For instance for COL1=600 , COL2=140/COL3=2 and COL2=150/COL3=3
    Return 20 and 35 as not common values
    
    

    I mean for source combination 600 , 140, 2  and destination combination 600, 150, 3 check the values of col4.

    And bring me the col1, col2 , col3 from destination  col4 from source that does not exist in destination ,  col5 , col6 from souce , and col7 from a sequence.

    So, I want to insert on existing table tab1 2 rows :

    600 , 150 , 3, 20 , 1400 , 340, 7

    600 , 150 , 3, 35 , 1700 , 120, 8

    not

    600 140 2 20 1400 340 1

    600 140 2 35 1700 120 1

    -----------------------

    @Solomon Yakobson

    I need procedure because I will use it in adf (parameters will be bindings)

    But this you answered to me I can do it only with minus. I have difficulty to extract all the values I need.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,018 Red Diamond
    edited Jul 19, 2015 2:03PM


    And what prevents you from useing bind variables in SQL:


    with t1 as (

                select  *

                  from  table1

                  where col1 = :P_COL1

                    and col2 = :P_FROM_COL2

                    and col3 = :P_FROM_COL3

              ),

        t2 as (

                select  *

                  from  table1

                  where col1 = :P_COL1

                    and col2 = :P_TO_COL2

                    and col3 = :P_TO_COL3

              )

    select  nvl(t1.col4,t2.col4) col4

      from      t1

            full join

                t2

              on t2.col4 = t1.col4

      where t1.col4 is null

        or t2.col4 is null

    /

    SY.

    P.S. Btw, by looking at your code all you need is MERGE statement.

  • Bufoss
    Bufoss Member Posts: 123 Red Ribbon
    edited Jul 19, 2015 3:34PM

    I don't know how to use merge ..

    Is it correct something like the following?

    select :P_COL1

             ,:P_FROM_COL2

             ,:P_FROM_COL3

             ,a.col4

             ,a.col5

             ,a.col6

         from :table_name as a

              left outer join :table_name as b

                           on b.col1=a.col1

                          and b.col2=:P_TO_COL2

                          and b.col3=:P_TO_COL3

                          and b.col4=a.col4

        where a.col1=:P_COL1

          and a.col2=:P_from_COL2

          and a.col3=:P_from_col3

          and b.col1 is null;

    I think Could I use the code in a function like do_copy returning a temp table and in procedure just write

    insert into table1
    select * from do_copy(600,140,2,150,3,'table1');


  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,018 Red Diamond
    edited Jul 19, 2015 5:33PM

    Sorry, even though MERGE can be used it wouldn't be right tool here. All you need is INSERT. The following replaces your procedure:


    SQL> select  *
      2    from  table1
      3  /

          COL1      COL2      COL3      COL4      COL5      COL6      COL7
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
          600        140          2        10      1300        500          1
          600        140          2        20      1400        340          4
          600        140          2        15      1400        230          3
          600        140          2        35      1700        120          2
          600        150          3        10      1300        166          6
          600        150          3        15      1400        435          5

    6 rows selected.

    SQL> insert
      2    into table1
      3    select  :P_COL1,
      4            :P_TO_COL2,
      5            :P_TO_COL3,
      6            col4,
      7            null,
      8            null,
      9            null
    10      from  table1
    11      where col1 = :P_COL1
    12        and col2 = :P_FROM_COL2
    13        and col3 = :P_FROM_COL3
    14        and :P_FLG1 = 'Y'
    15    minus
    16      select :P_COL1,
    17            :P_TO_COL2,
    18            :P_TO_COL3,
    19            col4,
    20            null,
    21            null,
    22            null
    23      from  table1
    24      where col1 = :P_COL1
    25        and col2 = :P_TO_COL2
    26        and col3 = :P_TO_COL3
    27        and :P_FLG1 = 'Y'
    28  /

    2 rows created.

    SQL> select  *
      2    from  table1
      3  /

          COL1      COL2      COL3      COL4      COL5      COL6      COL7
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
          600        140          2        10      1300        500          1
          600        140          2        20      1400        340          4
          600        140          2        15      1400        230          3
          600        140          2        35      1700        120          2
          600        150          3        10      1300        166          6
          600        150          3        15      1400        435          5
          600        150          3        20
          600        150          3        35

    8 rows selected.

    SQL>


    SY.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,018 Red Diamond
    edited Jul 19, 2015 5:33PM Accepted Answer

    And if you want to insert missing COL4 values both ways, use full join, as I already showed (slightly modified):


    SQL> insert
      2    into table1
      3    values(600,150,3,70,1500,567,8)
      4  /

    1 row created.

    SQL> insert
      2    into table1
      3    values(600,150,3,90,1900,789,9)
      4  /

    1 row created.

    SQL> select  *
      2    from  table1
      3  /

          COL1      COL2      COL3      COL4      COL5      COL6      COL7
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
          600        140          2        10      1300        500          1
          600        140          2        20      1400        340          4
          600        140          2        15      1400        230          3
          600        140          2        35      1700        120          2
          600        150          3        10      1300        166          6
          600        150          3        15      1400        435          5
          600        150          3        70      1500        567          8
          600        150          3        90      1900        789          9

    8 rows selected.

    SQL> insert
      2    into table1
      3    with t1 as (
      4                select  *
      5                  from  table1
      6                  where col1 = :P_COL1
      7                    and col2 = :P_FROM_COL2
      8                    and col3 = :P_FROM_COL3
      9                    and :P_FLG1 = 'Y'
    10              ),
    11        t2 as (
    12                select  *
    13                  from  table1
    14                  where col1 = :P_COL1
    15                    and col2 = :P_TO_COL2
    16                  and col3 = :P_TO_COL3
    17                    and :P_FLG1 = 'Y'
    18              )
    19    select  :P_COL1 col1,
    20            nvl2(t1.col4,:P_TO_COL2,:P_FROM_COL2) col2,
    21            nvl2(t1.col4,:P_TO_COL3,:P_FROM_COL3) col3,
    22            nvl(t1.col4,t2.col4) col4,
    23            null,
    24            null,
    25            null
    26      from      t1
    27            full join
    28                t2
    29              on t2.col4 = t1.col4
    30      where t1.col4 is null
    31        or t2.col4 is null
    32  /

    4 rows created.

    SQL> select  *
      2    from  table1
      3  /

          COL1      COL2      COL3      COL4      COL5      COL6      COL7
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
          600        140          2        10      1300        500          1
          600        140          2        20      1400        340          4
          600        140          2        15      1400        230          3
          600        140          2        35      1700        120          2
          600        150          3        10      1300        166          6
          600        150          3        15      1400        435          5
          600        150          3        70      1500        567          8
          600        150          3        90      1900        789          9
          600        140          2        70
          600        140          2        90
          600        150          3        35

          COL1      COL2      COL3      COL4      COL5      COL6      COL7
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
          600        150          3        20

    12 rows selected.

    SQL>

    SY.

    Rajesh123
  • Bufoss
    Bufoss Member Posts: 123 Red Ribbon
    edited Jul 20, 2015 12:51PM

    Thanks a lot for your help! It works great!

This discussion has been closed.