Pushing Data Down to the Server with (LIKE)

[didonato]

I’m using Crystal Reports 8.0 and Oracle 8.05. My problem is in the record selection formula. (I think ?) How do I retrieve all data (NULL and NOT NULL) using the (like) function with the wildcard (*). I need to use the (Use indexes or server for speed and Convert Null field value to default) options in the report options section. Here is what my selection expert looks like:
{CCALL.SERIAL} like {?serial}
When I enter a value in the parameter field it works fine, when I enter (*) I only get the records that have values. If I unselect the (Use index or server for speed) option, I get all records, but the speed is dramatically increased from 15 seconds to 1 hour. I tried using formulas with the (Use index or server for speed) set on, but again the data is not being pushed down to the server. Here is what my SQL looks like with the index selection on:

SELECT
CCALL."SERIAL"
FROM
"SYSADM"."CCALL" CCALL
WHERE
CCALL."SERIAL" LIKE '%'

Here is what my SQL looks like with the index selection off or using formulas in the selection expert:

SELECT
CCALL."SERIAL"
FROM
"SYSADM"."CCALL" CCALL

Please help!!

 

[JustineB]

Is the "Convert NULL Value to default" checked on the options tab?
If not, try checking this.
Let me know if it works.

Justine.

 

[didonato]

The "Convert NULL Value to default" is already checked.

 

[BFAGG]

Try:

SELECT
CCALL."SERIAL"
FROM
"SYSADM"."CCALL" CCALL
WHERE
(ISNULL(CCALL."SERIAL&quot OR CCALL."SERIAL" LIKE '%')

 

[didonato]

data is not being pushed down to the server. Here is what my SQL looks like:
SELECT
CCALL."SERIAL"
FROM
"SYSADM"."CCALL" CCALL

 

[BFAGG]

If selection expert looks like:
{CCALL.SERIAL} like {?serial}

Go to Report -> selection formula -> record and make it read

ISNULL({CCALL.SERIAL} ) or {CCALL.SERIAL} like {?serial}

 

[didonato]

I typed the following in the selection formula and the SQL is still not pushed down.

ISNULL({CCALL.SERIAL} ) or {CCALL.SERIAL} like {?serial}

SQL READS:

SELECT
CCALL."SERIAL"
FROM
"SYSADM"."CCALL" CCALL

 

[MalcolmW]

Two steps:
1 Record selection formula
{%SQLExprSERIAL} like {?serial}

2 SQL Expression formula SQLExprSERIAL
IIF (CCALL.`SERIAL` Is Null,'',CCALL.`SERIAL`)

The result will be parsed like:
SELECT blah, blah,
IIF (CCALL.`SERIAL` Is Null,'',CCALL.`SERIAL`)
FROM blah...
WHERE
IIF (CCALL.`SERIAL` Is Null,'',CCALL.`SERIAL`) LIKE '%'

Depending on your database driver, you can use a CASE or DECODE statement instead of IIF in the SQL Expression. Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net

 

[didonato]

Thanks for your HELP, It work great!!