Re: update statement

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.