Skip to Main Content

Generate Dynamic WHERE clause condition

RengudiNov 17 2022 — edited Nov 17 2022

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#);



image.pngHere, manually composing the select one by one. How can i do composing where clause dynamically
Thanks

This post has been answered by Frank Kulash on Nov 17 2022
Jump to Answer
Comments
Post Details
Added on Nov 17 2022
4 comments
83 views