Grid search not function if SQL statement already have Where Condition

Applications Grid / Reports
#1

In database users,

userid,username,level
admin,Administrator,1
super,Supervisor,2
manager,Manager,3
staff01,Staff 01,4
staff02,Staff 02,4
user01,User 01,5
user02,User 02,5
user03,User 03,5

Case 1,
Grid SQL statment = “Select userid, username, level from users order by userid”;
Grid Search ‘username’ contains ‘01’
and results shown
staff02,Staff 02,4
user02,User 02,5

just like expected.

Case 3,
Grid SQL statment = “Select userid, username, level from users where level = ‘5’ order by userid”;
Grid Search ‘username’ contains ‘02’
and results shown all records.
admin,Administrator,1
super,Supervisor,2
manager,Manager,3
staff01,Staff 01,4
staff02,Staff 02,4
user01,User 01,5
user02,User 02,5
user03,User 03,5

and not like expected result
user02,User 02,5

Seems the filter not working

Any idea on this issue?

Regards,
CK

#2

Re: Grid search not function if SQL statement already have Where Condition

Go to Application/Settings in your grid and change Debug Mode to yes and see what SC5 is creating for the SQL statement.

Regards,
Scott.

#3

Re: Grid search not function if SQL statement already have Where Condition

Also, are you hard coding the SQL statement: where level = ‘5’

You may want to have a look at sc_where_current,sc_where_filter,sc_where_orig along with sc_select_where(add);

Regards,
Scott.

#4

Re: Grid search not function if SQL statement already have Where Condition

Found the problem,

Existing SQL
select * from users where condition01 or condition02;
so, when apply the search / filter
it become
select * from users where condition01 or condition02 and searchcond;
the syntax become invalid

Solved
select * from users where (condition01 or condition02);
so, when apply the search / filter
it become
select * from users where (condition01 or condition02) and searchcond;
the syntax become VALID

To group the conditions in () is very important here.

Thanks scott for your information.

Regards
CK