7 Replies Latest reply: Apr 8, 2012 4:56 PM by 849776 RSS

    select statement

    849776
      Hi gurus,

      I am unable to write the a select statement for below req:
      CREATE TABLE ZTEST
      (
        STUDNUM            VARCHAR2(50 BYTE),
        STUDENTID          NUMBER(19)                 NOT NULL,
        PREVIOUSSTUDENTID  NUMBER(19),
        STUDENT_LIST       VARCHAR2(4000 BYTE)
      );
      
      
      Insert into ZTEST
         (STUDNUM, STUDENTID, PREVIOUSSTUDENTID, STUDENT_LIST)
       Values
         ('1', 333, 222, 'X1111 (03/10/10); JOHN (03/10/10); ');
      Insert into ZTEST
         (STUDNUM, STUDENTID, PREVIOUSSTUDENTID, STUDENT_LIST)
       Values
         ('2', 333, 222, 'X1111  (08/11/10); SCOTT (08/11/10); ');
      Insert into ZTEST
         (STUDNUM, STUDENTID, PREVIOUSSTUDENTID, STUDENT_LIST)
       Values
         ('3', 333, 222, 'X1111 (08/20/10); WILSON (08/20/10);  MATT (08/20/10);');
      Insert into ZTEST
         (STUDNUM, STUDENTID, PREVIOUSSTUDENTID, STUDENT_LIST)
       Values
         ('5', 222, 222, 'X1111 (08/20/10); X1111 (08/20/10); ');
      COMMIT;
      
      
      CASE 1 :  IF  (PREVIOUSSTUDENTID =222 OR STUDENTID =222)  AND THE FIRST VAUE OF THE  STUDENT_LIST STRING IS X1111 THEN I NEED TO UPDATE THE  STUDENT_LIST  AS BELOW
      
        JOHN (03/10/10); 
        
        CASE 2 :  IF  (PREVIOUSSTUDENTID =222  AND  STUDENTID =222)  AND THE FIRST VAUE OF THE  STUDENT_LIST STRING IS X1111 THEN I NEED TO UPDATE THE  STUDENT_LIST  AS BELOW
      
        X1111 (08/20/10); 
      
      
      MY OUTPUT SHOULD BE:
      
      STUDENTNUM         STUDENTID      PREVIOUSSTDID                STUDENT_LIST
      
      1                            333                  222                                 JOHN (03/10/10); 
      2                            333                  222                                SCOTT (08/11/10); 
      3                            333                  222                               WILSON (08/20/10);  MATT (08/20/10);
      5                            222                  222                               X1111 (08/20/10); 
      Can any one plz help me

      Thanks in advance

      Edited by: 846773 on Apr 7, 2012 2:25 AM
        • 1. Re: select statement
          rp0428
          >
          I am unable to write the a select statement for below req:
          >
          Can you please explain the rules some more?

          Output #4 - Why did this record change? The first value of student_list is not X111 and it doesn't meet either of your rules. Even if it did where did MARK come from?

          Output #5 - this record meets case 2 but why is the duplicate x111 string being removed?
          • 2. Re: select statement
            849776
            Thanks for immediate response

            sorry

            output 4 is invalid

            i will update my question

            But output 5# if i have more than 2 (X1111) in the string then i have to delete one set


            Thanks
            • 3. Re: select statement
              rp0428
              >
              But output 5# if i have more than 2 (X1111) in the string then i have to delete one set
              >
              But you don't have MORE than two you have exactly two.
              • 4. Re: select statement
                849776
                more than or equal to 2
                • 5. Re: select statement
                  Tubby
                  Lots of guessing going on here, but that seems to be the call of the day.
                  ME_XE?select
                    2    studnum, studentid, previousstudentid, student_list,
                    3    case
                    4      when studentid = 222 and previousstudentid = 222 and substr(student_list, 1, 4) = 'X111'  then substr(student_list, 1, instr(student_list, ';'))
                    5      when (studentid = 222 or previousstudentid = 222) and substr(student_list, 1, 4) = 'X111' then substr(student_list, instr(student_list, ';') + 2 )
                    6    end as insane_data_model_resolved
                    7  from ZTEST;
                  
                  STUDNUM             STUDENTID  PREVIOUSSTUDENTID STUDENT_LIST                                                                                         INSANE_DATA_MODEL_RESOLVED
                  ---------- ------------------ ------------------ ---------------------------------------------------------------------------------------------------- ----------------------------------------
                  1                         333                222 X1111 (03/10/10); JOHN (03/10/10);                                                                   JOHN (03/10/10);
                  2                         333                222 X1111  (08/11/10); SCOTT (08/11/10);                                                                 SCOTT (08/11/10);
                  3                         333                222 X1111 (08/20/10); WILSON (08/20/10);  MATT (08/20/10);                                               WILSON (08/20/10);  MATT (08/20/10);
                  5                         222                222 X1111 (08/20/10); X1111 (08/20/10);                                                                  X1111 (08/20/10);
                  
                  4 rows selected.
                  
                  Elapsed: 00:00:00.01
                  ME_XE?
                  That should at least give you a starting point to work with if the logic isn't 100% spot on ( i don't expect it to be since you haven't really outlined all the potential cases).

                  This is looking a lot like homework to me, but in any event i'll caution you that a data model like this (storing delimited lists in a single column) is neither "relational" nor "rational". It's a recipe for disaster.

                  Cheers,
                  • 6. Re: select statement
                    rp0428
                    >
                    more than or equal to 2
                    >
                    Something like this may be what you want
                    update ztest
                      set student_list = 
                    ltrim(substr('X1111 (03/10/10); JOHN (03/10/10); ', instr('X1111 (03/10/10); JOHN (03/10/10); ', ';') + 1))
                    where (previousstudentid = 222
                       or studentid = 222)
                      and substr(student_list, 1, 5) = 'X1111'
                    You still have a conflict between case1 and case2 since case 1 says prev = 222 or id = 222 which will also be true if both ids = 222 but both = 222 is also case2.

                    So what do you want to do if both case1 and case2 rules are met?
                    • 7. Re: select statement
                      849776
                      Thank you all for your help!!!