?
Solved

How to use like opertaor in Crystal Report using Paramtere

Posted on 2012-09-20
12
Medium Priority
?
537 Views
Last Modified: 2012-10-31
Hi
i have table called Students, i want ti retervie the student name having letter "S" in there names
Select  * from Students
where name  Like '%S%'

Using Parameter my query is like these , i have tried few ways still getting error : Database vendor code 911:

Please suggest :

Select  * from Students
where name  Like '*' & {?name} &'*'

Select  * from Students
where name  Like  {?name} &'*'
Select  * from Students
where name  Like '*' + {?name}  +'*'
Database is oracle 9i and crystal report version Xi

Error is failed to Retervie database error
.Please suggest how to resolve the error.


'
0
Comment
Question by:nrajasekhar7
  • 3
  • 2
  • 2
  • +3
12 Comments
 
LVL 13

Expert Comment

by:jonnidip
ID: 38417121
I would use a '%' instead of '*':

Select  * from Students
where name  Like '%' + {?name}  +'%'

Open in new window

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38417552
To add: Oracle is case sensitive.  If you need case insensitive:

where lower(name)  Like lower('%' + {?name}  +'%)'
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38417596
Where are you putting the SQL?

Does a simple unfiltered query work?
SELECT * FROM Students

Does a filter like this work
SELECT * FROM Students
WHERE Name = "Smith"

mlmcc
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 14

Expert Comment

by:LinInDenver
ID: 38418185
If this is inside your select expert, you do not need to include  SELECT * FROM

From the Report Menu, go to Selection Formulas, and then Record

 {Students.name} like '*' & {?name} & '*'

if you think your database is case sensitive, then
lowercase({Students.name}) like '*' & lowercase({?name}) & '*'
0
 
LVL 14

Expert Comment

by:LinInDenver
ID: 38418229
If it is in a Command statement (and the parameter is also inside the Command), this should work, or at least it did for me:

select * from Students
where Students.name like '%{?name}%'
0
 

Author Comment

by:nrajasekhar7
ID: 38420898
I have the syntax in command query inside the report still getting the same error
i have tried all the experts suggestion still getting the error:
i am writting my query in crystal reports :command :
when i ran my query at sql its working fine :


Database vendor 911
and failed to reterive from the database,Please i need urgent ..

Thanks
0
 
LVL 13

Expert Comment

by:jonnidip
ID: 38420900
911 error refers to a wrong char.
I feel that you are passing something wrong to Oracle.
Could you open a profiler to see what is going to your db?
0
 
LVL 35

Accepted Solution

by:
James0628 earned 1500 total points
ID: 38421003
I just wanted to make sure.  Did you try Lin's suggestion from post 38418229, with '%{?name}%' ?  If this is in a Command, I think it should be something like that, instead of something like '%' + {?name} + '%'.  CR just replaces the parameter with its value, so '%{?name}%' should give you something like '%John Smith%', while '%' + {?name} + '%' would give you something like '%' + John Smith + '%', which is wrong.

 If '%{?name}%' is not working then I'm not sure what to tell you, but, FWIW, I do believe that that is the correct form.

 James
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 38421753
What do you have as the command?

mlmcc
0
 

Author Comment

by:nrajasekhar7
ID: 38425714
when i hard in the command like these its working
select * from student
where doj  >= {?date1} and
doj < {?date2} and name like '%S%'
these query is working but whem i reaplace
with parameter like  these
select * from student
where doj  >= {?date1} and
doj < {?date2} and name like '%{?name}%'
then iam  getting the error.when i run the report .
i tried all the above suggestions in replacing with  '%{?name}%'.

oci package error i am getting

Please suggest . any alter nate way t o send the name parameter to get the reult i want.
i want to send the name as  paremeter i want to  see the
letter in the names, the parameter i pass.
please  suugest asasp its urrgent !!!
0
 
LVL 35

Expert Comment

by:James0628
ID: 38426758
The Command in the report works when you include the date1 and date2 parameters, without the name parameter?  I just wanted to make sure.  That would indicate that parameters are working in general and it's just a problem with that specific parameter.

 It might be interesting to see if using '{?name}' in the Command works without the "%"s.  You could try just entering anything for the parameter.  You may not get any records, but the idea is just to see if you still get the error.  If you don't get the error, then you could try entering the "%"s in the parameter.  For example, if you want any name with an "S" in it you would enter %S% for the parameter.  See if any of that works.

 Also, I haven't used Oracle, so I am assuming that it uses single-quotes for string literals.

 James
0
 
LVL 35

Expert Comment

by:James0628
ID: 38551831
What part of that post was the solution?  All I really did was say that '%{?name}%' seemed correct, but LinInDenver had suggested that first, and then you said that it didn't work.

 If it really did work, then LinInDenver should get the points.

 James
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month14 days, 2 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question