Home » SQL & PL/SQL » SQL & PL/SQL » Dynamically prepare the query (Oracle 10.2.0.3 database)
Dynamically prepare the query [message #395088] Tue, 31 March 2009 04:55 Go to next message
muralimadhavuni
Messages: 26
Registered: November 2005
Location: HYD
Junior Member
problem description - Based on the value of a column, the SQL query should be prepared dynamically. This should be done in only SQL query.

DDL
-------------
create table loan
(
 intrest1 varchar2(10),
 intrest2 varchar2(10),
 intrest3 varchar2(10)
);

DML
------------------
Insert into LOAN  (INTREST1, INTREST2, INTREST3)
 Values  ('10', '10.50', '10.80');
Insert into LOAN   (INTREST1, INTREST2, INTREST3)
 Values  ('11', '11.50', '11.80');
Insert into LOAN   (INTREST1, INTREST2, INTREST3)
 Values    ('12', '12.50', '12.80');
 
COMMIT;

Requirement:
If the value of column INTREST1 is 11, then i have to prepare a query which has the where condition like bwlo.
WHERE INTREST2='some value'.
If the value of column INTREST1 is 12, then i have to prepare a query which has the where condition like bwlo.
WHERE INTREST3='some value' .

So, based on the value of column INTREST1, the where condition should change.

Please can some one help me on this. Appreciation for the timely response.

[Updated on: Tue, 31 March 2009 04:59] by Moderator

Report message to a moderator

Re: Dynamically prepare the query [message #395093 is a reply to message #395088] Tue, 31 March 2009 05:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use OR, it's easier and less error-prone (and can be more performant) than using dynamic sql.

something along the lines:
where (   col1 = 1
      and col2 = <value>
      )
or    (   col1 = 2
      and col3 = <value>
      )

[Updated on: Tue, 31 March 2009 05:01]

Report message to a moderator

Re: Dynamically prepare the query [message #395095 is a reply to message #395088] Tue, 31 March 2009 05:07 Go to previous messageGo to next message
muralimadhavuni
Messages: 26
Registered: November 2005
Location: HYD
Junior Member
Hi Frank, Thanks for your reply.But, your answer will not solve my probem.

detail problem description is below.

IF LOAN.INTREST1 = '10' THEN
SELECT INTREST1, INTREST2 FROM LOAN
WHERE INTREST2='11';
ELSIF LOAN.INTREST1 = '11' THEN
SELECT INTREST1, INTREST3 FROM LOAN
WHERE INTREST3='12';
END IF;

I need a SQL query to meet the above requirement.

Re: Dynamically prepare the query [message #395098 is a reply to message #395088] Tue, 31 March 2009 05:18 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your original post stated that only the where clause changed.
Your next post indicates that the select list also changes.

Which is correct?
Re: Dynamically prepare the query [message #395099 is a reply to message #395088] Tue, 31 March 2009 05:19 Go to previous messageGo to next message
muralimadhavuni
Messages: 26
Registered: November 2005
Location: HYD
Junior Member
sorry for the typo error.

only where condition will change.
Re: Dynamically prepare the query [message #395127 is a reply to message #395099] Tue, 31 March 2009 07:18 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In which case @Frank's solution is fine.
SELECT <columns>
FROM   loan
WHERE  (   (intrest1 = 11 and intrst2 = <some value>)
        or (intrest1 = 12 and intrst2 = <some other value))
Previous Topic: Merging of Rows with a sequence number
Next Topic: ORA-01779: cannot modify a column which maps to a non key-preserved table
Goto Forum:
  


Current Time: Sun Dec 11 02:44:33 CST 2016

Total time taken to generate the page: 0.20997 seconds