Hi all I have a form with 8 fields and based on the values entered in them I have have to fetch the records from the DataBase. Now the problem is out of the 8 fields the user may fill any number of fields and that too in any order for example the user may fill fields 1,4 and 6 or he may fill 1 and 7 or he may fill all of them (of course he has to fill at least one field)... Now how will I write a query which will work for any number and order of input parameters? and also because this query will be used in reporting(iReport) I am not allowed to write any code with it , it has to be a SQL query. Any ideas... [ August 29, 2008: Message edited by: abhishek pendkay ]
The significant problems we face cannot be solved by the same level of thinking which created them – Einstein SCJP 1.5, SCWCD, SCBCD in the making
In my opinion, this question has nothing to do with JDBC. However Campbell decided to move it to here from its original location: Oracle OAS Forum I guess, Paul and Shawn, that answers your question as to which database is being used. Abhishek wrote:
I am not allowed to write any code with it , it has to be a SQL query
So pardon my ignorance, Paul, but how does java help Abhishek?
Abhishek, you can use Oracle's NVL function, for example:
If VAR1 has a value, then the condition becomes: COL1 = VAR1 Otherwise the condition becomes COL1 = COL1 which is always true.
COL1 will not equal COL1 if COL1 is NULL. Since NULL is a state of being instead of the absence of a value or something stupid like that. So if there is a chance of any of your columns being NULL you could get unintended results.
Hems code will really mess you up without placing the left and right parenthesis around each of the statements. Since each OR statement is like providing an additional WHERE clause.
If there is a chance of one of your columns being NULL you could try something like this:
When I do something like this I make the NVL value something which can't exist in the database. For example I would NVL a VARCHAR2(1) field to '~~' since if you NVLed it to '~' and '~' could be in that column you could get unintended results.
I'm jumping in a little late here but some comments...
Never use stored procedures directly with java code, this is definitely a case where they *are not* warranted
You can construct the SQL string dynamically and still use PreparedStatements. There's no requirement the SQL string has to be hard-coded ahead of time. It's not complicated to construct a query statement here, you can just have 8 lines such as "if(x.isAvailable()) query += "NAME = ?", then set the PreparedStatement obect similarly.
Never use NVL in a JDBC SQL query. Its non-standard for all databases and not really needed in JDBC. Putting NVL in queries equivalent to putting business logic in SQL since you're making decisions about input that should be made in java.
NVL is a standard function for an Oracle database and is commonly used. This post originally came from Oracle OAS Forum where Abhishek was needing to write a Plain Jane SQL query without using any Java since iReports allows the entry of Plain Jane SQL Querys.
The syntax for NVL is not standard in every database, meaning using it would make all of your queries Oracle-only. E en if it was, it makes more sense to perform 'embedded' logic in Java (a much more robust language) than in SQL. The whole point of helper functions like NVL is that SQL doesn't have much in the way of if/then/else statements, whereas Java has plenty of room for it. Lastly, better to push more complicated logic on the Java side then bother the database server with it.
But if you want to write non-portable code that unnecessarily taxes the database and contains business logic in the SQL statements that could just as easily be in java, by all means use NVL. [ September 30, 2008: Message edited by: Scott Selikoff ]
subject: sql query based on variable number of input parameters