Re: update statement

["Mike the Code Monkey" <koldark@xxxxxxxxx>]

Try

UPDATE ITSKCTST83/ATX SET ATX.TXIDTE
= (SELECT coalesce(APH.APIRDT, ' ' or 0 based on default value) FROM
ITSKCTST83/APH
WHERE ATX.TXVNDR=APH.APVNDR AND ATX.TXINV = APH.APINV
AND APH.PHDCYR = ATX.TXDCYR AND ATX.TXDCPX = APH.PHDCPX
AND ATX.TXDCSQ = APH.PHDCSQ AND ATX.TXCOMP = APH.APCMPY)

I think this might work. When doing joins, you have to think about the
possibility of having null values.

On 6/21/06, Jszarlik <jszarlik@xxxxxx> wrote:
> Hello,
> I would like to update one field in ATX file and I try to use SQL.
> The SQL statement is following:
>
> UPDATE ITSKCTST83/ATX SET ATX.TXIDTE
> = (SELECT APH.APIRDT FROM ITSKCTST83/APH
> WHERE ATX.TXVNDR=APH.APVNDR AND ATX.TXINV = APH.APINV
> AND APH.PHDCYR = ATX.TXDCYR AND ATX.TXDCPX = APH.PHDCPX
> AND ATX.TXDCSQ = APH.PHDCSQ AND ATX.TXCOMP = APH.APCMPY)
>
> but I receive message:
>
> Null values not allowed in column or variable TXIDTE
>
> could anybody explain it and how to resolve it?
>
> Thanks in advance
> Jarek
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.