Forum Stats

  • 3,874,184 Users
  • 2,266,680 Discussions
  • 7,911,761 Comments

Discussions

Generate Dynamic WHERE clause condition

Rengudi
Rengudi Member Posts: 583 Bronze Badge
edited Nov 17, 2022 4:04PM in SQL & PL/SQL

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

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,746 Red Diamond
    edited Nov 18, 2022 12:08AM Answer ✓

    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;
    


    Rengudi

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,746 Red Diamond
    edited Nov 17, 2022 4:01PM

    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
    Rengudi Member Posts: 583 Bronze Badge

    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
    Frank Kulash Member, Moderator Posts: 42,746 Red Diamond

    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
    Frank Kulash Member, Moderator Posts: 42,746 Red Diamond
    edited Nov 18, 2022 12:08AM Answer ✓

    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;
    


    Rengudi