Solved

Crystall_WildcardSearches

Posted on 2011-02-18
7
503 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
create a nested synonym 4 28
oracle numeric condition check 4 27
minium over 4 numeric columns for each row in oracle 2 27
Oracle Nested table uses ? 2 31
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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

856 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