Re: Null Date Problem

[Rachael Beatty <Rachael_Beatty@HILTON.COM>]

Try:
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

Hope that helps

Rachael

>  -----Original Message-----
> From:   "Brian Pelton" <bpelton@COMMUNITYMEDICAL.ORG>@INTERNET
> Sent:   Wednesday, July 11, 2001 9:45 AM
> To:     SQR-USERS@list.iex.net
> 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
>

(See attached file: C.DTF)

C.DTF