Re: Updating with null value

[Vipul Patel <vpatel@GWU.EDU>]

No we are not using peoplesoft. Also insert in same table works where as
update on same table does not work. I.e. column name on both sql is
pebempl_last_work_date
Actually based on other condition we have to do either Insert or update

Thank you

-----Original Message-----
From: Discussion of SQR, Brio Software's database reporting language
[mailto:SQR-USERS@LIST.IEX.NET]On Behalf Of the dragon
Sent: Tuesday, June 04, 2002 5:34 PM
To: SQR-USERS@LIST.IEX.NET
Subject: Re: Updating with null value


try this....

BUT, first of all, any changes you are making are being rolled back from the
database. also, &rec_date will never be null.  In your insert statement, is
the column really called pebempl_last_work_date, or is it called
last_work_date in the pebempl table, which would be designated as
pebempl.last_work_date, with the table part being not need to start with
(side note, if you are using a peoplesoft product, and a column name that
you are using already exists in the metadata, use it, do not create a new
column - it's sloppy database design and makes it difficult to maintain).

Last, but not least, what are you trying to do?

begin-report

do get_info
do update-pebempl
rollback
commit

end-report

begin-procedure get_info

let #sel_pidm = 4769801

begin-select

sysdate                                 &last_paid_date
to_char(sysdate + 1, 'DD-MON-YYYY')     &rec_date
to_char(sysdate + 1, 'YYYYMMDD')        &comp_date

from dual

end-select

show 'Rec Date is = |' &rec_date '|'

end-procedure ! get info

begin-procedure update-pebempl

show 'before insert'
print 'before select'             (+1,  1)

do select-rec

if isnull(&rec_date)
  show 'it is null'    ! this condition will never happen
else
  show 'it is not null'
end-if

do update_first

end-procedure

begin-procedure update_first

begin-sql

update pebempl
set pebempl_last_work_date = to_date(&rec_date,'DD-MON-YYYY'),
    pebempl_activity_date  = sysdate,
    pebempl_empl_status    = 'T',
    pebempl_trea_code      = '06',
    pebempl_term_date      = to_date(&rec_date,'DD-MON-YYYY')
where pebempl_pidm = #sel_pidm

end-sql

show 'After insert'
print 'after select'             (+1,  1)
do select-rec

end-procedure

begin-procedure select-rec

begin-select

pebempl_pidm                      (+1,  1)
pebempl_last_work_date            (  , 10)
pebempl_term_date                 (  , 30)
pebempl_empl_status               (  , 50)
pebempl_activity_date             (  , 55)

   print &rec_date                (  , 75)

from pebempl
where pebempl_pidm = #sel_pidm

end-select

end-procedure

clark 'the dragon' willis
dragon enterprises, consulting services


----Original Message Follows----

Here is my SQR
===================================================
begin-report

begin-select
to_number('4769801') &sel_pidm
sysdate &last_paid_date
from dual
end-select
begin-select
to_char(&last_paid_date+1,'DD-MON-YYYY') &rec_date
to_char(&last_paid_date+1,'YYYYMMDD') &comp_date
from dual
end-select
  let $v_rec_date =  &rec_date
  show $v_rec_date

  do update-pebempl
  rollback
  commit
end-report

begin-procedure update-pebempl

  do select-rec

   if isnull(&rec_date)
         show 'it is null'
   else
      show ' it is not null'
  end-if

begin-sql
update pebempl set
pebempl_last_work_date = to_date(&rec_date,'DD-MON-YYYY')  ,
pebempl_activity_date = sysdate,
pebempl_empl_status = 't',
pebempl_trea_code = '06'
where pebempl_pidm = &sel_pidm
end-sql

begin-sql
update pebempl set
pebempl_term_date = to_date(&rec_date,'DD-MON-YYYY')
where pebempl_pidm = &sel_pidm
end-sql
         do select-rec

end-procedure

begin-procedure select-rec
begin-select
pebempl_last_work_date &test_last_work_date
pebempl_term_date &test_term_date
pebempl_empl_status &test_status
pebempl_activity_date &test_activity_date
from pebempl
where pebempl_pidm = &sel_pidm
end-select
print &sel_pidm (+1,1)
print &test_last_work_date (,10)
print &test_term_date (, 30)
print &test_status (, 50)
print &test_activity_date (, 55)
print &rec_date (,75)
end-procedure

===================================================
here is my output
===================================================

4769801                                          A    04-JUN-02
05-JUN-2002
4769801                                          t    04-JUN-02
05-JUN-2002

===================================================
here is my problem
===================================================
Record gets updated but it updates last_work_date and term_date with null
value
if I use $v_rec_date in update then it is working fine
if I use &rec_date in insert statement value of rec_date  is getting
inserted

Can someone assit me to tell what could be wrong
we have :
Oracle Oracle8i Enterprise Edition Release 8.1.6.3.0
sqr    SQR/6.1.3/IBM RS6000/AIX 4.3.1/Oracle 8.0.6/Oct 05 2000



Thank You in advance

Vipul Patel
Admin Software Specialist
The George Washington University
Voice : 202-994-7697




PSA: Salary <> Slavery.  If you earn a salary, your employer is renting your
services for 40 hours a week, not purchasing your soul.  Your time is the
only real finite asset that you have, and once used it can never be
recovered, so don't waste it by giving it away.

"Time is the coin of your life. It is the only coin you
have, and only you can determine how it will be spent.
Be careful lest you let other people spend it for you."

Carl Sandburg
(1878 - 1967)



_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com