Not isnull not working for a alpha field

[gooseman]

I'm tyring to get a report for only the items that contain data is a specific field. The report consist of just one table with two fields. The first field (doc-num) is alpha 10 and the second field is alpha 15 (activity). I only want to print records that have an activity. The table has over a million records and no matter what I try I always get all the records. I have tried in the record selection -not isnull({mmdist.activity}) but I still get all the records. The database is oracle 8.1.6 and crystal reports 8.0.1.

 

[synapsevampire]

Make sure that the SQL (Use Database->Show SQL Query) is getting pass through to the database, and it may be that the field you're checking for null in isn't null, it is "", so you might try checking for both conditions.

({doc-num} <> &quot;&quot; and not(isnull({doc-num})))

and

(activity) <> &quot;&quot; and not(isnull((activity)))

If this doesn't resolve, please post your Record Selection Criteria and what is being generated in the SQL.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

sometimes... instead of a Null record the user enters blanks instead...thus the record is now Not Null

Since it appears that you are only interested in the activity field then your record select formula could be

(not isnull({Table.activity}) and
length(trim({Table.activity})) <> 0 );

this won't be passed to the server but should work reasonably fast Jim Broadbent

 

[gooseman]

Ngolem, your suggestion worked but it took a long time. I'm confused on the sql show query because it doesn't inlcude the record selection. It shows:

SELECT
MMDIST.&quot;DOC_NBR&quot;, MMDIST.&quot;ACTIVITY&quot;
FROM
&quot;LAWSON&quot;.&quot;MMDIST&quot; MMDIST

The record selection is happening after it reads all the records so I'm going thru 2 million records. Why doesn't the sql statement include the your not isnull statement?

 

[kenhamady]

I think you can use:

(activity) <> &quot;&quot;

CR usually skips the nulls with ANY criteria is used with a field. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Ngolem]

The reason it isn't in your SQL is because this formula is not pushed down to the server.

I am not certain if there are situations were there are activity entries that are just blank spaces or if they are all nulls


try this for a record select by itself

not isnull({Table.activity})

this should be pushed down to the server...if you get a different record set then there are situations where there are &quot;blankspaces&quot; entered and further processing would be necessary

This can be done in a separate formula.....for a second pass at the data....note the record count in both cases



Jim Broadbent

 

[gooseman]

Thank your for your help. I believe I have figured it out. The sql show query that seems to work is

SELECT
MMDIST.&quot;DOC_NBR&quot;, MMDIST.&quot;ACTIVITY&quot;
FROM
&quot;LAWSON&quot;.&quot;MMDIST&quot; MMDIST
WHERE
LENGTH(TRIM(MMDIST.&quot;ACTIVITY&quot) <> '0'

I was able to get all the records that I had gotten with the record selection(ngolem) of (not isnull({mmdist.activity}) and length(trim({mmdist.activity})) <> 0); The method above gave me the records in a matter of seconds.