Solved

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

Posted on 2013-01-10
2
374 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
2 Comments
 
LVL 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now