Home » SQL & PL/SQL » SQL & PL/SQL » Conditional statement within Where clause subquery (Oracle11G)
Conditional statement within Where clause subquery [message #332652] Wed, 09 July 2008 05:10 Go to next message
chandrad
Messages: 4
Registered: July 2008
Junior Member

CREATE OR REPLACE PACKAGE PKG_KPIQUERY AS
/* TODO enter package declarations (types, exceptions, methods etc) here */
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE kpilist_rep(r_id IN VARCHAR2,
Proj_id IN VARCHAR2, Stag_id IN VARCHAR2, Stat_id IN VARCHAR2,
KPINameCursor OUT T_CURSOR);

END PKG_KPIQUERY;

/


CREATE OR REPLACE PACKAGE BODY PKG_KPIQUERY AS
PROCEDURE kpilist_rep(r_id IN VARCHAR2,
Proj_id IN VARCHAR2, Stag_id IN VARCHAR2, Stat_id IN VARCHAR2,
KPINameCursor OUT T_CURSOR) AS
/*
kpi_p varchar2(30):= NULL;
*/
BEGIN


/*
If r_id != null then kpi_p:=' and kr.report_id=r_id';
else
kpi_p:=' ';
end if ;
*/

OPEN KPINameCursor FOR
select distinct vt.id, vt.name from variabletype vt, kpireport kr, kpistate ks, vie_pks vpk
where vt.id=kr.kpi_id and vt.id=ks.kpi_id and vt.id=vpk.kpi_id
and kr.report_id IN
(
CASE r_id
When NULL then
(Select report_id from Report)
ELSE
(Select report_id from Report where report_id=r_id)
END
)
and ks.STATE_ID IN
(
CASE stat_id
When NULL then
(Select state_id from State)
ELSE
(Select state_id from State where state_id=stat_id)
END
)

and vpk.PROJECT_ID IN
(
CASE proj_id
When NULL then
(Select project_id from vie_pks)
ELSE
(Select project_id from vie_pks where project_id=proj_id)
END
)

and vpk.stage_ID IN
(
CASE stag_id
When NULL then
(Select stage_id from vie_pks)
ELSE
(Select stage_id from vie_pks where stage_id=stag_id)
END
)

;


END kpilist_rep;

END PKG_KPIQUERY;

/
*********************************

This is what is supposed tobe happening.

CASE r_id
When NULL then
(Select report_id from Report)
ELSE
(Select report_id from Report where report_id=r_id)
END

I am aware of the fact that, this statement is single row query. But NULL condition results in multiplerows. How can this be achieved. Any suggestions. I dont have to use Case/decode. But conditional statement should be used.

Re: Conditional statement within Where clause subquery [message #332656 is a reply to message #332652] Wed, 09 July 2008 05:21 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
PLEASE format your code before posting anything.
Read the Forum Posting guidelines in the "HELP" link in the home page
Re: Conditional statement within Where clause subquery [message #332662 is a reply to message #332652] Wed, 09 July 2008 05:39 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
If my understanding is Correct,you are trying to select all the records in case stag_id is null

You may rewrite your code like this

Select stage_id from vie_pks where stage_id=nvl(stag_id,stage_id)




Thanks,
Dwarak.k
Re: Conditional statement within Where clause subquery [message #332663 is a reply to message #332652] Wed, 09 July 2008 05:40 Go to previous messageGo to next message
chandrad
Messages: 4
Registered: July 2008
Junior Member

CREATE OR REPLACE PACKAGE PKG_KPIQUERY AS
  /* TODO enter package declarations (types, exceptions, methods etc) here */
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE kpilist_rep(r_id IN VARCHAR2, 
Proj_id IN VARCHAR2, Stag_id IN VARCHAR2, Stat_id IN VARCHAR2,
KPINameCursor OUT T_CURSOR);

END PKG_KPIQUERY;

/


CREATE OR REPLACE PACKAGE BODY PKG_KPIQUERY AS
PROCEDURE kpilist_rep(r_id IN VARCHAR2, 
Proj_id IN VARCHAR2, Stag_id IN VARCHAR2, Stat_id IN VARCHAR2,
KPINameCursor OUT T_CURSOR) AS
/*
kpi_p varchar2(30):= NULL;
*/
BEGIN


/*
If r_id != null then kpi_p:=' and kr.report_id=r_id';
else 
kpi_p:=' ';
end if ;
*/

OPEN KPINameCursor FOR 
select distinct vt.id, vt.name from variabletype vt, kpireport kr, kpistate ks, vie_pks vpk 
where vt.id=kr.kpi_id and vt.id=ks.kpi_id and vt.id=vpk.kpi_id 
and kr.report_id IN
(
CASE r_id
When NULL then
(Select report_id from Report)
ELSE 
(Select report_id from Report where report_id=r_id)
END
)
and ks.STATE_ID IN
(
CASE stat_id
When NULL then
(Select state_id from State)
ELSE 
(Select state_id from State where state_id=stat_id)
END
)

and vpk.PROJECT_ID IN
(
CASE proj_id
When NULL then
(Select project_id from vie_pks)
ELSE 
(Select project_id from vie_pks where project_id=proj_id)
END
)

and vpk.stage_ID IN
(
CASE stag_id
When NULL then
(Select stage_id from vie_pks)
ELSE 
(Select stage_id from vie_pks where stage_id=stag_id)
END
)

;


END kpilist_rep;

END PKG_KPIQUERY;

/


*********************************

This is what is supposed tobe happening.

CASE r_id
When NULL then
(Select report_id from Report)
ELSE
(Select report_id from Report where report_id=r_id)
END

I am aware of the fact that, this statement is single row query. But NULL condition results in multiplerows. How can this be achieved. Any suggestions. I dont have to use Case/decode. But conditional statement should be used.

[/quote]
Re: Conditional statement within Where clause subquery [message #332664 is a reply to message #332652] Wed, 09 July 2008 05:47 Go to previous messageGo to next message
chandrad
Messages: 4
Registered: July 2008
Junior Member

CASE r_id
When NULL then
(Select report_id from Report)
ELSE 
(Select report_id from Report where report_id=r_id)
END



condition is the selection of r_id(which is the parameter).
if a r_id value is given the select statement would result in single result.

If r_id is not given, which is null, then selection should result all rows. How can such a condition be written. Case is my best guess. But within a case, when it returns single row, it processes fine. But when the select statement returns multiple row, it throws up an error in execution.


Hope i made it clear. this procedure should be looked in total, as there are 4 such selections like r_id.





Re: Conditional statement within Where clause subquery [message #332667 is a reply to message #332664] Wed, 09 July 2008 05:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Select report_id 
from    Report w
here    report_id=nvl(r_id,report_id)
Re: Conditional statement within Where clause subquery [message #332668 is a reply to message #332664] Wed, 09 July 2008 05:59 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
I hope my previous work around should be fine.

For further reading about CASE statements and NULL,
http://www.dba-oracle.com/t_case_statement_else_null_value.htm

Thanks,
Dwarak.K
Re: Conditional statement within Where clause subquery [message #332674 is a reply to message #332652] Wed, 09 July 2008 06:10 Go to previous message
chandrad
Messages: 4
Registered: July 2008
Junior Member

Thanks for the help both Dwarak and Jrowbottom.

Will shout if I get stuck.
Previous Topic: Generating a Sequence
Next Topic: Is "pipelining" feature available in 10G to select from PL/SQL table?
Goto Forum:
  


Current Time: Sat Dec 03 22:08:01 CST 2016

Total time taken to generate the page: 0.24406 seconds