Hi Gurus
Greetings.
Requirement : I want to dynamically create the where condition and extract the result set for the count.
Sample tables:-
DOOR_NAME(NAME,ADDRESS,TYPE) AS
SELECT 'Door 1' AS NAME,'123 Main St' AS ADDRESS,1 AS TYPE FROM dual
UNION ALL
SELECT 'Door 2','456 Elm St',2 FROM dual
UNION ALL
SELECT 'Door 3','789 Oak St',3 FROM dual
UNION ALL
SELECT 'Door 4','101 First Ave',4 FROM dual
UNION ALL
SELECT 'Door 5','202 Second Ave',5 FROM dual
UNION ALL
SELECT 'Door 6','303 Third Ave',6 FROM dual
UNION ALL
SELECT 'Door 7','404 Fourth Ave',7 FROM dual
UNION ALL
SELECT 'Door 8','505 Fifth Ave',8 FROM dual
UNION ALL
SELECT 'Door 9','606 Sixth Ave',9 FROM dual
UNION ALL
SELECT 'Zone D','101 First Ave',4 FROM dual
UNION ALL
SELECT 'Zone E','202 Second Ave',5 FROM dual
UNION ALL
SELECT 'Zone F','303 Third Ave',6 FROM dual
UNION ALL
SELECT 'Zone G','404 Fourth Ave',7 FROM dual;
DOOR_ZONES(NAME,ADDRESS,VISIBILITY,NUMBER#) AS
SELECT 'Zone A' AS NAME,'123 Main St' AS ADDRESS,1 AS VISIBILITY,1 AS NUMBER# FROM dual
UNION ALL
SELECT 'Zone B','456 Elm St',2,2 FROM dual
UNION ALL
SELECT 'Zone C','789 Oak St',3,3 FROM dual
UNION ALL
SELECT 'Zone D','101 First Ave',4,4 FROM dual
UNION ALL
SELECT 'Zone E','202 Second Ave',5,5 FROM dual
UNION ALL
SELECT 'Zone F','303 Third Ave',6,6 FROM dual
UNION ALL
SELECT 'Zone G','404 Fourth Ave',7,7 FROM dual
UNION ALL
SELECT 'Zone H','505 Fifth Ave',8,8 FROM dual
UNION ALL
SELECT 'Zone I','606 Sixth Ave',9,9 FROM dual
UNION ALL
SELECT 'Door 3','789 Oak St',3,0 FROM dual
UNION ALL
SELECT 'Door 4','101 First Ave',4,0 FROM dual
UNION ALL
SELECT 'Door 5','202 Second Ave',5,0 FROM dual
UNION ALL
SELECT 'Door 6','303 Third Ave',6,0 FROM dual;
SELECT count(*) from (
SELECT dn.NAME,
dn.ADDRESS,
dn.TYPE#,
dz.NAME,
dz.ADDRESS,
dz.VISIBILITY#,
dz.NUMBER#
FROM DOOR_ZONES dz, DOOR_NAME dn
WHERE dn.NAME = dz.NAME);
SELECT count(*) from (
SELECT dn.NAME,
dn.ADDRESS,
dn.TYPE#,
dz.NAME,
dz.ADDRESS,
dz.VISIBILITY#,
dz.NUMBER#
FROM DOOR_ZONES dz, DOOR_NAME dn
WHERE dn.ADDRESS = dz.ADDRESS);
SELECT count(*) from (
SELECT dn.NAME,
dn.ADDRESS,
dn.TYPE#,
dz.NAME,
dz.ADDRESS,
dz.VISIBILITY#,
dz.NUMBER#
FROM DOOR_ZONES dz, DOOR_NAME dn
WHERE dn.TYPE# = dz.VISIBILITY#);
Here, manually composing the select one by one. How can i do composing where clause dynamically
Thanks