14 Replies Latest reply: Mar 18, 2013 12:01 PM by APC RSS

    Manipulations

    Oracle Maniac
      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
          please also post your expectedoutput for quick understanding
          • 2. Re: Manipulations
            jeneesh
            And the {noformat}
            {noformat} tags should be in lower case..                                                                                                                                                                                                
            • 3. Re: Manipulations
              APC
              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
                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
                  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
                    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
                      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
                        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
                          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
                            Hmm, obviously I was editing my answer as you were writing that.

                            Cheers, APC
                            • 11. Re: Manipulations
                              Frank Kulash
                              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
                                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
                                  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
                                    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