Query Not working

I have a function in which there is a SQL statement -

select sum(shrtgpa_hours_earned)/sum(shrtgpa_hours_attempted) into stu_comp_rate
from shrtgpa
where shrtgpa_pidm = ipidm and
           shrtgpa_term_code in ''' || ass_terms || ''';

The ass_terms ia a varchar2 and has a value of (201010,201020)

and ipidm is a number.

When I run the same query in command prompt I get a value for stu_compl_rate. However, in the function, the stu_compl_rate is NULL.

Thanks
happylife1234Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
at the command prompt you're either typing the value in "as is" , or you're using a substitution variable which isn't really a variable at all.

in the function you have a real variable,  and IN clauses don't work like that
0
sdstuberCommented:
you can change your query to accept the string and turn it into a collection that you can do the IN clause on
or
put the data in a temp table do the IN clause
or
(not recommended) use dynamic sql to embed the string as a literal
0
sdstuberCommented:
to turn a string like...   '201010,201020'  into a collection of 2 values use something like the attached type and function
then modify your sql like this...


select sum(shrtgpa_hours_earned)/sum(shrtgpa_hours_attempted) into stu_comp_rate
from shrtgpa
where shrtgpa_pidm = ipidm and
           shrtgpa_term_code in  (select * from table(str2tbl(ass_terms)))
CREATE OR REPLACE TYPE  VCARRAY AS TABLE OF VARCHAR2(4000);

CREATE OR REPLACE FUNCTION str2tbl(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
        RETURN vcarray PIPELINED
    AS
        v_length   NUMBER := LENGTH(p_string);
        v_start    NUMBER := 1;
        v_index    NUMBER;
    BEGIN
        WHILE(v_start <= v_length)
        LOOP
            v_index    := INSTR(p_string, p_delimiter, v_start);

            IF v_index = 0
            THEN
                PIPE ROW(SUBSTR(p_string, v_start));
                v_start    := v_length + 1;
            ELSE
                PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
                v_start    := v_index + 1;
            END IF;
        END LOOP;

        RETURN;
    END str2tbl;

Open in new window

0
sdstuberCommented:
alternately, if on 10g or higher

change the 100 to some number large enough to extract all the elements in your string
in your example , 2 would be sufficient

SELECT SUM(shrtgpa_hours_earned) / SUM(shrtgpa_hours_attempted)
  INTO stu_comp_rate
  FROM shrtgpa
 WHERE shrtgpa_pidm = ipidm
       AND shrtgpa_term_code IN
               (SELECT     REGEXP_SUBSTR(ass_terms, '[^,]+', 1, LEVEL)
                      FROM DUAL
                CONNECT BY LEVEL <= 100
                           AND REGEXP_SUBSTR(ass_terms, '[^,]+', 1, LEVEL) IS NOT NULL)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.