This discussion is archived
14 Replies Latest reply: Mar 18, 2013 10:01 AM by APC RSS

Manipulations

Oracle Maniac Explorer
Currently Being Moderated
HI ,

CREATE TABLE "TEST_JET" ("K1" NUMBER, "K2" NUMBER, "K3" NUMBER, "K4" VARCHAR2(1)) ;
REM INSERTING into TEST_JET
Insert into TEST_JET (K1,K2,K3,K4) values (1,2,3,'I');
Insert into TEST_JET (K1,K2,K3,K4) values (1,2,3,'U');
Insert into TEST_JET (K1,K2,K3,K4) values (1,2,3,'D');
Insert into TEST_JET (K1,K2,K3,K4) values (1,2,2,'U');
Insert into TEST_JET (K1,K2,K3,K4) values (1,2,2,'D');
Insert into TEST_JET (K1,K2,K3,K4) values (1,3,5,'I');
Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'U');
Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'D');
Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'T');
Insert into TEST_JET (K1,K2,K3,K4) values (1,8,9,'U');
Insert into TEST_JET (K1,K2,K3,K4) values (1,8,9,'U');
Insert into TEST_JET (K1,K2,K3,K4) values (1,8,9,'D');
Insert into TEST_JET (K1,K2,K3,K4) values (7,7,7,'T');
Insert into TEST_JET (K1,K2,K3,K4) values (7,7,7,'I');



SELECT K1,K2,K3,CASE  WHEN STR LIKE '%D%' THEN 'D' WHEN STR LIKE '%T%' THEN 'T' WHEN STR LIKE '%I%' THEN 'I'  WHEN STR LIKE '%X%' THEN 'X' END  OP FROM
(
SELECT K1,K2,K3,COUNT(1),LISTAGG(K4, ',') WITHIN GROUP (ORDER BY K4) STR
FROM 
TEST_JET
GROUP BY K1,K2,K3
HAVING COUNT(1)>1
)
based on the above result set , for a particular group ,only that op will be retained which comes out in the query . say for example , we have got 1,2,3,'D' for group 1,2,3

now since we have got the D Operation from the above query , i don't need the other two rows .i.e.
(1,2,3,'I');
(1,2,3,'U');



what is the best way to delete the data we dont want retaining the rows we want ,using a single sql statement . Also , for the result set row 7,7,7,T I first need to delete the group containing T operation, and insert two new rows .i.e.
7,7,7,D and 7,7,7,I .

Edited by: Rahul K on Mar 18, 2013 5:15 AM
  • 1. Re: Manipulations
    NSK2KSN Journeyer
    Currently Being Moderated
    please also post your expectedoutput for quick understanding
  • 2. Re: Manipulations
    jeneesh Guru
    Currently Being Moderated
    And the {noformat}
    {noformat} tags should be in lower case..                                                                                                                                                                                                
  • 3. Re: Manipulations
    APC Oracle ACE
    Currently Being Moderated
    Rahul  K wrote:
    for the result set row 7,7,7,T I first need to delete the group containing T operation, and insert two new rows .i.e.
    7,7,7,D and 7,7,7,I .
    Why are you creating two rows for this, when your previous requirement is zap eveything except the 'D' record?
    now since we have got the D Operation from the above query , i don't need the other two rows .i.e.
    (1,2,3,'I');
    (1,2,3,'U');
    Cheers, APC
  • 4. Re: Manipulations
    Oracle Maniac Explorer
    Currently Being Moderated
    expected output


    1,2,2,D
    1,2,3,D
    1,6,7,D
    1,8,9,D
    7,7,7,D
    7,7,7,I

    Edited by: Rahul K on Mar 18, 2013 5:16 AM
  • 5. Re: Manipulations
    Oracle Maniac Explorer
    Currently Being Moderated
    As per the business logic ,

    The moment I come across any row with operation code T , i need to replace that with D and I statements


    for eg 7,7,7,T with 7,7,7,I & 7,7,7,D

    Edited by: Rahul K on Mar 18, 2013 5:32 AM
  • 6. Re: Manipulations
    APC Oracle ACE
    Currently Being Moderated
    Rahul  K wrote:
    As per the business logic ,
    But the buisness logic is illogical.

    Anyway. How often will yiou run this operation? What are the windows (that is, is performance an issue)?

    Cheers, APC
  • 7. Re: Manipulations
    Oracle Maniac Explorer
    Currently Being Moderated
    Not of a real-time requirement .It can be considered as a batch job for 1 million rows. Also , is the usage of like in my CASE statement an overhead ?

    Thanks
  • 8. Re: Manipulations
    APC Oracle ACE
    Currently Being Moderated
    Having re-read the original post I now see that Rule 1 (D) always takes precedence over Rule 2 T. It would have been helpful if you made better use of the carriage return key so that all of your code could be read without recourse to the scrollbar: making your post readable increases the likelihood of getting a quick solution.

    I don't think the CASE() solution is a performance overhead. Your main problem is going to be the RBAR processing which your query implies:
    begin
    
        for lrec in ( SELECT K1,K2,K3,CASE  
                               WHEN STR LIKE '%D%' THEN 'D' 
                               WHEN STR LIKE '%T%' THEN 'T' 
                               WHEN STR LIKE '%I%' THEN 'I'  
                               WHEN STR LIKE '%X%' THEN 'X'
                            END  OP 
                        FROM
                                  (
                                  SELECT K1,K2,K3,COUNT(1),LISTAGG(K4, ',') WITHIN GROUP (ORDER BY K4) STR
                                  FROM 
                                  TEST_JET
                                  GROUP BY K1,K2,K3
                                  HAVING COUNT(1)>1
                                  )
    
                       )
         loop
              case lrec.op
              when 'D' then 
                    delete from TEST_JET
                    where k1 = lrec.k1
                     and k2 = lrec.k2
                     and k3 = lrec.k3
                     and k4 != 'D';
              when 'T' then 
                    delete from TEST_JET
                    where k1 = lrec.k1
                     and k2 = lrec.k2
                     and k3 = lrec.k3;
                   insert into TEST_JET values (lrec.k1, lrec.k2, lrec.k3, 'I' );
                  insert into TEST_JET values (lrec.k1, lrec.k2, lrec.k3, 'D' );
            else
                null;
           end case;
         end loop;
    end;
    Hmm, that's some really hateful syntax there, and I have second thoughts about posting it. If this was a one-off overnight job on a system with lot sof spare crunch I could run with a clear conscience. Otherwise I would want to tune it some more. But without knowing more about you data I'm not going to guess.

    Cheers, APC

    Edited by: APC on Mar 18, 2013 1:13 PM
  • 9. Re: Manipulations
    Oracle Maniac Explorer
    Currently Being Moderated
    Thanks for the reply . Seems I was not able to explain the issue correctly .


    Suppose if my table has just the below mentioned rows.
    Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'U');
    Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'D');
    Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'T');
    the moment i execute the group by query , i will get the below row for the group 1,6,7 .i.e. 1,6,7,'U,D,'T' AND as per my CASE precedence , the first check is for D and so i will get 1,6,7,D. Once I get this row for the group 1,6,7 : I will want to delete all the other rows of the group 1,6,7 and just retain this row .i.e. 1,6,7,D .My table at the end should look like
    k1,k2,k3,k4
    1,6,7,D
    now suppose if my table contains the below rows
    Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'U');
    Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'T');
    the moment i execute the group by query , i will get the below row for the group 1,6,7 .i.e. 1,6,7,'U,'T' AND as per my CASE precedence , the first check will be for T and so i will get 1,6,7,T.Once I get this row for the group 1,6,7 : I will want to delete all the other rows of the group 1,6,7 and just retain this row .i.e. 1,6,7,T .My table at the end should look like
    k1,k2,k3,k4
    1,6,7,D
    1,6,7,I
    I agree that the business logic is somewhat illogical but this is the reality . :)


    Thanks a lot
  • 10. Re: Manipulations
    APC Oracle ACE
    Currently Being Moderated
    Hmm, obviously I was editing my answer as you were writing that.

    Cheers, APC
  • 11. Re: Manipulations
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Here's one way:
    DELETE     test_jet
    WHERE     (k1, k2, k3, k4) NOT IN
         (
             SELECT    k1, k2, k3
             ,           MIN (k4) KEEP ( DENSE_RANK FIRST
                                           ORDER BY  INSTR ( 'XITD'
                                                  , k4
                                               )
                                     DESC
                            )
             FROM      test_jet
             GROUP BY  k1, k2, k3
         )
    ;
    I don't understand the following:
    Rahul  K wrote:
    ... now suppose if my table contains the below rows
    Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'U');
    Insert into TEST_JET (K1,K2,K3,K4) values (1,6,7,'T');
    the moment i execute the group by query , i will get the below row for the group 1,6,7 .i.e. 1,6,7,'U,'T' AND as per my CASE precedence , the first check will be for T and so i will get 1,6,7,T.Once I get this row for the group 1,6,7 : I will want to delete all the other rows of the group 1,6,7 and just retain this row .i.e. 1,6,7,T .My table at the end should look like
    k1,k2,k3,k4
    1,6,7,D
    1,6,7,I
    If the table doesn't contain any rows with k4='I', why should it have a row with k4='I' after deleting?

    Edited by: Frank Kulash on Mar 18, 2013 9:50 AM
  • 12. Re: Manipulations
    APC Oracle ACE
    Currently Being Moderated
    Frank Kulash wrote:
    If the table doesn't contain any rows with k4='I', why should it have a row with k4='I' after deleting?
    That was basically my point about the illogicality of the business logic. It's a (sort of) de-duuplicating exercise which also spawns duplicate records. But the answer appears to be, "just because".

    Anyway, I like your solution more than mine. I'm intrigued to see how you're going to handle the 'T' records....

    Cheers, APC
  • 13. Re: Manipulations
    Oracle Maniac Explorer
    Currently Being Moderated
    I totally agree to your point . I am dealing with oracle total recall where my logic states that if i have T then replace that T with I and D .How to tackle this using sql .



    Thanks for you reply.
  • 14. Re: Manipulations
    APC Oracle ACE
    Currently Being Moderated
    When you say you're dealing with "oracle total recall" do you mean Oracle Total Recall AKA Flashback Database? If so perhaps you could explain a bit more about what you're trying to do? I find it even more difficult to understand the logic with that additional piece of information ....

    Cheers, APC

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points