Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Red face Unanswered: Please could someone help me with this case statement

    Good day all,

    I am struggling with this code as I don't even know if this is possible in oracle or with the method I have chosen. I am using a case statement but a function is also feasible but have no idea how to create this. I am using oracle 9.2.

    I have a subset of data that ranks each row of data based on Ticket

    FName Ticket State TRank
    Jim C 123 Open 1
    Dino B 123 New 2
    Jim C 123 Closed 3
    Sal M 456 New 1
    Dave G 456 new 2
    Sal M 456 Closed 3

    So what I need to say is for each change of State, look to see if the state = 'Closed' and if it is look at the FName of that State and compare it against FName where TRank = 1(for each new ticket number). So compare the Fname of the closed record to the first record of that Ticket(TRank = 1). If the names are equal then create another column and put a 1 in that column, else put a zero.

    I have tried something like
    Code:
    (Case when state = 'Closed' and Rank() over partition by ticket order by ticket = 1 and Fname = ....I'm lost here.  Any ideas?
    Thx.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As I've understood it, "create another column (and put 1 into it)" means that you want to ALTER TABLE in order to ADD another column. Right? If so, I must admit that I don't like it. Furthermore, it means that you'll have to use EXECUTE IMMEDIATE and automatize column names' creation (because, if they are hard coded, next procedure execution will fail as table (which table? Different from the one you mentioned in your post) already contains such a column).

    Therefore, here is one way to do it - using two nested loops. I wasn't in the mood to create EXECUTE IMMEDIATE string - I'll leave it to you. Instead of it, I altered the table and update newly added column, depending on condition you mentioned.

    First, create a table and insert some data into it. Also, add another column:
    Code:
    CREATE TABLE TEST
    (fname VARCHAR2(20), ticket NUMBER, state VARCHAR2(20), trank NUMBER);
    
    INSERT ALL
      INTO TEST VALUES ('Jim', 123, 'Open', 1)
      INTO TEST VALUES ('Dino', 123, 'New', 2)
      INTO TEST VALUES ('Jim', 123, 'Closed', 3)
      INTO TEST VALUES ('Sal', 456, 'New', 1)
      INTO TEST VALUES ('Dave', 456, 'New', 2)
      INTO TEST VALUES ('Sal', 456, 'Closed', 3)
    SELECT * FROM dual;
    
    ALTER TABLE TEST ADD new_column NUMBER;
    PL/SQL procedure might look like this:
    Code:
    DECLARE
      l_fname TEST.fname%TYPE;
    BEGIN
      FOR cur_t IN (SELECT DISTINCT ticket FROM TEST)
      LOOP
        SELECT fname INTO l_fname
          FROM TEST
          WHERE ticket = cur_t.ticket
            AND trank = 1;  
        
        FOR cur_r IN (SELECT fname, state, trank FROM TEST
                      WHERE ticket = cur_t.ticket
                      ORDER BY ticket)
        LOOP
          IF cur_r.state = 'Closed' AND cur_r.fname = l_fname
          THEN
             -- EXECUTE IMMEDIATE here, instead of UPDATE statement
             UPDATE TEST SET
               new_column = 1
               WHERE ticket = cur_t.ticket
                 AND trank = cur_r.trank;
          ELSE
             -- EXECUTE IMMEDIATE here, instead of UPDATE statement
             UPDATE TEST SET
               new_column = 0
               WHERE ticket = cur_t.ticket
                 AND trank = cur_r.trank;
          END IF;
        END LOOP;
      END LOOP;
    END;
    Finally, the result is as follows:
    Code:
    SQL> select * from test order by ticket, trank;
    
    FNAME                    TICKET STATE                     TRANK NEW_COLUMN
    -------------------- ---------- -------------------- ---------- ----------
    Jim                         123 Open                          1          0
    Dino                        123 New                           2          0
    Jim                         123 Closed                        3          1
    Sal                         456 New                           1          0
    Dave                        456 New                           2          0
    Sal                         456 Closed                        3          1
    
    6 rows selected.
    
    SQL>
    If I misunderstood the question, I'm sorry ... Also, I spent the best years of my life solving a non-existent problem.

  3. #3
    Join Date
    Apr 2006
    Posts
    140
    Liitlefoot,

    Thanks for this lengthy reply. Actually I may have lead you in the wrong direction Create another column actually means querying a table and adding that column with the use of a select statement. So I was trying to attain what I needed in a case statement that is incorporated in my select statement. Sorry again for not being clear.

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Something like this should get you going.
    Code:
    SQL> select t.*,
      2         case when state = 'Closed'
      3               and fname = min( fname ) keep ( dense_rank first order by trank )
      4                                        over ( partition by ticket )
      5              then 1
      6              else 0
      7           end cs
      8    from t
      9   order by ticket, trank
     10  /
    
    FNAME          TICKET STATE           TRANK         CS
    ---------- ---------- ---------- ---------- ----------
    Jim C             123 Open                1          0
    Dino B            123 New                 2          0
    Jim C             123 Closed              3          1
    Sal M             456 New                 1          0
    Dave G            456 New                 2          0
    Sal M             456 Closed              3          1
    
    6 rows selected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •