sql query based on variable number of input parameters

[abhishek pendkay]

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 ]

[Campbell Ritchie]

You have really been dropped in it; you have 2^8-1 = 255 possible combinations of vacant fields .

What format are those fields in? Can you use "like '%'" when there is a vacant field? Can you use "is null" or "is not null" when there is a vacant field?
Don't know any more, sorry.

[Campbell Ritchie]

Select the fields actually filled in into a temporary table or a view??

Still not sure.

I actually think you might stand a better chance of a helpful answer on the JDBC forum, so I shall try moving you there.

[Paul Yule]

I do not think I quite understand the requirements...you can't use code to generate the SQL string because that would seriously be a pain to try and cover every single combination of query.

So no :

  
 
StringBuffer SQL_Query = new StringBuffer();
SQL_Query .append("SELECT "); 
 
if(1stFormFieldChosen){
SQL_Query.append("CollumName as Title");
}
 
if(2ndFormFieldChosen){
SQL_Query.append("CollumName2 as Whatever");
}
 
....
 
SQL_Query.append("FROM table ");
SQL_Query.append("WHERE ");
 
if(1stFormFieldChosen){
   SQL_Query.append("FieldName= ?");  
 
}
//keep a hashTable or something of all the parameters and where they go in //the query

I've never used that technology =(.You are not able to do this because of limitations on the reports side of things?
[ August 29, 2008: Message edited by: Paul Yule ]

[Shawn Montague]

Is this MySQL or SQL Server? If it is SQL Server, you could write a stored procedure and handle the params in the sproc.

[Campbell Ritchie]

MySQL will happily handle stored procedures too.

[Avi Abrami]

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:

select COL
  from TAB
 where COL1 = nvl(VAR1, COL1)

If VAR1 has a value, then the condition becomes:
COL1 = VAR1
Otherwise the condition becomes
COL1 = COL1
which is always true.

Good Luck,
Avi.

[Hemavathy Ramamoorthy Gopal]

Hi,

I think using ' OR ' operator in select statement we can fetch the records.
Performance need to be checked based the availability of data.

Eg : WHERE Input1 = 'value1' or isnull(Input1,'') = ''
AND Input2 = 'value2' or isnull(Input2,'') = ''

[Campbell Ritchie]

Sorry if people think I have moved the thread inappropriately. I think both the last solutions would probably work, and as they say in London, "You pays your money and you takes your choice." ;)

[Campbell Ritchie]

Actually you need to make sure when testing for null that it is the input you check, not the value in the table.

[abhishek pendkay]

Thanks everyone for your reply..
and yes I got my answer Avi Abrami's solution works, I could actually do it using the nvl function so thanks for that Avi

Campbell

Sorry if people think I have moved the thread inappropriately

No you were not wrong at all in moving my post to this forum...Thanks Campbell :thumb:
[ September 01, 2008: Message edited by: abhishek pendkay ]

[Jason Pirkey]

Avi,

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.

Like:

WHERE (col1 = var1 OR col1 IS NULL)
AND   (col2 = var2 OR col2 IS NULL)

If there is a chance of one of your columns being NULL you could try something like this:

WHERE NVL(col1,'~') = NVL(var1,NVL(col1,'~'))
AND   NVL(col2,'~') = NVL(var2,NVL(col2,'~'))

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.

[Scott Selikoff]

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.

Hope that helps.
-Scott

[Jason Pirkey]

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.

[Scott Selikoff]

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 ]