Generate Dynamic WHERE clause condition

Rengudi

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

Frank Kulash

If you just want one query that can do the work of any of the three queries you posted by using a dynamic WHERE clause, then you can simply make the WHERE clause (or the part of it after the keyword WHERE) dyanmic. Using a substitution variable:

DEFINE  where_condition = "dn.NAME = dz.NAME"


SELECT count(*) AS num_found from (
SELECT dn.NAME,
       dn.ADDRESS,
       dn.TYPE,		-- there is no TYPE# column in the sample data
       dz.NAME,
       dz.ADDRESS,
       dz.VISIBILITY,	-- there is no VISIBILITY# column in the sample data
       dz.NUMBER#
  FROM DOOR_ZONES dz, DOOR_NAME dn
 WHERE &where_condition);

Since you only need the COUNT, and not any of the other columns, you can omit the other columns, and simply say:

DEFINE  where_condition = "dn.NAME = dz.NAME"

SELECT count(*) AS num_found
  FROM DOOR_ZONES dz, DOOR_NAME dn
 WHERE &where_condition;

Frank Kulash

HI, @Rengudi

Requirement : I want to dynamically create the where condition and extract the result set for the count.

"Dynamic" notrmally means something that must be hard-coded will not be known untill you run the query. What is unknown in this problem?

Do those CREATE TABLE statements work when you run them? I get an error because you're trying to use NUMBER as a column name.

Always post the complete, exact results you want from the given sample data.

Rengudi

Hi @Frank Kulash

Thanks for your kind reply; My Sorry it should be NUMBER# , Typo error my side

I corrected now.

Good day/

Frank Kulash

Hi, @Rengudi

I corrected now.

Okay. Don't forget to explain what is dynamic about this problem, and to post the exact results you want from the given sample data.

Frank Kulash

If you just want one query that can do the work of any of the three queries you posted by using a dynamic WHERE clause, then you can simply make the WHERE clause (or the part of it after the keyword WHERE) dyanmic. Using a substitution variable:

DEFINE  where_condition = "dn.NAME = dz.NAME"


SELECT count(*) AS num_found from (
SELECT dn.NAME,
       dn.ADDRESS,
       dn.TYPE,		-- there is no TYPE# column in the sample data
       dz.NAME,
       dz.ADDRESS,
       dz.VISIBILITY,	-- there is no VISIBILITY# column in the sample data
       dz.NUMBER#
  FROM DOOR_ZONES dz, DOOR_NAME dn
 WHERE &where_condition);

Since you only need the COUNT, and not any of the other columns, you can omit the other columns, and simply say:

DEFINE  where_condition = "dn.NAME = dz.NAME"

SELECT count(*) AS num_found
  FROM DOOR_ZONES dz, DOOR_NAME dn
 WHERE &where_condition;