Re: Null Date Problem

["Johnson, Dan" <Dan_Johnson@WRIGHTEXPRESS.COM>]

Assuming that your &a.[variable] variables have the correct information you
may want to try this:

Begin-Procedure Update-Descr
    display '    Attempting to update description...'
Begin-SQL
update ps_accomplishments
set descr              = $TrueDescr
where emplid           = &a.emplid
  and accomplishment   = &a.accomplishment
  and org              = &a.org
  and descr            = &a.descr
  (and dt_issued        = &a.dt_issued             !PROBLEM WITH THIS MATCH
   or dt_issued       is null)                     !PROBLEM WITH THIS MATCH
  and major_code       = &a.major_code
End-SQL
End-Procedure

Note the parenthisis.

Good luck,
Dan

-----Original Message-----
From: Brian Pelton
Sent: Wednesday, July 11, 2001 12:45 PM
Subject: Null Date Problem


I'm running a short update-SQL program to push in the description field on
the
ps_accomplishments table in PeopleSoft HRMS.

I'm sure this is a novice question, but I'm stumped.
The problem I'm having is that the match for the date issued field
(dt_issued)
isn't working:

Begin-Procedure Update-Descr
    display '    Attempting to update description...'
Begin-SQL
update ps_accomplishments
set descr              = $TrueDescr
where emplid           = &a.emplid
  and accomplishment   = &a.accomplishment
  and org              = &a.org
  and descr            = &a.descr
  and dt_issued        = &a.dt_issued             !PROBLEM WITH THIS MATCH
   or dt_issued       is null                     !PROBLEM WITH THIS MATCH
  and major_code       = &a.major_code
End-SQL
End-Procedure

The update procedure runs fine if I comment out both of the dt_issued
criterias,
but I'd rather not do this.
The majority of the rows have null values for their dt_issued field, but
some
don't.
When I try a simple select statement in SQLPlus it selects all the rows it
should:

select * from ps_accomplishments
where dt_issued is null

But the SQR program doesn't work.  I have no idea why.  Any thoughts??

PeopleSoft HRMS 7.51
Oracle  8.0.5.0.0
SQRW 4.3.4

Brian Pelton
Financial Programmer / Analyst
Community Medical Centers