query problem

hi i have the following query and 2 problems with it.
EventView is a view.
1. if pregnancy.dischargedate is null i want to display EventValue.
(Select ISNULL(pregnancy.dischargedate, EventValue)
works fine when discharge date is null it takes the value of dischargedate1.
but the problem is if discharge date has a value, it still displays null.
2. and i want to use dischargedate1 Between '1.12.1990' AND '12.12.2004'
it says invalid columnname dischargedate1.
select
Pregnancy.DischargeDate,
(Select ISNULL(pregnancy.dischargedate, EventValue)
From EventView
--I have a lot of where clauses and processing here.
))AS DischargeDate1
From Patient INNER JOIN Pregnancy ON Patient.Patientid = Pregnancy.Patientid
INNER JOIN Baby ON Baby.Pregnancyno = Pregnancy.Pregnancyno
where DischargeDate1 Between '1.1.1990' AND '1.1.2004'
thnx.Frazer,
if you do Select ISNULL(pregnancy.dischargedate,
EventValue), pregnancy.dischargedate, EventValue you
should see that when dischargedate is null then the
EventValue is returned, otherwise the dischargedate is
returned. Is this what you require? Your message is a
little confusing: 'it takes the value of dischargedate1' -
which is the expression alias.
For your second point, you can't refer to the expression
alias dischargedate1 in a where clause - you'll need to
repeat the expression instead.
HTH,
Paul Ibison