Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic parenthesis in where clause Possible?
icon11.gif  Dynamic parenthesis in where clause Possible? [message #198386] Mon, 16 October 2006 23:37 Go to next message
radheesh
Messages: 2
Registered: October 2006
Junior Member
Hi friends ..

i am having WHERE clause as following...

WHERE NUM_MIN_SI BETWEEN 800 AND 15000
AND   NUM_MAX_SI BETWEEN 2000 AND 500000
AND   NUM_FG_MIN_PREMIUM BETWEEN 5 AND 200
AND   NUM_ENDOR_MIN_PREMIUM BETWEEN 10 AND 100
AND   NUM_LOADING_PERCENT BETWEEN 0 AND 15
AND   NUM_EXCESS_PERCENT BETWEEN 10 AND 70
AND   NUM_MAX_CUMM_LOAD_PERCENT BETWEEN 10 AND 70
AND   NUM_EXCESS_PERCENT BETWEEN 10 AND 50
AND   NUM_MAX_EXCESS BETWEEN 20 AND 5000
AND   ASCIISTR(CHR(VCH_ALLOW))='Y'
AND   ASCIISTR(CHR(VCH_USAGE_ALLOW))='Y'
AND   ASCIISTR(CHR(VCH_MODEL_ALLOW))='Y'


In this condition AND & OR Conditons will come dynamically
That is it will change as user selecting the condition in
front end

Problem is the parenthesis that i need to use during ORcondition...

I am having some idea to send AND & OR but i dont know
how to put parenthesis dynamically based on OR condition

I need your help friends

Regards,
Radheesh

[mod edit for code tags]

[Updated on: Tue, 17 October 2006 02:01] by Moderator

Report message to a moderator

Re: Dynamic parenthesis in where clause Possible? [message #198415 is a reply to message #198386] Tue, 17 October 2006 02:20 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You could, for example, write a PL/SQL procedure which would enable you to dynamically create the whole statement, and - as a part of it - its WHERE clause.
Re: Dynamic parenthesis in where clause Possible? [message #198426 is a reply to message #198386] Tue, 17 October 2006 02:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You are presumably going to be building this query dynamically using NDS.
Can you not just need to build up each of the OR terms individually, and then wrap them in a bracket pair before including them in the query?
icon5.gif  Re: Dynamic parenthesis in where clause Possible? [message #198484 is a reply to message #198415] Tue, 17 October 2006 06:17 Go to previous message
radheesh
Messages: 2
Registered: October 2006
Junior Member
hi Littlefoot .........

i cant able to catch ur answer............

exact thing i wanted is parenthesis around OR condition
---------------This is one type-----------
WHERE

(NUM_MIN_SI BETWEEN 800 AND 15000
OR
NUM_MAX_SI BETWEEN 2000 AND 500000)
AND
NUM_FG_MIN_PREMIUM BETWEEN 5 AND 200
AND
(NUM_ENDOR_MIN_PREMIUM BETWEEN 10 AND 100
OR
NUM_LOADING_PERCENT BETWEEN 0 AND 15)
AND
NUM_EXCESS_PERCENT BETWEEN 10 AND 70

-------------- This is another type-------------
NUM_MIN_SI BETWEEN 800 AND 15000
AND
(NUM_MAX_SI BETWEEN 2000 AND 500000
OR
NUM_FG_MIN_PREMIUM BETWEEN 5 AND 200)
AND
NUM_ENDOR_MIN_PREMIUM BETWEEN 10 AND 100
AND
(NUM_LOADING_PERCENT BETWEEN 0 AND 15)
OR
NUM_EXCESS_PERCENT BETWEEN 10 AND 70)
---------------------------------------------------------------

like above example there will be many combinations of AND & OR

so that only i am asking about dynamic parenthsis...
little if u have any idea ,explain with examples....
so that it will be easy for me to catch

If anybody knows.... help me with examples........

regards,
Radheesh
Previous Topic: Oracle Database 10g Express Edition Backup Question
Next Topic: CSV file data into Oracle Table Using PL/SQL
Goto Forum:
  


Current Time: Thu Dec 08 05:56:39 CST 2016

Total time taken to generate the page: 0.11041 seconds