Re: SQL Help

Thanks to all that have responded. I have used Jim's advise and that seems to have worked. I have spot checked 10 - 20 records and they appear to have updated as I would have expected. I will continue to check my results. Just as an FYI, I tried the following:

update qtemp/fcmst# as Q
set (FCJAN,FCFEB,FCMAR,FCAPR,FCMAY,FCJUN,FCJUL) =
(Select coalesce( FCJAN , 0)
, coalesce( FCFEB , 0)
, coalesce( FCMAR , 0)
, coalesce( FCAPR , 0)
, coalesce( FCMAY , 0)
, coalesce( FCJUN , 0)
, coalesce( FCJUL , 0)
From LIBNAME/FCMST# as A
Where Q.ITNBR = A.ITNBR and Q.CTYPE = A.CTYPE and
A.FISYY = 2008)
Where FISYY = 2028

That still produces the error "Null values not allowed in column or variable FCJAN" .

Thanks Jim!




----- Original Message ----
From: Jim Essinger <dilbernator@xxxxxxxxx>
To: RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Sent: Monday, March 2, 2009 10:59:51 AM
Subject: Re: SQL Help

For reasons that have been covered before, and can be found in the archives,
to eliminate nulls from your update you need to add the something like the
following to your statement;

where exists
(Select 1
From LIBNAME/FCMST# as A
Where Q.ITNBR = A.ITNBR and Q.CTYPE = A.CTYPE and
A.FISYY = 2008)

So your entire statement might look like;

update qtemp/fcmst# as Q
set (FCJAN,FCFEB,FCMAR,FCAPR,

FCMAY,FCJUN,FCJUL) =
(Select FCJAN,FCFEB,FCMAR,FCAPR,FCMAY,FCJUN,FCJUL
From LIBNAME/FCMST# as A
Where Q.ITNBR = A.ITNBR and Q.CTYPE = A.CTYPE and
A.FISYY = 2008)
Where FISYY = 2028

and exists
(Select 1
From LIBNAME/FCMST# as A
Where Q.ITNBR = A.ITNBR and Q.CTYPE = A.CTYPE and
A.FISYY = 2008)

HTH

Jim

On Mon, Mar 2, 2009 at 8:27 AM, Jim Minisce <jminisce@xxxxxxxxx> wrote:

Hi all,

I am working on an SQL statement to update fields in a file. Here is
what I have so far:

update qtemp/fcmst# as Q
set (FCJAN,FCFEB,FCMAR,FCAPR,FCMAY,FCJUN,FCJUL) =
(Select FCJAN,FCFEB,FCMAR,FCAPR,FCMAY,FCJUN,FCJUL
From LIBNAME/FCMST# as A
Where Q.ITNBR = A.ITNBR and Q.CTYPE = A.CTYPE and
A.FISYY = 2008)
Where FISYY = 2028

I
am working on this in an interactive SQL session prior to moving to an
RPG program. I am receiving the following error message: "Null values
not allowed in column or variable FCJAN". I have tried using the
Coalesce SQL statement but that will only allow me to move one month at
a time. Here is an example that works for only one month:

update qtemp/fcmst# as Q
set FCJAN =
Coalesce((Select FCJAN
From LIBNAME/FCMST# as A
Where Q.ITNBR = A.ITNBR and Q.CTYPE = A.CTYPE and
A.FISYY = 2008),FCJAN)
Where FISYY = 2028

The
overall goal would be move 7 months of data from one fiscal year to
another. I am sure my syntax is correct but I am missing something
that I do not see it right now. I would appreciate any help. Man I
hate Mondays.

Jim Minisce
Godiva Chocolatier, Inc.