null date field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am running the query below. I am trying to get cases with an end date
greater than 2/28 or null....and then there is another field of case close
which has to be greater than 2/28 or null as well. I can't seem to get the
nulls or blanks and I don't know if the database does not like them...I can
see the blank fields in the raw data...I don't see a dummy value. thanks

SELECT
MDR.CASE.CASE_SID,
MDR.CASE_MV.CASE_CLOSURE_DATE,
MDR.CASE_MV.CASE_CLOSURE_RSN_DESC,
MDR.CASE_MV.CASE_END_DATE,
MDR.CASE_MV.CASE_TX_SETTING_DESC,
MDR.CASE_MV.OWNER_USER_NAME
FROM
MDR.CASE,
MDR.CASE_MV
WHERE
( MDR.CASE_MV.CASE_PID=MDR.CASE.CASE_PID )
AND ( MDR.CASE.ACTIVE_FLAG (+) = 1 )
AND (
( MDR.CASE_MV.CASE_ACTIVE_FLAG = 1 )
AND MDR.CASE_MV.CASE_TX_SETTING_DESC IN ('CM- Medical', 'Non Complex CM')
AND MDR.CASE_MV.CASE_CLOSURE_RSN_DESC NOT IN ('BH - RAP Successful',
'CM Not Accepted - Member expir', 'CM Not Accepted - Other', 'CM Not
Accpt-Criteria Not Met', 'CM Not Opened - Other', 'CM Not Opened-Member
Refuse CM', 'CM Not Opened-No Memb Corresp', 'CM Not Opnd-Memb Pract Decl
BH', 'Duplicate Case', 'Entire Case Denied', 'Not a Covered Benefit',
'Patient Never Admitted/Treated')
AND MDR.CASE_MV.OWNER_USER_NAME IN ('Ann Meidenbauer', 'Ann Watson',
'Carol Rath (CC037)', 'Carol Wilson (CC043)', 'Carolyn Eppolito', 'Cathy
Major (MC491)', 'Christine Hotchkis(MC475)', 'Colleen Valenti (CC046)',
'Deanne Tarallo (PM529)', 'Deborah Finn', 'Diane Noel (CC038)', 'Dierdre
Jaquin (MC346)', 'Donna Malysa (PM509)', 'Jean Best', 'Jean Salomone
(MC339)', 'Joan Farnan (CC008)', 'Joanne Richards (CC011)', 'Karol Thomas
(CC013)', 'Katherine Harpst', 'Kathleen Janiszeski', 'Margaret Rotondo
(CC003)', 'Mary Alice Manley (CC506)', 'Mary Meisenzahl (CC047)', 'Mary Reed
(MR301)', 'Melody Antanavige (PM518)', 'Melody Antanavige(PM518)', 'Michele
Jones (PM511)', 'Patricia Woodard (CC040)', 'Roseanne Vermeulen(CC044)',
'Susan Rice', 'Tom Frateschi(SY150)')
AND MDR.CASE_MV.CASE_END_DATE > '28-02-2005 00:00:00'
AND MDR.CASE_MV.CASE_CLOSURE_DATE > '28-02-2005 00:00:00'
)
 
You've described "how" you are trying to do something, but I still don't
have a very clear picture of why or what.

And your use of IN() makes me wonder -- won't you have to be continually
rewriting the SQL statement to handle new codes you wish excluded, and new
folks you wish included? It may be that you could use related queries or
subqueries to accomplish what you want dynamically, instead of hard-coding
it in the SQL statement.

As for nulls, because they are NOT dates, and not text and not ... (you get
the idea), you need to use a slightly different syntax for them... something
like:

WHERE YourDateField IS NULL

I suppose another approach would be to first find all the rows where
YourDateField is < your target date, and then find all the rows not in this
first query -- related queries again!
 
SELECT
MDR.CASE.CASE_SID,
MDR.CASE_MV.CASE_TX_SETTING_DESC
FROM
MDR.CASE,
MDR.CASE_MV
WHERE
hopefully this is clearer.....when I have it like this I cannot get both
greater than or null for close date and end date

( MDR.CASE_MV.CASE_PID=MDR.CASE.CASE_PID )
AND ( MDR.CASE.ACTIVE_FLAG (+) = 1 )
AND (
MDR.CASE_MV.CASE_TX_SETTING_DESC IN ('CM- Medical', 'Non Complex CM')
AND MDR.CASE_MV.CASE_CLOSURE_DATE > '28-02-2005 00:00:00'
AND MDR.CASE_MV.CASE_END_DATE > '28-02-2005 00:00:00'
AND MDR.CASE_MV.CASE_CLOSURE_DATE IS NULL
AND MDR.CASE_MV.CASE_END_DATE IS NULL
)
 
Actually, no. I asked about why -- a SQL statement doesn't tell me why.

Jeff Boyce
<Access MPV>
 
Back
Top