Use like operator in Crystal Report record selection formula

Question

Hi I am using Crystal Report 2008 to create a report. I need to convert the following oracle SQL query to Crystal Report record selection formula.

Oracle Query is :

AND 
(
  UPPER(920_SEARCH_REPORT.HANDICAP_TYPE) LIKE UPPER(:HANDICAP_TYPE)
  OR UPPER(920_SEARCH_REPORT.SKSKODENR) LIKE UPPER(:SKSKODENR)
)

I have convert this to formula like following way :

and 
(
    isnull({?Hancicap_Type}) = true 
        or ((UpperCase({?Hancicap_Type}) Like ("*"&UpperCase({920_SEARCH_REPORT.HANDICAP_TYPE})&"*"))    
        or (UpperCase({?SKSKODENR}) Like ("*"&UpperCase({920_SEARCH_REPORT.SKSKODENR})&"*")))
)

But data is not showing when crystal report executes. But the sql query returns one record.

Can anyone help me to solve the problem.

It checks whether the parameter value is null or not. IsNull function is not necessary to use. we can do like this way: {?Hancicap_Type} = "" Or .... — mnu-nasir
That is the point you are missing.... im this case use if function then i am sure your formula will work — Siva
can you give a idea. a sample code. it will help me. my other condition is working nicely where multiple or operator is not used. — mnu-nasir
I had the same general problem, but I was using normal SQL syntax (%) instead of Crystal syntax (*). — Brad Rhoads

craig craig · Accepted Answer · 2014-10-20T11:43:28

You need to use the proper syntax for parameters: {?parameter_name}.

...
AND 
(
  ( UPPER(920_SEARCH_REPORT.HANDICAP_TYPE) LIKE "*" + UPPERCASE({?HANDICAP_TYPE}) + "*")
  OR 
  ( UPPER(920_SEARCH_REPORT.SKSKODENR) LIKE "*" + UPPERCASE({?SKSKODENR}) + "*")
)

Use like operator in Crystal Report record selection formula

1 Answers