Conditional statement within Where clause subquery [message #332652] |
Wed, 09 July 2008 05:10  |
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 #332663 is a reply to message #332652] |
Wed, 09 July 2008 05:40   |
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]
|
|
|
|
|
|
|