How to test for a null value in a date column

[Jagdip Singh Ajimal]

I am creating the following view

CREATE OR REPLACE VIEW AllMedics AS
SELECT * FROM Medic m, Employee e
WHERE e.EmployeeID = m.MedicID AND
e.DateFinished = NULL;

But all I get is "no rows selected". How do I test a date column to
see if it is null?

NB The DateDinished above means the date that the employee was
fired/resigned.

Any help would be greatly appriciated.

Jagdip Singh Ajimal

[Ron]

"e.DateFinished = NULL" - did you try "e.DateFinished is null"

Regards,

Ron
DBA Infopower
http://www.dbainfopower.com
Standard disclaimer:
http://www.dbainfopower.com/dbaip_ad...isclaimer.html


"Jagdip Singh Ajimal" <js*****@hotmail.com> wrote in message
news:c8**************************@posting.google.c om...

I am creating the following view

CREATE OR REPLACE VIEW AllMedics AS
SELECT * FROM Medic m, Employee e
WHERE e.EmployeeID = m.MedicID AND
e.DateFinished = NULL;

But all I get is "no rows selected". How do I test a date column to
see if it is null?

NB The DateDinished above means the date that the employee was
fired/resigned.

Any help would be greatly appriciated.

Jagdip Singh Ajimal

[Dave]

js*****@hotmail.com (Jagdip Singh Ajimal) wrote in message news:<c8**************************@posting.google. com>...

I am creating the following view

CREATE OR REPLACE VIEW AllMedics AS
SELECT * FROM Medic m, Employee e
WHERE e.EmployeeID = m.MedicID AND
e.DateFinished = NULL;

But all I get is "no rows selected". How do I test a date column to
see if it is null?

NB The DateDinished above means the date that the employee was
fired/resigned.

Any help would be greatly appriciated.

Jagdip Singh Ajimal

e.DateFinished IS NULL;

Dave

[Michael Draves]

js*****@hotmail.com (Jagdip Singh Ajimal) wrote in message news:<c8**************************@posting.google. com>...

I am creating the following view

CREATE OR REPLACE VIEW AllMedics AS
SELECT * FROM Medic m, Employee e
WHERE e.EmployeeID = m.MedicID AND
e.DateFinished = NULL;

But all I get is "no rows selected". How do I test a date column to
see if it is null?

NB The DateDinished above means the date that the employee was
fired/resigned.

Any help would be greatly appriciated.

Jagdip Singh Ajimal

Have you tried "is null" instead of "= null " or "is not null" instead of "<> null"?

[Hans Forbrich]

Jagdip Singh Ajimal wrote:

I am creating the following view

CREATE OR REPLACE VIEW AllMedics AS
SELECT * FROM Medic m, Employee e
WHERE e.EmployeeID = m.MedicID AND
e.DateFinished = NULL;

But all I get is "no rows selected". How do I test a date column to
see if it is null?

NB The DateDinished above means the date that the employee was
fired/resigned.

Any help would be greatly appriciated.

Jagdip Singh Ajimal

Consider this: what is the definition of 'NULL' and how can anything
'equate' to that 'value'?

You want to use the 'IS NULL', not the '= NULL' construct.

(This is basic SQL - please consider reviewing your/an 'introduction to
SQL' documentation or book. Things like this are not intuitive to the
uninitiated and will lead to frustration.)
/Hans

[Jagdip Singh Ajimal]

The solution turned out to be simple. All I did was change the code to
the following:

CREATE OR REPLACE VIEW AllMedics AS
SELECT * FROM Medic m, Employee e
WHERE e.EmployeeID = m.MedicID AND
* e.DateFinished IS NULL;
js*****@hotmail.com (Jagdip Singh Ajimal) wrote in message news:<c8**************************@posting.google. com>...

I am creating the following view

CREATE OR REPLACE VIEW AllMedics AS
SELECT * FROM Medic m, Employee e
WHERE e.EmployeeID = m.MedicID AND
e.DateFinished = NULL;

But all I get is "no rows selected". How do I test a date column to
see if it is null?

NB The DateDinished above means the date that the employee was
fired/resigned.

Any help would be greatly appriciated.

Jagdip Singh Ajimal