Thread: Nested Case

    #1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    354
    Rep Power
    7

    Nested Case


    I'm trying to pull all the degrees into a table based on which institution is selected. If institution is 'AAA' or 'BBB' then pull ACAD_PLAN, DESCR by ACAD_PROG where ACAD_PROG >= some value and <= some other value.

    If institution is 'CCC' then pull ACAD_PLAN, DESCR by institution regardless of ACAD_PROG.

    Something like
    Code:
    INSERT INTO table
    SELECT
    'value_a'
    ,%Bind(INSTITUTION)
    ,value_c'
    ,(SELECT ACAD_PLAN, DESCR FROM PS_ACAD_PLAN_TBL
     CASE WHEN %Bind(INSTITUTION) = 'AAA' OR %Bind(INSTITUTION) = 'BBB'
    				THEN (
    select by acad_prog >= and <=  other values 
    
     )
    WHEN %Bind(INSTITUTION) = 'CCC' THEN (
    
    select from PS_ACAD_PLAN_TBL where institution = 'CCC' 
    
    )END);
    I don't have this formatted right cause it keep telling me missing keywords.
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    225
    Rep Power
    41
    This is a simple sql script that use/modify to accomplish it:
    Code:
    set linesize 150 define off heading off feedback off serveroutput on format word_wrapped
    
    whenever sqlerror exit sql.sqlcode
    
    declare
      cusrsor data_cur is
        select acad_plan, descr
         from  ps_acad_plan_tbl
        where  ( institution in( 'AAA', 'BBB' )
         and     acad_prog between xxx and yyy )
          or   institution not in( 'AAA', 'BBB' );
      v_insert_count             simple_integer    := 0;
      g_error_text               varchar2( 2000 );
      begin
        for data_rec in data_cur
        loop
          insert into table
            ( acad_plan, descr )
          values
            ( data_rec.acad_plan, data_rec.descr );
          v_insert_count  :=  v_insert_count + SQL%ROWCOUNT;
          if mod( v_insert_count, 1000 )  =  0 then
            commit;
          end if;
        end loop;
        commit;
        dbms_output.put_line( '*** Count of rows updated: ' || to_char( v_insert_count, '999,999' ) );
      exception
        when others then
          g_error_text := dbms_utility.format_error_backtrace;
          rollback;
          dbms_output.put_line( to_char( sqlcode ) || ': ' || sqlerrm );
          dbms_output.put_line( g_error_text );
    end;
    /

IMN logo majestic logo threadwatch logo seochat tools logo