Solved

Crystall_WildcardSearches

Posted on 2011-02-18
7
495 Views
Last Modified: 2012-05-11

1) If I have a simple crystal report based on one oracle table with static parameter (p_stock_no)
and i want to give user an option to use wild card searches.

* = multiple characters
? = Single character


Do i just simply add a selection criteria like this. I tried this and it seems to work. I have not tested it enough though.

{table.stock_no} like {?p_stock_no}

Will this will work for all cases
a) when user select single/multiple values from static list
b) when user enters a value only with wild card "?" or "*"
c) when user enters a combination criteria: values from static list and wild card search.



 I was not sure why i was told before to do something like this. I think this will always do wildcard searches so user cant do exact search if he wants.

{field1| like {?stock_no} + "*"

2) Is it better to have static list display stock number (stock no) only or both (stock no - description)?
which is a common practice.
0
Comment
Question by:sam15
  • 4
  • 2
7 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 34931417
{table.stock_no} like {?p_stock_no}

That should work for all your cases.

2) - I think it really depends on the situation and the data.  If the stock number readily identifies the item then a description isn't really needed or if the users all know the stock numbers but I always like to see the description with it.

mlmcc
0
 

Author Comment

by:sam15
ID: 34933057
I think i do not even need to add "ALL" option to the list. If they enter * in the value it will so the whole list.

Also, if i want to sort the list i would need to create a separate view in the DB and import the list from there or import via text file?

It does not sound also you have any control over the colors or fonts that show up in the static list. is there?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34933535
If you allow wildcards then the * should get all the records.

I don't know about the sorting issue.  WHen I add a static parameter and then append all values they get sorted by default.

If you are referring to the fonts/colors on the default parameter screen, then you are correct there is no way that I have found to make changes to it.

mlmcc
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 26

Assisted Solution

by:Kurt Reinhardt
Kurt Reinhardt earned 250 total points
ID: 34933575
"I think i do not even need to add "ALL" option to the list. If they enter * in the value it will so the whole list."

There are two problems with this approach:

1)  When you use LIKE, it only searches for literal values.  So, technically, if there are any records in your table that have a NULL value for the field you're querying then they won't be included in the results.  In the following sample report (Xtreme Sample Database),  which is parameterized against the PO Number field from the Orders table, you can see how using the "Convert NULL values to default" option dramatically affects the results of the report.  So, if you want to use * to select ALL records AND there's any possibility of there being NULLs in the field you're querying  then you need to set this option, at minimum.

2)  Using LIKE forces a full table scan and is inherently inefficient because the query has to read through every record in the table to see if the condition is met.  For that reason, it's much more efficient to use an explicit ALL value instead of *.  That has it's own set of issues, though.  If you use the syntax like this ({?Param = 'All' or {table.field} = {?Param}) then you'll run into the same limitation as demonstrated in the report above. To get around this, try the following syntax instead:

(
If
  {?Param} <> 'All'
Then
  {table.field} = {?Param}
Else If
  {?Param} = 'All'
Then
  True
)

Please note, you might be able to simplify the syntax - more recent versions of Crystal Reports are more generous with how they use If Thens in the record selection criteria, but that Syntax works in every version I've tested from at least 8

Please reference this presentation for further information: https://www.box.net/shared/ysqmn7eov5

~Kurt
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34933744
You could use a similar formula

If {?Param}= '*' then
   TRUE
Else If InStr({?Param}, '*') > 0 then
   {Table.Field} LIKE {?Param}
Else
    {Table.Field} = {?Param}

That won't work if you allow multiple values

mlmcc
0
 

Author Comment

by:sam15
ID: 34958034
yes, i do support multiple values.

I do not think this formula support wildcard characters if i am correct
(
If
  {?Param} <> 'All'
Then
  {table.field} = {?Param}
Else If
  {?Param} = 'All'
Then
  True
)


I need to test if the *LIKE* will always use a full scan. I think if you search
where stock_no like 'ABC*" it might use an index to find any entries that start with ABC. I do not see why it need to do a full scan.

Right now the report runs ina couple of seconds. I think anything under 5 seconds is considered good performance.
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 34958103
It should for a single value with a wildcard.

Mine will also suport wildcards with a single value.

If {?Param}= '*' then                               ALL VALUES
   TRUE
Else If InStr({?Param}, '*') > 0 then    Single parameter value with a wildcard
   {Table.Field} LIKE {?Param}
Else                                                         Multiple values no wildcards
    {Table.Field} = {?Param}

mlmcc
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
business objects to connect to MSSQL 6 74
how to trim oracle sql sentence in unix 17 53
Crystal reports vb.net 2 41
Oracle - SQL Query with Function 3 36
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

777 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