Solved

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

Posted on 2013-01-10
2
379 Views
Last Modified: 2013-01-10
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;
0
Comment
Question by:mytfein
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 38764300
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
 

Author Comment

by:mytfein
ID: 38764334
Hi SDStuber,

that's brilliant....

tx so much,  sandra
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question