SQL query

[venkatesh pendharkar]

Hi
I am developing a WEB PhoneBook Catalogue for a comany intranet in JSP & MSACCESS.In this we can add employee information by following attributes:-empl Id,first Name,lastName,department,site,designation,extension,mob no,email Id.So there will be a USER database in which all these fileds will be present & employeeId will be the primary key.
We can search the employee info as follows-
These are the attributes by which I need to make search:
empl Id,first Name,lastName,department,site,designation & for each of this there will be a textbox in my JSP.
So if the user puts only firstName (eg.John)in the textbox & enetrs Search then all the employess with name John in the entire database will be displayed.
If the user enters firstName(eg.John) & Department(eg.HR) then all John in HR department will be displayed.
Like this depending on the values enetered by the user,dnamically databse will be searched.
Now my question is how can I write a single SQL query for this.my query will be something like this:-"select * from user where emplId='"+s1+"' AND firstName='"+s2+"' AND lastName='"+s3+"' AND department='"+s=4+"'....so on
& s1,s2,s3...will be the strings which are taken from databse as the values enetred by the user.
But the problem is if user only enters firstName i.e. "s1" then all other Strings "s2","s3"...will be null & so in the query null value will be passed.
So can anyone help me how to write a dynamic SQL query for where clause for MSACCESS so that I don need to hardcode SQL queries for differnet combination of user Input???

[Jan Cumps]

Originally posted by venkatesh pendharkar:
Hi



Now my question is how can I write a single SQL query for this.my query will be something like this:-"select * from user where emplId='"+s1+"' AND firstName='"+s2+"' AND lastName='"+s3+"' AND department='"+s=4+"'....so on
& s1,s2,s3...will be the strings which are taken from databse as the values enetred by the user.
But the problem is if user only enters firstName i.e. "s1" then all other Strings "s2","s3"...will be null & so in the query null value will be passed.
So can anyone help me how to write a dynamic SQL query for where clause for MSACCESS so that I don need to hardcode SQL queries for differnet combination of user Input???

Here's some pseudocode.

String query = "select bla from bla where 1 = 1";
if (s1 != null) {
  query = query + " and emplId = '" + s1 + "'";
}
if (s2 != null) {
  query = query + " and firstName = '" + s2 + "'";
}

You can use a StringBuffer to append the clauses.
It's also better to use parameter binding in stead of inserting parameters in your query string.

Regards, Jan

[venkatesh pendharkar]

Thanks a lot Jan for your relpy,this is a really nice idea.

[vijeta singh]

Note: I am not a MSAccess user, below solution are based on Oracle DB
There are two way to resolve this issue:
1)While building the dynamic sql statement you can use if else to check Not null value of any attribute and build where clause based on that like
sql_statement = 'select * from table_name where 1=1 '
If first_name IS NOT NULL THEN
sql_statement = sql_statement + 'and first_name = '+ param_first_name
else if last_name is not null then
sql_statement = sql_statement + ' and last_name = '+param_last_name
else if phone_no is not null then
....
...
end if;


2)Try to use NVL function ( NVL is sql function not sure that it will work in MS access but definitly you will something resembling to it)

select * from table_name
where first_name = nvl(param_first_name,first_name)
and last_name = nvl(param_last_name ,last_name)
and phone_number = nvl(param_phone_Number,phone_number)
...

Please refer
http://www.orafaq.com/forum/t/46536/0/