Oracle function: Has 2 similar SELECTS - want to modify to use dynamic sql

Hi EE,

in this post:

http://www.experts-exchange.com/Database/Oracle/Q_27991191.html#a38763997

SDStuber helped me to create IN dynamically via values supplied as a string in a parm.

Pls note that i have an IF statement with very similar SELECT statements
      in first SELECT,  = academic period admitted

      in second SELECT <> academic period admitted

is there a way to create SELECT DYNAMICALLY in (3) below

tx for your help, sandra

1)
create or replace type DMC_Type_ParseTable as table
  of varchar2 (255);

2)
create or replace function ODSMGR.DMC_str2tbl( p_string in varchar2 , p_delim in varchar2) return DMC_Type_ParseTable
    as
       l_string   long default p_string || p_delim;
       l_data    DMC_Type_ParseTable := DMC_Type_ParseTable();
       n     number;
    begin
      loop
         exit when l_string is null;
        n := instr( l_string, p_delim );
        l_data.extend;
        l_data(l_data.count) :=
        ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
        l_string := substr( l_string, n+length (p_delim) );
     end loop;
     return l_data;
   end;

3) pls overlay function with this:

create or replace function DMC_COUNT_BY_ETHNIC_GENDER_YR
         (PARM_College      varchar2
         ,PARM_StudentPop   varchar2
         ,PARM_Gender       varchar2
         ,PARM_Ethnicity    varchar2
         ,PARM_Term         varchar2
         ,PARM_Attrib       varchar2
         ,PARM_YesNo        varchar2)


   
              return number is lngCount number;

 
begin
  lngCount := 0;

 
  If PARM_StudentPop = 'N' then
     select count(A.College)
            into lngCount
            from Academic_Study A
                   INNER JOIN PERSON P
                       ON A.person_uid = p.person_uid
         where A.College = PARM_College
                        and
               A.academic_period_admitted = PARM_Term
                        And
               P.gender = PARM_gender
                        And
               P.primary_ethnicity = PARM_ethnicity
                        And
               A.academic_period = PARM_Term
                        AND
               A.STUDENT_status in('AS', 'LA')
                        AND
               A.STUDENT_CLASSIFICATION in (select * from TABLE (DMC_str2tbl(PARM_attrib, ':' ) ) )
                        and
               DMC_IF_STUDENT_REG_COLLEG_TERM(A.PERSON_UID, A.ACADEMIC_PERIOD, A.COLLEGE) = PARM_YesNo;
  Else

           select count(A.College)
            into lngCount
            from Academic_Study A
                   INNER JOIN PERSON P
                       ON A.person_uid = p.person_uid
         where A.College = PARM_College
                        and
               A.academic_period_admitted <> PARM_Term
                        And
               P.gender = PARM_gender
                        And
               P.primary_ethnicity = PARM_ethnicity
                        And
               A.academic_period = PARM_Term
                        AND
               A.STUDENT_status in('AS', 'LA')
                        AND
               A.STUDENT_CLASSIFICATION in (select * from TABLE (DMC_str2tbl(PARM_attrib, ':' ) ) )
                        and
               DMC_IF_STUDENT_REG_COLLEG_TERM(A.PERSON_UID, A.ACADEMIC_PERIOD, A.COLLEGE) = PARM_YesNo;
  End if;
     
  return lngCount;

end DMC_COUNT_BY_ETHNIC_GENDER_YR;
mytfeinAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
for execution efficiency I would leave it the way you have it.

But, if you really want to combine them, try this...



  select count(A.College)
            into lngCount
            from Academic_Study A
                   INNER JOIN PERSON P
                       ON A.person_uid = p.person_uid
         where A.College = PARM_College
                        and
  ((PARM_StudentPop = 'N'   and  A.academic_period_admitted = PARM_Term)
   or
     (PARM_StudentPop != 'N' and A.academic_period_admitted <> PARM_Term)
)
                        And
               P.gender = PARM_gender
                        And
               P.primary_ethnicity = PARM_ethnicity
                        And
               A.academic_period = PARM_Term
                        AND
               A.STUDENT_status in('AS', 'LA')
                        AND
               A.STUDENT_CLASSIFICATION in (select * from TABLE (DMC_str2tbl(PARM_attrib, ':' ) ) )
                        and
               DMC_IF_STUDENT_REG_COLLEG_TERM(A.PERSON_UID, A.ACADEMIC_PERIOD, A.COLLEGE) = PARM_YesNo;
  End if;
0
 
mytfeinAuthor Commented:
Hi SDStuber,

that's brilliant....

tx so much,  sandra
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.