Odd Database Inquiry. 1

[MrSki]

We are using ColdFusion MX and 5 with SQL 2000.

I can run this query IN the Enterprise Manager for SQL and it returns 2 records, which is as it should. But when I run it on a ColdFusion page, it only returns one item:

SELECT *
FROM vwHelpRequests
WHERE assignee = 12
and timeRequested < {d '2004-05-08'}
and timeRequested >= {d '2004-05-01'}
and (timeClosed >= {d '2004-05-08'}
or timeClosed = '')

One record has a value in the timeClosed field and the other has nothing in it. The ColdFusion page is not finding the record with the empty value.

Anyone have an idea as to why?



Mitch Duszynski
Web Developer
Human Kinetics
PO Box 5076, Champaign, IL 61825
Tel: 217-351-5076 x2474 | Fax: 217-351-2674
mitchd@hkusa.com |

http://www.humankinetics.com

 

[NorthStarDA]

maybe the SQL Server driver is not handling NULL values in the same way as entering a query in the enterprise manager.

just a guess

SELECT *
FROM vwHelpRequests
WHERE assignee = 12
  and timeRequested < {d '2004-05-08'}
  and timeRequested >= {d '2004-05-01'}
  and (timeClosed >= {d '2004-05-08'}
    or timeClosed IS NULL)

 

[MrSki]

IS NULL did the trick! Thanks!

Mitch Duszynski
Web Developer
Human Kinetics
PO Box 5076, Champaign, IL 61825
Tel: 217-351-5076 x2474 | Fax: 217-351-2674
mitchd@hkusa.com |

http://www.humankinetics.com