Home » SQL & PL/SQL » SQL & PL/SQL » Parameterized view (Oracle 9i)
Parameterized view [message #330362] Sun, 29 June 2008 22:52 Go to next message
vavesw
Messages: 5
Registered: June 2008
Junior Member
HI All

I have the one table with columns ID, COL1, COL2, COL3. Right now I am writing code in Java to build the SQL. Here is a code snippet

<code>
If (uiParam.equals("A") {
sqlBuffer.append(" WHERE COL1 < 100");
else
sqlBuffer.append(" WHERE COL2 < 200");

</code>

I want to move this java part also into the SQL. I am planning to create a view for this because it is used at many places. But I am not sure how to do it. Can anyone pl help

Tx
Sam

Re: Parameterized view [message #330363 is a reply to message #330362] Sun, 29 June 2008 22:54 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guideline as stated in URL above


>But I am not sure how to do it.
You need to devise a solution that will work because this won't.
Re: Parameterized view [message #330366 is a reply to message #330362] Sun, 29 June 2008 23:17 Go to previous messageGo to next message
vavesw
Messages: 5
Registered: June 2008
Junior Member
I just want to find out if its possible to do it in oracle. Otherwise I can go with a java solution

Thanks in advance
Sam
Re: Parameterized view [message #330370 is a reply to message #330362] Sun, 29 June 2008 23:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at these threads on AskTom:
Parameterized View & Truncation of Table
Parametrised view !

Regards
Michel
Re: Parameterized view [message #330372 is a reply to message #330362] Sun, 29 June 2008 23:43 Go to previous messageGo to next message
vavesw
Messages: 5
Registered: June 2008
Junior Member
There are 2 parts to this question.

1) Parameterized view for which the links explain by itself

2) The where condition is dynamic. The columns used in the where condition is different depending upon the parameter value.

Just want to find out if its possible in Oracle, to build a dynamic WHERE condition depending upon parameters

Thanks in advance
Sam
Re: Parameterized view [message #330379 is a reply to message #330372] Mon, 30 June 2008 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The columns used in the where condition is different depending upon the parameter value.

What about using OR in view?

Regards
Michel
Re: Parameterized view [message #330382 is a reply to message #330362] Mon, 30 June 2008 00:59 Go to previous messageGo to next message
vavesw
Messages: 5
Registered: June 2008
Junior Member
OR wouldnt work. As I need to take the parameter and depending upon that I need to have the WHERE column. OR would include both the columns in the where condition all the time
Re: Parameterized view [message #330383 is a reply to message #330362] Mon, 30 June 2008 01:05 Go to previous messageGo to next message
vavesw
Messages: 5
Registered: June 2008
Junior Member
I just did this....
want to know if there is a better way to do it

CREATE OR REPLACE VIEW AS TRANS
SELECT ID, COL1 as NEW_COL, 'T' as type
FROM table1
union all
SELECT ID, COL2 as NEW_COL, 'A' as type
FROM table1


And would use the view as follows
SELECT * from TRANS
WHERE TYPE = 'A' and NEW_COL < 100


the second condition would be as follows

SELECT * from TRANS
WHERE TYPE = 'T' and NEW_COL < 200

Would like to know if there is any other better way

Thanks
Sam
Re: Parameterized view [message #330387 is a reply to message #330382] Mon, 30 June 2008 01:12 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
vavesw wrote on Mon, 30 June 2008 07:59
OR wouldnt work. As I need to take the parameter and depending upon that I need to have the WHERE column. OR would include both the columns in the where condition all the time

OR will work... if you add some AND with the parameter.

Regards
Michel

Previous Topic: convert error to a table format
Next Topic: Sysdate is not working
Goto Forum:
  


Current Time: Wed Dec 07 05:18:23 CST 2016

Total time taken to generate the page: 0.07295 seconds