Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2013-01-10
2
Medium Priority
?
382 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 74

Accepted Solution

by:
sdstuber earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

577 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