Re: Psoft AP developers - help to resolve the update

[Daniel Haag <haag@MT.NET>]

Jim,
I don't see why you need anything after the clause
'and     act.posting_date >= '17-NOV-2000'.
Assuming that additional where criteria is necessary, a much cleaner 'where'
clause is shown below.  This technique works in Oracle but I'm not sure about
other environments.  Good luck.

Dan

update ps_vchr_acctg_line act
 set act.descr = (select vchr.descr from sysadm.ps_voucher_line vchr
where act.business_unit = vchr.business_unit
  and act.voucher_id =vchr.voucher_id
  and act.voucher_line_num=vchr.voucher_line_num
  and act.distrib_line_num <=vchr.total_distribs
  and vchr.descr <> ' '
  and act.descr <> vchr.descr)
where act.unpost_seq = 0
and   act.appl_jrnl_id = 'ACCRUAL'
and     act.pymnt_cnt = 0
and     act.dst_acct_type = 'DST'
and     act.cf_bal_line_num = 0
and     act.ledger_group = 'ACTUALS'
and     act.posting_date >= '17-NOV-2000'
and (act.business_unit, act.voucher_id, act.voucher_line_num,
act.distrib_line_num) = (select vchr1.business_unit,
vchr1.voucher_id, vchr1.voucher_line_num, vchr1.distrib_line_num
from
sysadm.ps_voucher_line vchr1
where act.business_unit =vchr1.business_unit
  and act.voucher_id =vchr1.voucher_id
  and act.voucher_line_num =vchr1.voucher_line_num
  and act.distrib_line_num <=vchr1.total_distribs
  AND vchr1.descr <> ' ')


Lambert, Jin wrote:

> Hi, your AP psoft experts out there.... please help me to resolve this
> dilemma- and my thanks in advance!!!
>
> situation:
> we have the description (descr) in the ps_vchr_acctg_line table messed up,
> and they need to be swept w/description (descr) in the ps_voucher_line
> table.
>
> I have tried:
> I have created the following SQL script to update the table, but I got error
> - saying it is trying to insert null value into the descr of
> ps_vchr_acctg_line - which is very strange, since when I did select, all
> descr of voucher_line have value other than null.
>
> here is the SQL script
>
> update ps_vchr_acctg_line act
>  set act.descr = (select vchr.descr from sysadm.ps_voucher_line vchr
>                                                 where act.business_unit =
> vchr.business_unit
>                                              and act.voucher_id =
> vchr.voucher_id
>                                                   and act.voucher_line_num =
> vchr.voucher_line_num
>                                                 and act.distrib_line_num <=
> vchr.total_distribs
>                                                 and vchr.descr <> ' '
>                                                 and act.descr <> vchr.descr)
>
> where act.unpost_seq = 0
> and   act.appl_jrnl_id = 'ACCRUAL'
> and     act.pymnt_cnt = 0
> and     act.dst_acct_type = 'DST'
> and     act.cf_bal_line_num = 0
> and     act.ledger_group = 'ACTUALS'
> and     act.posting_date >= '17-NOV-2000'
>
> and act.business_unit = (select vchr1.business_unit from
> sysadm.ps_voucher_line vchr1
>                                                 where act.business_unit =
> vchr1.business_unit
>                                                 and act.voucher_id =
> vchr1.voucher_id
>                                                 and act.voucher_line_num =
> vchr1.voucher_line_num
>                                                 and act.distrib_line_num <=
> vchr1.total_distribs
>                                                 AND vchr1.descr <> ' ')
>
> and act.voucher_id = (select vchr2. voucher_id from sysadm.ps_voucher_line
> vchr2
>                                                 where act.business_unit =
> vchr2.business_unit
>                                         and act.voucher_id =
> vchr2.voucher_id
>                                                 and act.voucher_line_num =
> vchr2.voucher_line_num
>                                                 and act.distrib_line_num <=
> vchr2.total_distribs
>                                                 and vchr2.descr <> ' ')
>
> and act.voucher_line_num = (select vchr3.voucher_line_num from
> sysadm.ps_voucher_line vchr3
>                                                 where act.business_unit =
> vchr3.business_unit
>                                         and act.voucher_id =
> vchr3.voucher_id
>                                                 and act.voucher_line_num =
> vchr3.voucher_line_num
>                                                 and act.distrib_line_num <=
> vchr3.total_distribs
>                                                 and vchr3.descr <> ' ')
>
> and act.distrib_line_num <= (select vchr4.total_distribs from
> sysadm.ps_voucher_line vchr4
>                                                 where act.business_unit =
> vchr4.business_unit
>                                         and act.voucher_id =
> vchr4.voucher_id
>                                                 and act.voucher_line_num =
> vchr4.voucher_line_num
>                                                 and act.distrib_line_num <=
> vchr4.total_distribs
>                                                 and vchr4.descr <> ' ')
>
> Jin Lambert
> voice:       (816) 932-7529
> fax:          (603) 687-4815
> e-mail:      JLambert@hrblock.com