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]

Re: How to test for a null value in a date column


"e.DateFini shed = NULL" - did you try "e.DateFini shed is null"

Regards,

Ron
DBA Infopower

http://www.dbainfopower.com

Standard disclaimer:

http://www.dbainfopower.com/dbaip_advice_disclaimer.html

"Jagdip Singh Ajimal" <jsa1981@hotmai l.comwrote in message
news:c84eb1b0.0 402120435.21773 0f4@posting.goo gle.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

[Dave]

Re: How to test for a null value in a date column

jsa1981@hotmail .com (Jagdip Singh Ajimal) wrote in message news:<c84eb1b0. 0402120435.2177 30f4@posting.go ogle.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]

Re: How to test for a null value in a date column

jsa1981@hotmail .com (Jagdip Singh Ajimal) wrote in message news:<c84eb1b0. 0402120435.2177 30f4@posting.go ogle.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]

Re: How to test for a null value in a date column

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]

Re: How to test for a null value in a date column

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;


jsa1981@hotmail .com (Jagdip Singh Ajimal) wrote in message news:<c84eb1b0. 0402120435.2177 30f4@posting.go ogle.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