Skip nulls in database

[jollyreaper]

Crystal Reports / Server XI
Windows 2003
Topspeed ODBC

I have a query that seems to be choking on nulls. Some of the date values in the database are null. Now SQL nulls are not the same as '' so saying

where startdate<>''

wouldn't work, more's the pity. When I remove all references to date, the query runs fine. I would have thought that the where clause specifying the dates would take care of the nulls but I guess my crummy odbc driver is choking when evaluating nulls.


**uncommented query, commented one below**
SELECT "CVENDOR"."NAME", "CTASK"."STARTDATE", "CCLIENT"."ADKEY", "CCLIENT"."JOBADDR", "CTASK"."DESCRIPTION", "CDVLP"."LONGNAME", "CTASK"."ENDDATE", "CTASK"."STATUS", "CCLIENT"."SCHACTIVE"
FROM "CCLIENT" "CCLIENT", "CDVLP" "CDVLP", "CTASK" "CTASK", "CVENDOR" "CVENDOR"
WHERE ("CCLIENT"."IDCDVLP"="CDVLP"."IDCDVLP") AND ("CCLIENT"."IDCCLIENT"="CTASK"."IDCCLIENT") AND ("CTASK"."IDCVENDOR"="CVENDOR"."IDCVENDOR") AND "CDVLP"."LONGNAME"='Sail Harbour' AND "CTASK"."STATUS"<>'D' AND "CCLIENT"."SCHACTIVE"=1 AND ("CTASK"."STARTDATE">={d '2005-09-12'} AND "CTASK"."STARTDATE"<={d '2005-09-16'})
ORDER BY "CVENDOR"."NAME"





**commented query**
SELECT "CVENDOR"."NAME", "CTASK"."STARTDATE", "CCLIENT"."ADKEY", "CCLIENT"."JOBADDR", "CTASK"."DESCRIPTION", "CDVLP"."LONGNAME", "CTASK"."ENDDATE", "CTASK"."STATUS", "CCLIENT"."SCHACTIVE"
******************all of the above is just a bunch of selections, no biggie********
FROM "CCLIENT" "CCLIENT", "CDVLP" "CDVLP", "CTASK" "CTASK", "CVENDOR" "CVENDOR"

***********tables above, naturally*******************

WHERE ("CCLIENT"."IDCDVLP"="CDVLP"."IDCDVLP") AND

****linking client table to community table (i.e. the development*****

("CCLIENT"."IDCCLIENT"="CTASK"."IDCCLIENT") AND

****linking client table to task table*****

("CTASK"."IDCVENDOR"="CVENDOR"."IDCVENDOR")

****linking task to the vendor who performs the task*****

AND "CDVLP"."LONGNAME"='Sail Harbour'

***allows us to filter the development by its name rather than by its ID#****

AND "CTASK"."STATUS"<>'D'

***If the task falls within the date range but is d-for-done, do not display***

AND "CCLIENT"."SCHACTIVE"=1

***skip showing the client if the schedule is not active***

AND ("CTASK"."STARTDATE">={d '2005-09-12'} AND "CTASK"."STARTDATE"<={d '2005-09-16'})

***the timeframe the query is supposed to return for***

ORDER BY "CVENDOR"."NAME"


Here is the formula that is used to determine the timeframe.

{CTASK.STARTDATE} in currentdate-dayofweek(currentdate)+2 to currentdate-dayofweek(currentdate)+ 6

 

[dgillz]

use a formula:

Not Isnull({YourField})

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[jollyreaper]

Whoops, forgot to ask my specific question. How do I work around this? My guess is adding another line to the date formula about where not null?

 

[jollyreaper]

Wow, that was quick! So for that formula:

{CTASK.STARTDATE} in currentdate-dayofweek(currentdate)+2 to currentdate-dayofweek(currentdate)+ 6 && Not Isnull({CTASK.STARTDATE})

Something like that?

 

[jollyreaper]

Hmm. I tried that out and I must be missing something incredibly obvious.

Here's how the where's are looking now.

WHERE ("CCLIENT"."IDCDVLP"="CDVLP"."IDCDVLP") AND ("CCLIENT"."IDCCLIENT"="CTASK"."IDCCLIENT") AND ("CTASK"."IDCVENDOR"="CVENDOR"."IDCVENDOR") AND "CDVLP"."LONGNAME"='Sail Harbour' AND "CTASK"."STATUS"<>'D' AND "CCLIENT"."SCHACTIVE"=1 AND "CTASK"."STARTDATE" IS NOT NULL AND ("CTASK"."STARTDATE">={d '2005-09-12'} AND "CTASK"."STARTDATE"<={d '2005-09-16'})
ORDER BY "CVENDOR"."NAME"

That was created from this formula in selection expert:

Not Isnull({CTASK.STARTDATE}) and
{CTASK.STARTDATE} in currentdate-dayofweek(currentdate)+2 to currentdate-dayofweek(currentdate)+ 6

Running it generated this:
SQLExecDirect Successful

[Row, NAME, STARTDATE, ADKEY, JOBADDR, DESCRIPTION, LONGNAME, ENDDATE, STATUS, SCHACTIVE]
[SoftVelocity Inc.][TopSpeed ODBC Driver]No data found


(using SQL Connectivity Test from Business Objects.)

 

[synapsevampire]

Since you have:

("CTASK"."STARTDATE">={d '2005-09-12'} AND "CTASK"."STARTDATE"<={d '2005-09-16'})

Eliminating nulls shouldn't be required anyway.

Might be an issue with the TopSpeed ODBC driver.

-k

 

[jollyreaper]

Turns out it is a driver issue. Crystal isn't even involved here. Using that simple query testing tool they provide, simple reads from the database choke whenever there's a null involved. Complete stupidity on the ODBC driver writer's end but that doesn't do me any good to discover it. Argh.

 

[synapsevampire]

You might try a Crystal SQL Expression on the date field to change any null values to a default value, and then test against the SQL Expression for the default value vs. a real value. Or even convert the date value to a string in a SQL Expression (or as a last ditch effort, a Crystal formula), and then create a formula which uses a CDATE function to convert it back and then use your criteria.

The latter has the disadvantage of performing everything on the Crystal side.

-k

 

[jollyreaper]

Right, I understand what you're suggesting. In fact, we tried that on the phone. (was talking with Crystal support.) The problem is entirely on the driver's end. Using that test program, I can do the following:
SELECT "CCLIENT"."IDCCLIENT", "CCLIENT"."LASTNAME", "CCLIENT"."FIRSTNAME"
FROM "CCLIENT" "CCLIENT", "CTASK" "CTASK"
WHERE ("CCLIENT"."IDCCLIENT"="CTASK"."IDCCLIENT")

That gives me around 5000 records and returns in a flash. If I include one stupid field with nulls it changes everything. (startdate has the nulls below.)
SELECT "CCLIENT"."IDCCLIENT", "CCLIENT"."LASTNAME", "CCLIENT"."FIRSTNAME", "CTASK"."STARTDATE"
FROM "CCLIENT" "CCLIENT", "CTASK" "CTASK"
WHERE ("CCLIENT"."IDCCLIENT"="CTASK"."IDCCLIENT")

This now takes twenty minutes to run. This is the case even when I do it outside of Crystal. So what that means is that if I tried to use Crystal to suppress the nulls, it would still have to read those records first to find out they're nulls which means we're at the 20 minute mark again. ARGH! The ODBC people promise a new version of the driver will be out soon.

 

[synapsevampire]

What happens if you use a CAST oir CONVERT against the null field, same thing?

If not, then you have a solution. Otherwise you can do as my last two suggestions point out, which will be horrid performance, but it should return the proper data.

-k

 

[jollyreaper]

I'm trying that out. Am I mungling the SQL?

SELECT "CCLIENT"."IDCCLIENT", "CCLIENT"."LASTNAME", "CCLIENT"."FIRSTNAME", "CTASK"."STARTDATE"
FROM "CCLIENT" "CCLIENT", "CTASK" "CTASK"
WHERE ("CCLIENT"."IDCCLIENT"="CTASK"."IDCCLIENT")
AND
CAST("CTASK"."STARTDATE" AS char(20))

It choked.

SQLExecDirect Failed
Error Messages:
[SoftVelocity Inc.][TopSpeed ODBC Driver]Expected lexical element not found: =, <>, <, <=, >, or >=

 

[synapsevampire]

No, it's not in the record selection formula.

Don't pull the date field at all in the report, as you've already proven, the ODBC driver cannot handle this.

Anyway, test in your query executiong program, not Crystal, and you'll need to know the syntax in whatever database you're using to convert the field to a varchar.

-k

 

[jollyreaper]

I saw the first example I used wasn't what I needed. I tried it a different way, still no dice. This way it says "FROM is expected."
SELECT "CCLIENT"."IDCCLIENT", "CCLIENT"."LASTNAME", "CCLIENT"."FIRSTNAME", CAST("CTASK"."STARTDATE" AS char(20))
FROM "CCLIENT" "CCLIENT", "CTASK" "CTASK"
WHERE ("CCLIENT"."IDCCLIENT"="CTASK"."IDCCLIENT")

 

[jollyreaper]

Anyway, test in your query executiong program, not Crystal, and you'll need to know the syntax in whatever database you're using to convert the field to a varchar.



Ok, was hoping the CAST was traditional SQL. These guys only support it halfway.

 

[synapsevampire]

Yeah, they sound half baked, sorry.

Perhaps they support a different syntax, check with them.

-k

 

[jollyreaper]

Half-baked is giving them entirely too much credit.