• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Dynamic SQL for Search

I am trying to build search functionalities and it has more than 20 search criteria’s.
User may or may not select all search criteria and also may be all users will have different selected values for search. I am looking for good approach to solve this issue.
0
skumar99
Asked:
skumar99
  • 5
  • 4
  • 2
  • +3
1 Solution
 
sameer_goyalCommented:
This approach should work for you

Lets assume you are querying a single table with 20 columns in it and these 20 columns can actually be the different search criterias.


SELECT *
FROM table1
where (@column1 is null or column1 = @column1)
and (@column2 is null or column2 = @column2)
and (@column3 is null or column3 = @column3)
and (@column4 is null or column4 = @column4)
and (@column5 is null or column5 = @column5)
and (@column6 is null or column6 = @column6)
and (@column7 is null or column7 = @column7)
and (@column8 is null or column8 = @column8)
and (@column9 is null or column9 = @column9)
.
.
.
.
.
.
and so on.

Considering that you have written a stored procedure for executing the search, you pass all 20 search criterias in the worst case as parameters to this procedure. So depending on whether the search criteria has a value or is null, the query will adapt to take in to consideration the value for search

hope it helps
0
 
slightwv (䄆 Netminder) Commented:
I don't think the above example will return the correct results.

For example, you have data where col1='a' and col2='b' and the user selects only col2='b'.  The query above will ignore the row above.

Personally I would just dynamically build the select based on the user inputs.
0
 
sameer_goyalCommented:
slightwv: why do you say the query will ignore the condition col1 ='a' if user selects col2='b'?

since the user is not selecting any condition for col1, that means that is not to be considered for the query and the sql is going to behave as is expected.

Dynamic queries have more evils than one. And one must be careful when using them
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
slightwv (䄆 Netminder) Commented:
>>slightwv: why do you say the query will ignore the condition col1 ='a' if user selects col2='b'?

Check the following test case.  It simulates only passing in a value for parameter2.

It should return the row since col2='b' but returns no rows.

drop table tab1 purge;
create table tab1( col1 char(1), col2 char(1));
insert into tab1 values('a','b');
commit;

--no col1 parameter so it is an empty string or considered NULL in Oracle
---passes in a 'b' for the col2 parameter
select * from tab1 where (col1 is null or col1='')
and (col2 is null or col2 = 'b')
/

Open in new window

0
 
Amitkumar PSr. ConsultantCommented:
Try below query is specific to Oracle.

select
      *
from
      table1
where
      1=1
and       nvl(col1,'%') like nvl(value1,'%')
and       nvl(col2,'%') like nvl(value2,'%')
and       nvl(col3,'%') like nvl(value3,'%');
0
 
johnsoneSenior Oracle DBACommented:
Also, the is null approach would only work for columns that do not contain nulls.  If the column contains nulls, then (NULL, 'B') matches with ('A', 'B'), which shouldn't be true.
0
 
sameer_goyalCommented:
sligthmv: Please check your query. it is not equivalent to passing only col2 value

select * from tab1 where (col1 is null or col1='')
and (col2 is null or col2 = 'b')

you are actually checking if col1 = '' i.e. col1 is equal to blank
and hence your query is not returning anything

and moreover, this query in no way is equivalent to my query above since you are comparing columns and not parameters

if you want to replicate the query, create a proc and pass around parameters and use them where I used and then see if it works or not.

That query is a tried and tested solution and works everytime.

I believe, if there are reservations for someone's solution, you should prove it by trying it out in totality and not just come up your own modifications.

Thanks,
0
 
Amitkumar PSr. ConsultantCommented:
johnsone,

Consider the below values for the parameters.

Value1 = A
ValueB = null
ValueC = null

The query would be like the given below.

select
      *
from
      table1
where
      1=1
and       nvl(col1,'%') like 'A'
and       nvl(col2,'%') like '%'
and       nvl(col3,'%') like '%';


skumar99, Please check that below one (same as my earlier comment) fulfills your requirement.
select
      *
from
      table1
where
      1=1
and       nvl(col1,'%') like nvl(value1,'%')
and       nvl(col2,'%') like nvl(value2,'%')
and       nvl(col3,'%') like nvl(value3,'%');

You can use other functions also like trim, upper, lower etc.
ex. upper(trim(nvl(col1,'%'))) like upper(trim(nvl(value1,'%')))

You also can match the value partially
ex. nvl(col1,'%') like (nvl(value1,'%') || '%')
0
 
skumar99Author Commented:
Sorry i missed to mentioned about tables. There is multiple tables for condition and also from one table there is more than one values. Please see below few tables

CREATE TABLE `IBM_USER_PROFILE` (
  `PROFILE_ID` int(20) NOT NULL AUTO_INCREMENT,
  `USER_ID` int(25) NOT NULL,  
        
  `PROFILE_CREATED_BY` varchar(40),
  `GENDER` varchar(10),
  `BIRTH_DATE` date,
  `AGE` int(3),
  `MOTHER_TONGUE` varchar(60),
  `PERSONAL_VALUE` varchar(40),
  `MARITAL_STATUS` varchar(40),                  
  `CHILDREN_TYPE` varchar(40),                        
  `KID_NUMBER` varchar(15),  
      
  `COMMUNITY_NAME` varchar(40),                        
  `RELIGION_TYPE` varchar(40),                        
  `CASTE_NAME` varchar(60),                        
  `SUB_CASTE_NAME` varchar(60),
  `CASTE_NO_BAR` varchar(5),
      
  `DIET_TYPE` varchar(40),                        
  `DRINK_TYPE` varchar(40),                        
  `SMOKE_TYPE` varchar(40),                        
      
  `COMPLEXION_TYPE` varchar(40),                        
  `BODY_TYPE` varchar(40),                        
  `HEIGHT_IN` char(40),
  `HEIGHT_SEARCH` int(5),
  `WEIGHT_IN` char(40),                        
  `BLOOD_GROUP` varchar(15),                        
  `SPECIAL_CASE_TYPE` varchar(80),                  
  `HEALTH_INFO_TYPE` varchar(40),                        
      
  `EDUCATION_LEVEL` varchar(40),                  
  `EDUCATION_FIELD` varchar(60),                  
  `SCHOOL_NAME` varchar(100),                              
  `EMPLOYED_IN` varchar(40),                              
  `PROFESSION_TYPE` varchar(60),  
  `ANNUAL_INCOME_VALUE` varchar(60),  
  `WORK_AFTER_MARRIAGE` varchar(40),
  `WORKING_PARTNER` varchar(40),      
  `CITIZEN_COUNTRY` varchar(60),                        
  `RESIDENT_STATUS` varchar(40),                        
      
                    
  `YOURSELF_DESC` varchar(5000),
  `APPROVED_FLAG` char(5) NOT NULL DEFAULT 'false',
  `CREATED_DATE` datetime,
  `MODIFIED_DATE` datetime,

  PRIMARY KEY (`PROFILE_ID`),
  UNIQUE KEY `USER_ID` (`USER_ID`)  
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;


CREATE TABLE IF NOT EXISTS `IBM_USER_GREWUP_COUNTRY` (
`USER_ID` int(25) NOT NULL,
`COUNTRY_NAME` varchar(60),
`CREATED_DATE` datetime,
`MODIFIED_DATE` datetime,

 INDEX(USER_ID)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE IF NOT EXISTS `IBM_USER_HOBBIES` (
`USER_ID` int(25) NOT NULL,
`HOBBIES_TYPE` varchar(60) NOT NULL,
`CREATED_DATE` datetime,
`MODIFIED_DATE` datetime,
 
 INDEX(USER_ID)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
0
 
sameer_goyalCommented:
My solution provided above will still work well even with multiple tables provided you use proper joins.
0
 
slightwv (䄆 Netminder) Commented:
>>this query in no way is equivalent to my query above since you are comparing columns and not parameters

I just hard-coded the values that would have been set by the parameters.

re http:#a38404396

...
where (@column1 is null or column1 = @column1)
and (@column2 is null or column2 = @column2)
...

What if they don't provide a value for the parameter "@column1"?
Would it not be a NULL value?

>>My solution provided above will still work well even with multiple tables provided you use proper joins.

Can you provide a simple working test case to show it?
0
 
johnsoneSenior Oracle DBACommented:
In my post, I specifically address the is null approach and not the nvl approach.  Saying that my comment is not valid by using an entirely different approach as an argument doesn't work.
0
 
sameer_goyalCommented:
slightmv:

"I just hard-coded the values that would have been set by the parameters.

re http:#a38404396"

you are mistaken when you say you have just hard -coded the values instead of parameters. I would suggest you try the query i wrote.

"What if they don't provide a value for the parameter "@column1"?
Would it not be a NULL value?"

@column1 is null

is not same as

col1 is null

It seems you don't understand how OR operator work

Assume that @column1 is null, the condition will break there because it encountered a 'true' and will not even go to "column1 = @column1" and simply will try and look for

and (@column2 is null or column2 = @column2) condition

Meaning, that since the user did not provided any  value for @column1, so that should not be considered in the search

Here's a full blown example to help you understand

Create table dbo.Employees(EmpId int, EmpName varchar(50) null, Dept Int null, Salary money null, EmailId varchar(200) null)

INSERT INTO dbo.Employees VALUES(1, 'XYZ', 1, 100, 'XYZ@myemail.com')
INSERT INTO dbo.Employees VALUES(1, 'ABC', 1, 5000, 'ABC@myemail.com')
INSERT INTO dbo.Employees VALUES(1, null, 1, 1100, 'someemail@myemail.com')
INSERT INTO dbo.Employees VALUES(1, 'PQR', 1, null, 'PQR@myemail.com')

I created a table above and some test data is inserted in it


Now, here I am simulating what I have been posting here

Declare @empId int
Declare @EmpName varchar(50)
Declare @Dept Int
Declare @Salary money

Set @empId = 1
Set @EmpName = null
Set @Salary = null
Set @Dept = 1


Select * from dbo.Employees WHERE
(@empId is null or empId = @empId)
And (@EmpName is null or empName = @empName)
And (@Dept is null or Dept = @Dept)
And (@Salary is null or salary = @salary)


Let me know what do you think will be result of the query above.  

I am in any case attaching the result that it actually yields to - DynamicQueryDemo.png

Further, consider the following

Declare @empId int
Declare @EmpName varchar(50)
Declare @Dept Int
Declare @Salary money

Set @empId = null
Set @EmpName = 'XYZ'
Set @Salary = null
Set @Dept = null

Select * from dbo.Employees WHERE
(@empId is null or empId = @empId)
And (@EmpName is null or empName = @empName)
And (@Dept is null or Dept = @Dept)
And (@Salary is null or salary = @salary)

Any guesses what should be the output of the following?

Refer to attached image - DynamicQueryDemo2.png

And now the query that you suggesting

Select * from dbo.Employees WHERE
(empId is null or empId = '')
And (EmpName is null or empName = 'XYZ')

Well, it leads to no result. Attached image, DynamicQueryDemo3.png

I hope I was able to make a point here.
DynamicQueryDemo.png
DynamicQueryDemo2.png
DynamicQueryDemo3.png
0
 
slightwv (䄆 Netminder) Commented:
>>I would suggest you try the query i wrote.

The issue was in my porting of it from SQL Server to Oracle.  
I only replaced the right side of the variables and was using the left side as the column.

Based on your latest test case, I see the mistake I made and what it is doing.  I agree it will work.  Apologies.
0
 
Sanjeev LabhCommented:
Hi,

There are a number a ways in which you can achieve solve your problems.

1. Write single query.
2. Write plsql block
3. Write Dynamic query.

Single query approach - Here you would be writing a single query including all the search criteria within the where clause. To achieve this you will have to handle the nulls of the parameters whose values has not been passed.

e.g. emp.empid = nvl(v_empid, emp.empid)

means if empid is not passed and v_empid is null then the query forms a self join on the column being used. This would not let the query fail.

2. You can use plsql block to form a dynamic query. With various if's in place first build your query string so that at run time only those conditions get included which have been passed, other wise it won't be included.

This would help to remove any heavy self join conditions as well as null conditions. This would give you a better performance improvement.

However, this has an overhead of parsing the query string every time as it treats it as literals. To remove this problem and remove the hard parses every time you can use bind parameters for it. Using SYS_CONTEXT would be the best approach to introduce bind parameters.

For using dynamic query you can also make use of the in built package DBMS_SQL. So, try any which suits you best. In my experience using dynamic query with SYS_CONTEXT as
 bind parameters gives the best performance. Only overhead is the initial writing is little pains taking but long term beneficial.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now