| Parameterized view [message #330362] |
Sun, 29 June 2008 22:52  |
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 #330372 is a reply to message #330362] |
Sun, 29 June 2008 23:43  |
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 #330382 is a reply to message #330362] |
Mon, 30 June 2008 00:59 |
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 |
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 |
 |
Michel Cadot
Messages: 68645
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
