Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: December 13, 2002 - 9:25 am UTC

Last updated: December 23, 2002 - 11:14 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,

I have a pretty length select statement...Something like this:

(select ....from <table1> where ....
union all
select ....from <table2> where ....
union all
select(select ....from <table1> where ....)
/
(select ....from <table2> where ....)
*100
from dual


In other words my third union need to get the percentage of
first one over the second. Is there any way to simplify that?

Thank You


HERE IS MY EXAMPLE:

PACKAGE BODY PKG_RPT_ACC_RESET_SUMMARY
IS
PROCEDURE USP_RPT_ACC_RESET_SUMMARY
( p_startdate IN VARCHAR2,
p_enddate IN VARCHAR2,
p_chain IN VARCHAR2,
p_regionname IN VARCHAR2,
p_questionnaire IN VARCHAR2,
p_resultset OUT rc_resultset)

IS
v_output rc_resultset;
BEGIN
OPEN v_output FOR
'

select concat(RPAD(''Calls Scheduled'',20),count(call.callid)) "NUMBER"
from call,questionnaire,outlet,mapregionoutlet,region,chain
where call.outletid=outlet.outletid
and outlet.outletid=mapregionoutlet.outletid
and mapregionoutlet.regionid=region.regionid
and call.questionnaireid=questionnaire.questionnaireid
and chain.chainid=outlet.chainid
and chain.name=:p_chain
and region.name like ''%'' || :p_regionname || ''%''
and call.duedate between to_date(:p_startdate,''mm/dd/yyyy'') and to_date(:p_enddate,''mm/dd/yyyy'')
and questionnaire.name = :p_questionnaire

UNION ALL
select concat(RPAD(''Calls Actual'',20),count(call.callid))
from call,questionnaire,outlet,mapregionoutlet,region,chain
where call.outletid=outlet.outletid
and outlet.outletid=mapregionoutlet.outletid
and mapregionoutlet.regionid=region.regionid
and call.questionnaireid=questionnaire.questionnaireid
and chain.chainid=outlet.chainid
and chain.name=:p_chain
and region.name like ''%'' || :p_regionname || ''%''
and call.datecompleted between to_date(:p_startdate,''mm/dd/yyyy'') and to_date(:p_enddate,''mm/dd/yyyy'')
and questionnaire.name = :p_questionnaire

UNION ALL

SELECT
concat(RPAD(''% Actual to Plan'',20),ROUND((select count(call.callid)
from call,questionnaire,outlet,mapregionoutlet,region,chain
where call.outletid=outlet.outletid
and outlet.outletid=mapregionoutlet.outletid
and mapregionoutlet.regionid=region.regionid
and call.questionnaireid=questionnaire.questionnaireid
and chain.chainid=outlet.chainid
and chain.name=:p_chain
and region.name like ''%'' || :p_regionname || ''%''
and call.datecompleted between to_date(:p_startdate,''mm/dd/yyyy'') and to_date(:p_enddate,''mm/dd/yyyy'')
and questionnaire.name = :p_questionnaire)/
(select count(call.callid)
from call,questionnaire,outlet,mapregionoutlet,region,chain
where call.outletid=outlet.outletid
and outlet.outletid=mapregionoutlet.outletid
and mapregionoutlet.regionid=region.regionid
and call.questionnaireid=questionnaire.questionnaireid
and chain.chainid=outlet.chainid
and chain.name=:p_chain
and region.name like ''%'' || :p_regionname || ''%''
and call.duedate between to_date(:p_startdate,''mm/dd/yyyy'') and to_date(:p_enddate,''mm/dd/yyyy'')
and questionnaire.name = :p_questionnaire)*100))
FROM DUAL

'using
p_chain,p_regionname,p_startdate,p_enddate,p_questionnaire,
p_chain,p_regionname,p_startdate,p_enddate,p_questionnaire,
p_chain,p_regionname,p_startdate,p_enddate,p_questionnaire,
p_chain,p_regionname,p_startdate,p_enddate,p_questionnaire;
p_resultset := v_output;

END;
END;



and Tom said...

I believe we can do this in a single pass. Basically, you want to return 3 rows:

row 1: calls scheduled
row 2: calls actual
row 3: calls actual/calls_scheduled

What we'll do is ONE query to get us a single row with two columns, calls scheduled and calls actual.

Then, we can divide to get the third value easily.

Then, we cartesian product this one row with 3 rows from dual and we get our 3 rows of output -- and we only made one pass on the query itself... Try (i did not actually RUN this as I don't have your tables but I believe it to be pretty close) this query. I sum up the CASE of the date -- to count the number of records for each -- then using inline views just keep processing the results to get what I want:

open v_output for
'
select decode( rn, 1, c1, 2, c2, c3 ) "NUMBER"
from (
select rpad( ''Calls Scheduled'', 20 ) || calls_scheduled c1,
rpad( ''Calls Actual'', 20 ) || calls_actual c2,
rpad( ''% Actual to Plan'',20 ) ||
decode( calls_scheduled, 0, ''****'',
round( calls_actual/calls_scheduled*100, 2 ) ) c3
from (
select sum( case when (call.duedate between :p_startdate and :p_enddate)
then 1
else 0
end ) calls_scheduled,
sum( case when (call.datecompleted between :p_startdate and :p_enddate)
then 1
else 0
end ) calls_completed
from call,
questionnaire,
outlet,
mapregionoutlet,
region,
chain
where call.outletid=outlet.outletid
and outlet.outletid=mapregionoutlet.outletid
and mapregionoutlet.regionid=region.regionid
and call.questionnaireid=questionnaire.questionnaireid
and chain.chainid=outlet.chainid
and chain.name=:p_chain
and region.name like :p_regionname
and questionnaire.name = :p_questionnaire
and (call.duedate between :p_startdate and :p_enddate
or
call.datecompleted between :p_startdate and :p_enddate)
),
( select 1 rn from dual
union all
select 2 rn from dual
union all
select 3 rn from dual )
)
' using to_date( p_start_date, 'mm/dd/yyyy' ),
to_date( p_end_date, 'mm/dd/yyyy' ),
to_date( p_start_date, 'mm/dd/yyyy' ),
to_date( p_end_date, 'mm/dd/yyyy' ),
p_chain,
'%' || p_regionName || '%',
p_questionnaire,
to_date( p_start_date, 'mm/dd/yyyy' ),
to_date( p_end_date, 'mm/dd/yyyy' ),
to_date( p_start_date, 'mm/dd/yyyy' ),
to_date( p_end_date, 'mm/dd/yyyy' );





Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, December 16, 2002 - 9:03 am UTC

THANK YOU VERY MUCH TOM

A reader, December 18, 2002 - 8:30 am UTC

Tom,
In the query you showed me you selected call act. and call scheduled from only one region (where region.name like ''%'' || :p_regionname || ''%'')...What if I want to select call act. and call sched. where region.level1=9 along with call act. and call sched where region.level1=10
along with call act. and call sched where region.level1=125.
How should I proceed? Separate query with union since to all of them we have to apply different where clause in regard to region level or else?

Thank You very much

Tom Kyte
December 18, 2002 - 12:41 pm UTC

Is it that you want 9 rows back (3 for each "region") or 3 rows back across all regions.

A reader, December 20, 2002 - 9:07 am UTC

Tom,

Thanks very much for you quick response. I did how you suggested and everythin is working perfect...
Following is another question pertaining to the same quiry:

select sum(case
when (question.questiontext='Enter Display Type' and answer.answerinteger=1
)
then 1
else 0
end) "System XL",
sum(case
when (question.questiontext='Enter Display Type' and answer.answerinteger=2
)
then 1
else 0
end) "Vista",
sum(case
when (question.questiontext='Enter Display Type' and answer.answerinteger=3
)
then 1
else 0
end) "Other",
sum(case
when (question.questiontext='Enter Total Time Spent in the Store, in Hours'
)
then answer.answerinteger
else 0
end) "(Included) SPP Hrs.",
avg(case
when (question.questiontext='Enter Display Width in Feet'
)
then answer.answerinteger
else 0
end) "(Included) SPP Hrs."
FROM outlet,
call,
question,
answer,
region,
mapregionoutlet,
chain,
project
WHERE outlet.outletid=call.outletid
and call.callid=answer.callid
and answer.questionid=question.questionid
and outlet.outletid=mapregionoutlet.outletid
and mapregionoutlet.regionid=region.regionid
and outlet.chainid=chain.chainid
and call.projectid=project.projectid
and call.duedate between to_date('1/1/2001','mm/dd/yyyy') and to_date('12/31/2001','mm/dd/yyyy')
and chain.chainid=132
and project.name in ('Secondary Maybelline Reset','Maybelline Reset')
and region.level1=9


For the first 3 cases I applied condition of project.name in ('Secondary Maybelline Reset','Maybelline Reset')
However, for the last case I don't want to include anything where project.name in 'Maybelline Reset', only where project name in 'Secondary Maybelline Reset'.

I've tried to put that filter inside of each case, however it runs forever, so I had to cancel the operation.

Thanks again

Tom Kyte
December 20, 2002 - 9:21 am UTC

avg(case
when (question.questiontext='Enter Display Width in Feet' and
project.name = 'Secondary Maybelline Reset'
)
then answer.answerinteger
else NULL
end) "(Included) SPP Hrs."

add the predicate in the case, return NULL, not 0, and it won't be averaged in.

A reader, December 20, 2002 - 11:01 am UTC

Thanks a lot, everything works.
How can I make following select statement work for many different regions, which is the first inner select statement I need to repeat 5 times just to change region.level1 to region.level2=10,then region.level2=120 and so on. If I just concatenate them all the select statement will be humangous. Would it be better and possible for me to write function to return let's say 10 columns based on the region.level

Tom Kyte
December 20, 2002 - 11:14 am UTC

why not

and region.level1 in ( .... )
group by region.level1

and add region.level1 to the select list. get a row/region?





A reader, December 20, 2002 - 11:02 am UTC

I forgot to include select statement:

select decode(rn,1,c1,2,c2,3,c3,4,c4,5,c5,6,c6,7,c7,c8) "NATIONAL"
from

(select
sum(case
when (question.questiontext='Enter Display Width in Feet' and project.name = 'Maybelline Reset' and region.level1=9)
then answer.answerinteger
else NULL
end) c1,
sum(case
when (question.questiontext='Enter Display Type' and answer.answerinteger=1 and project.name = 'Maybelline Reset' and region.level1=9)
then 1
else 0
end) c2,
sum(case
when (question.questiontext='Enter Display Type' and answer.answerinteger=2 and project.name = 'Maybelline Reset' and region.level1=9)
then 1
else 0
end) c3,
sum(case
when (question.questiontext='Enter Display Type' and answer.answerinteger=3 and project.name = 'Maybelline Reset' and region.level1=9)
then 1
else 0
end) c4,
sum(case
when (question.questiontext='Enter Total Time Spent in the Store, in Hours' and project.name = 'Maybelline Reset' and region.level1=9)
then answer.answerinteger
else NULL
end) c5,
sum(case
when (question.questiontext='Enter Total Time Spent in the Store, in Hours' and project.name = 'Maybelline Reset' and region.level1=9)
then answer.answerinteger
else NULL
end)+
sum(case
when (question.questiontext='Enter Total Time Spent in the Store, in Hours' and project.name = 'Secondary Maybelline Reset' and region.level1=9)
then answer.answerinteger
else NULL
end) c6,
to_char(to_number(avg(case
when (question.questiontext='Enter Total Time Spent in the Store, in Hours'and project.name in ('Maybelline Reset') and region.level1=9)
then answer.answerinteger
else 0
end)),'9.99')c7,
to_char(to_number(avg(case
when (question.questiontext='Enter Total Time Spent in the Store, in Hours'and project.name in ('Maybelline Reset','Secondary Maybelline Reset') and region.level1=9)
then answer.answerinteger
else 0
end)),'9.99') c8
FROM outlet,
call,
question,
answer,
region,
mapregionoutlet,
chain,
project
WHERE outlet.outletid=call.outletid
and call.callid=answer.callid
and answer.questionid=question.questionid
and outlet.outletid=mapregionoutlet.outletid
and mapregionoutlet.regionid=region.regionid
and outlet.chainid=chain.chainid
and call.projectid=project.projectid
and call.duedate between to_date('1/1/2001','mm/dd/yyyy') and to_date('12/31/2001','mm/dd/yyyy')
and chain.chainid=132
and project.name in ('Secondary Maybelline Reset','Maybelline Reset')),
( select 1 rn from dual
union all
select 2 rn from dual
union all
select 3 rn from dual
union all
select 4 rn from dual
union all
select 5 rn from dual
union all
select 6 rn from dual
union all
select 7 rn from dual
union all
select 8 rn from dual
)
--and region.level1=9




A reader, December 20, 2002 - 11:44 am UTC

I am sorry Tom but I did not get it...
How the function would look like with following select statement and return something like that:

Heading -->c1 c2 c3 c4
Value -->12 23 32 423

select
sum(case
when (question.questiontext='Enter Display Width in Feet' and project.name = 'Maybelline Reset' and region.level1=9)
then answer.answerinteger
else NULL
end) c1,
sum(case
when (question.questiontext='Enter Display Type' and answer.answerinteger=1 and project.name = 'Maybelline Reset' and region.level1=9)
then 1
else 0
end) c2,
sum(case
when (question.questiontext='Enter Display Type' and answer.answerinteger=2 and project.name = 'Maybelline Reset' and region.level1=9)
then 1
else 0
end) c3,
sum(case
when (question.questiontext='Enter Display Type' and answer.answerinteger=3 and project.name = 'Maybelline Reset' and region.level1=9)
then 1
else 0
end) c4,
sum(case
when (question.questiontext='Enter Total Time Spent in the Store, in Hours' and project.name = 'Maybelline Reset' and region.level1=9)
then answer.answerinteger
else NULL
end) c5,
sum(case
when (question.questiontext='Enter Total Time Spent in the Store, in Hours' and project.name = 'Maybelline Reset' and region.level1=9)
then answer.answerinteger
else NULL
end)+
sum(case
when (question.questiontext='Enter Total Time Spent in the Store, in Hours' and project.name = 'Secondary Maybelline Reset' and region.level1=9)
then answer.answerinteger
else NULL
end) c6,
to_char(to_number(avg(case
when (question.questiontext='Enter Total Time Spent in the Store, in Hours'and project.name in ('Maybelline Reset') and region.level1=9)
then answer.answerinteger
else 0
end)),'9.99')c7,
to_char(to_number(avg(case
when (question.questiontext='Enter Total Time Spent in the Store, in Hours'and project.name in ('Maybelline Reset','Secondary Maybelline Reset') and region.level1=9)
then answer.answerinteger
else 0
end)),'9.99') c8
FROM outlet,
call,
question,
answer,
region,
mapregionoutlet,
chain,
project
WHERE outlet.outletid=call.outletid
and call.callid=answer.callid
and answer.questionid=question.questionid
and outlet.outletid=mapregionoutlet.outletid
and mapregionoutlet.regionid=region.regionid
and outlet.chainid=chain.chainid
and call.projectid=project.projectid
and call.duedate between to_date('1/1/2001','mm/dd/yyyy') and to_date('12/31/2001','mm/dd/yyyy')
and chain.chainid=132
and project.name in ('Secondary Maybelline Reset','Maybelline Reset')



Tom Kyte
December 20, 2002 - 11:55 am UTC

select region.level1,
sum(case
when (question.questiontext='Enter Display Width in Feet' and
project.name = 'Maybelline Reset' and region.level1=9)
then answer.answerinteger
else NULL
end) c1,
sum(case
when (question.questiontext='Enter Display Type' and
answer.answerinteger=1 and project.name = 'Maybelline Reset' and
region.level1=9)
then 1
else 0
end) c2,
sum(case
when (question.questiontext='Enter Display Type' and
answer.answerinteger=2 and project.name = 'Maybelline Reset' and
region.level1=9)
then 1
else 0
end) c3,
sum(case
when (question.questiontext='Enter Display Type' and
answer.answerinteger=3 and project.name = 'Maybelline Reset' and
region.level1=9)
then 1
else 0
end) c4,
sum(case
when (question.questiontext='Enter Total Time Spent in the Store, in
Hours' and project.name = 'Maybelline Reset' and region.level1=9)
then answer.answerinteger
else NULL
end) c5,
sum(case
when (question.questiontext='Enter Total Time Spent in the Store, in
Hours' and project.name = 'Maybelline Reset' and region.level1=9)
then answer.answerinteger
else NULL
end)+
sum(case
when (question.questiontext='Enter Total Time Spent in the Store, in
Hours' and project.name = 'Secondary Maybelline Reset' and region.level1=9)
then answer.answerinteger
else NULL
end) c6,
to_char(to_number(avg(case
when (question.questiontext='Enter Total Time Spent in the Store, in
Hours'and project.name in ('Maybelline Reset') and region.level1=9)
then answer.answerinteger
else 0
end)),'9.99')c7,
to_char(to_number(avg(case
when (question.questiontext='Enter Total Time Spent in the Store, in
Hours'and project.name in ('Maybelline Reset','Secondary Maybelline Reset') and
region.level1=9)
then answer.answerinteger
else 0
end)),'9.99') c8
FROM outlet,
call,
question,
answer,
region,
mapregionoutlet,
chain,
project
WHERE outlet.outletid=call.outletid
and call.callid=answer.callid
and answer.questionid=question.questionid
and outlet.outletid=mapregionoutlet.outletid
and mapregionoutlet.regionid=region.regionid
and outlet.chainid=chain.chainid
and call.projectid=project.projectid
and call.duedate between to_date('1/1/2001','mm/dd/yyyy') and
to_date('12/31/2001','mm/dd/yyyy')
and chain.chainid=132
and project.name in ('Secondary Maybelline Reset','Maybelline Reset')
and region.level1 in ( 9, 10, 11, ... )
group by region.level1

you would get a row/region.





Are you some kind of machine ?

C, December 20, 2002 - 1:49 pm UTC

Tom, I really don't know... Are you really human ???

Sometimes I just don't believe you ALONE (I know other experts step in some rare times) can answer such long, complex and -- many times -- dull and boring questions.

How do you make it ?

It just doesn't make sense... In addition to spending soooo muuuuuch time on this site (working Saturdays and Sundays too), you must keep up-to-date with the Oracle DB, you are a VP for government, education and health, you write lengthy and awesome books, gives lectures, etc, etc, etc.

IT JUST DOESN'T MAKE SENSE...

Do you get any leisure at all ? What do you do with your spare time ?

Either you don't exist or you are some kind of godlike creature.

Hasn't Oracle just made you up ?

A reader, December 20, 2002 - 2:42 pm UTC

Sorry if I did not specify it more clear, but the first row should be based on pridicate region.level1=9 and going forward the predicate should be based on region.level2 in (10,120,245).How to do it? With the following select statement I got 3 requeired with level 2, but did not get first one with level 1


select
sum(case
when (question.questiontext='Enter Display Width in Feet' and project.name = 'Maybelline Reset' and region.level1=9)
then answer.answerinteger
else NULL
end) c1,
sum(case
when (question.questiontext='Enter Display Type' and answer.answerinteger=1 and project.name = 'Maybelline Reset' and region.level1=9)
then 1
else 0
end) c2,
sum(case
when (question.questiontext='Enter Display Type' and answer.answerinteger=2 and project.name = 'Maybelline Reset' and region.level1=9)
then 1
else 0
end) c3,
sum(case
when (question.questiontext='Enter Display Type' and answer.answerinteger=3 and project.name = 'Maybelline Reset' and region.level1=9)
then 1
else 0
end) c4,
sum(case
when (question.questiontext='Enter Total Time Spent in the Store, in Hours' and project.name = 'Maybelline Reset' and region.level1=9)
then answer.answerinteger
else NULL
end) c5,
sum(case
when (question.questiontext='Enter Total Time Spent in the Store, in Hours' and project.name = 'Maybelline Reset' and region.level1=9)
then answer.answerinteger
else NULL
end)+
sum(case
when (question.questiontext='Enter Total Time Spent in the Store, in Hours' and project.name = 'Secondary Maybelline Reset' and region.level1=9)
then answer.answerinteger
else NULL
end) c6,
to_char(to_number(avg(case
when (question.questiontext='Enter Total Time Spent in the Store, in Hours'and project.name in ('Maybelline Reset') and region.level1=9)
then answer.answerinteger
else 0
end)),'9.99')c7,
to_char(to_number(avg(case
when (question.questiontext='Enter Total Time Spent in the Store, in Hours'and project.name in ('Maybelline Reset','Secondary Maybelline Reset') and region.level1=9)
then answer.answerinteger
else 0
end)),'9.99') c8
FROM outlet,
call,
question,
answer,
region,
mapregionoutlet,
chain,
project
WHERE outlet.outletid=call.outletid
and call.callid=answer.callid
and answer.questionid=question.questionid
and outlet.outletid=mapregionoutlet.outletid
and mapregionoutlet.regionid=region.regionid
and outlet.chainid=chain.chainid
and call.projectid=project.projectid
and call.duedate between to_date('1/1/2001','mm/dd/yyyy') and to_date('12/31/2001','mm/dd/yyyy')
and chain.chainid=132
and project.name in ('Secondary Maybelline Reset','Maybelline Reset')
and region.level1 in (0,9)
and region.level2 in ( 0,9, 10, 120,245 )
group by region.level2,region.level1



Tom Kyte
December 20, 2002 - 2:59 pm UTC

if I understand, I think this is it:

Select
sum(case
when (question.questiontext='Enter Display Width in Feet' and project.name = 'Maybelline Reset' and region.level1 in ( 9, 10 ) )
then answer.answerinteger
else NULL
end) c1_lvl1,
sum(case
when (question.questiontext='Enter Display Width in Feet' and project.name = 'Maybelline Reset' and region.level2 in ( 10, 120, 245 ) )
then answer.answerinteger
else NULL
end) c1_lvl2,
.....
FROM outlet,
call,
question,
answer,
region,
mapregionoutlet,
chain,
project
WHERE outlet.outletid=call.outletid
and call.callid=answer.callid
and answer.questionid=question.questionid
and outlet.outletid=mapregionoutlet.outletid
and mapregionoutlet.regionid=region.regionid
and outlet.chainid=chain.chainid
and call.projectid=project.projectid
and call.duedate between to_date('1/1/2001','mm/dd/yyyy') and
to_date('12/31/2001','mm/dd/yyyy')
and chain.chainid=132
and project.name in ('Secondary Maybelline Reset','Maybelline Reset')
and ( region.level1 in (0,9) or region.level2 in ( 0,9, 10, 120,245 ) )

I think you need an "or" and then use the in again in the case statement.
that'll give you a single row with all of the numbers.


A reader, December 20, 2002 - 3:57 pm UTC

Does not work...
I need a function which would return only one record...
However, predicate in that function should vary. On one occasion I need to put in predicate 'where region.level1=9'
on another occasion I need to put in predicate 'where region.level2=10'.How can I accomplish that?

Tom Kyte
December 20, 2002 - 8:11 pm UTC

native dynamic sql.

..
is
type rc is ref cursor;
l_cursor rc;
..
begin
open l_cursor for your_query || your_predicate using your_bind variables...



A reader, December 23, 2002 - 9:28 am UTC

Tom,

What am I doing wrong? The following function does not work...And would it be possibile to pass 'v_where' value as a parameter as well?

FUNCTION F_ACC_RESET_ACC_DTL_COSM(p_region_level INTEGER)

RETURN PKG_RPT_ACC_RESET_ACC_DTL_COSM.v_refcursor
IS
v_where VARCHAR2(100) ;

l_cursor PKG_RPT_ACC_RESET_ACC_DTL_COSM.v_refcursor;
BEGIN
v_where:='and region.level1';
open l_cursor for 'select
sum(case
when (question.questiontext=''Enter Display Width in Feet'' and project.name = ''Maybelline Reset'' )
then answer.answerinteger
else NULL
end) c1,
sum(case
when (question.questiontext=''Enter Display Type'' and answer.answerinteger=1 and project.name = ''Maybelline Reset'' )
then 1
else 0
end) c2,
sum(case
when (question.questiontext=''Enter Display Type'' and answer.answerinteger=2 and project.name = ''Maybelline Reset'')
then 1
else 0
end) c3,
sum(case
when (question.questiontext=''Enter Display Type'' and answer.answerinteger=3 and project.name = ''Maybelline Reset'')
then 1
else 0
end) c4,
sum(case
when (question.questiontext=''Enter Total Time Spent in the Store, in Hours'' and project.name = ''Maybelline Reset'')
then answer.answerinteger
else NULL
end) c5,
sum(case
when (question.questiontext=''Enter Total Time Spent in the Store, in Hours'' and project.name = ''Maybelline Reset'')
then answer.answerinteger
else NULL
end)+
sum(case
when (question.questiontext=''Enter Total Time Spent in the Store, in Hours'' and project.name = ''Secondary Maybelline Reset'')
then answer.answerinteger
else NULL
end) c6,
to_char(to_number(avg(case
when (question.questiontext=''Enter Total Time Spent in the Store, in Hours''and project.name in (''Maybelline Reset'') )
then answer.answerinteger
else 0
end)),''9.99'')c7,
to_char(to_number(avg(case
when (question.questiontext=''Enter Total Time Spent in the Store, in Hours''and project.name in (''Maybelline Reset'',''Secondary Maybelline Reset'') )
then answer.answerinteger
else 0
end)),''9.99'') c8
FROM outlet,
call,
question,
answer,
region,
mapregionoutlet,
chain,
project
WHERE outlet.outletid=call.outletid
and call.callid=answer.callid
and answer.questionid=question.questionid
and outlet.outletid=mapregionoutlet.outletid
and mapregionoutlet.regionid=region.regionid
and outlet.chainid=chain.chainid
and call.projectid=project.projectid
and call.duedate between to_date(''1/1/2001'',''mm/dd/yyyy'') and to_date(''12/31/2001'',''mm/dd/yyyy'')
and chain.chainid=132
and project.name in (''Secondary Maybelline Reset'',''Maybelline Reset'') ' || v_where =':p_region_level'
--and region.level1=:p_region_level'
using p_region_level;
RETURN l_cursor ;

END;


Tom Kyte
December 23, 2002 - 11:14 am UTC

v_where:='and region.level1';
....

and project.name in (''Secondary Maybelline Reset'',''Maybelline Reset'') '
|| v_where =':p_region_level'
^^^^ should be || v_where || ' = :p_region_level'
using p_region_level;
RETURN l_cursor ;

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library