Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Unfortunately Java ME SDK 3.2 is not publicaly available now. Could you please help me to understend why are you asking for such an old version so I might be able to suggest you an alternative solution? What a use case do you have?

gdillen

Hi,

Sorry for the late answer.

Java ME SDK 3.2 is needed to build Eclipse PAHO MQTT library (org.eclipse.paho.mqtt.java.git - MQTT Client Package - Java Language).

From the README file:

Paho Java ME client for MQTT

The Java ME client requires the Oracle J2ME SDK 3.2 (which is only available for windows) and assumes it will be installed in c:/Java_ME_platform_SDK_3.2/

The client jar is built using ant and the build.xml file in org.eclipse.paho.jmeclient.mqttv3/

Sergey.N-Oracle

Hi!

J2ME SDK3.4 is good choice to substitute SDK3.2 without any source and binaries update

/Sergey

gdillen

Hi,

I asked the guys from Eclipse PAHO: Java ME SDK 3.2 is a requirement!

thanks.

Guy

Sergey.N-Oracle

Didn't try to build but reference to SDK3.2 and its library is placed at root/org.eclipse.paho.jmeclient/org.eclipse.paho.jmeclient.mqttv3/build.xml

<path id="lib.path.ref">
<fileset dir="c:/Java_ME_platform_SDK_3.2/lib">
<include name="**/cldc_1.1.jar" />
<include name="**/jsr75_1.0.jar" />
<include name="**/jsr120_1.1.jar" />
<include name="**/jsr172_1.0.jar" />
<include name="**/jsr177_1.0.jar" />
<include name="**/jsr179_1.0.jar" />
<include name="**/jsr280_1.0.jar" />
<include name="**/impng_1.0.jar" />
</fileset>
</path>

Please substitute SDK_3.2 with SDK_3.4

gdillen

Thanks.

I know and I already changed and tried (a while ago) by changing v3.2 in v3.4 in the build.xml; but that doesn't work unfortunately.

Thanks.

Guy

Sergey.N-Oracle

Hi!

May I ask to show error log?

/Sergey

1 - 7

Post Details

Added on Nov 17 2022
4 comments
218 views