5 Replies Latest reply on Oct 5, 2012 7:30 PM by 812975

    Find number table B > = number in table A but < then next entry in B

    Withnoe
      I am trying to figure out do the following: Find number table B > = number in table A but < then next entry in B

      Table A

      5
      10
      21
      20

      Table B

      8
      12
      16
      23
      40

      For entry 5 in table A I would like to return 8 from B
      For entry 10 I would lke to return 12 from B
      For entry 21 I would like to return 23 from B
      For entry 20 I would like to return 23 from B

      Edited by: Withnoe on Oct 5, 2012 9:19 AM

      Edited by: Withnoe on Oct 5, 2012 10:54 AM
        • 2. Re: Find number table B > = number in table A but < then next entry in B
          812975
          can you please give us table scripts and sample data? is there any unique constraint? any sorting available? etc?
          • 3. Re: Find number table B > = number in table A but < then next entry in B
            Withnoe
            Table A -- All of the collumns are part of the key

            CLASS # TERM

            BIOL 520 201010
            BIOL 521 201250
            BIOL 522 200980


            Table B All of the collumns are part of the key


            BIOL 520 201110
            BIOL 520 201150
            BIOL 520 201250


            Table A BIOL 520 201010 should match up with BIOL 520 201110 in table B because BIOL 520 201110 row 1 in table B is > than 201010 from table A row 1 but I don't want it to return the second or third row in Collumn B which is what I would get if I used select max term from B where the term from A >= term in B

            We are on Oracle 11G


            Does this help SaadL?
            • 4. Re: Find number table B > = number in table A but < then next entry in B
              Ashu_Neo
              Hi,
              Please check below.
              create table  a (cola number);
              create table  b (colb number);
              insert into a values(5);
              insert into a values(10);
              insert into a values(21);
              insert into a values(20);
              insert into b values(8);
              insert into b values(12);
              insert into b values(16);
              insert into b values(23);
              insert into b values(40);
              
              create or replace function fab ( p1 number)
              return number
              is
              ret number;
              begin
                   for i in (select colb from b where colb >= p1 and rownum <3 order by colb)
                    loop
                         ret := i.colb;
                          exit;
                    end loop;
                    return ret;
              end;
              /
              
              SQL> select fab(cola) from a;
              
               FAB(COLA)
              ----------
                       8
                      12
                      23
                      23
              NB:- Put requirement in body of your posting in format as mentioned in the link "how to ask a question" of Sb's reply, instead of putting it on thread with hints only.
              It's really helpful. Even I joined recently on otn and read it on the first day and it's helping me a lot till date.. :)

              Thanks!

              Edited by: Ashu_Neo on Oct 5, 2012 10:55 PM
              • 5. Re: Find number table B > = number in table A but < then next entry in B
                812975
                CREATE TABLE TAB_A (CLASS# VARCHAR2(10), TERM NUMBER);
                CREATE TABLE TAB_B (CLASS# VARCHAR2(10), TERM NUMBER);

                INSERT INTO TAB_A VALUES ('BIOL 520', 201010);
                INSERT INTO TAB_A VALUES ('BIOL 521', 201250);
                INSERT INTO TAB_A VALUES ('BIOL 522', 200980);

                INSERT INTO TAB_B VALUES ('BIOL 520', 201110);
                INSERT INTO TAB_B VALUES ('BIOL 520', 201150);
                INSERT INTO TAB_B VALUES ('BIOL 520', 201250);
                INSERT INTO TAB_B VALUES ('BIOL 521', 201250);
                INSERT INTO TAB_B VALUES ('BIOL 521', 201260);

                SELECT MIN(TAB_B.TERM) TERM, TAB_B.CLASS# FROM TAB_A, TAB_B
                WHERE TAB_A.CLASS# = TAB_B.CLASS#
                AND TAB_B.TERM > TAB_A.TERM
                GROUP BY TAB_B.CLASS#;

                Please let us know if you require something else. Thanks