Thank you for your input Santiago. The statement you offered is the one I settled on. So far my data looks good. On to the RPG part.
Jim Minisce
----- Original Message ----
From: Santiago G Martí <smarti@xxxxxxxx>
To: RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Sent: Tuesday, March 3, 2009 9:11:53 AM
Subject: Re: SQL Help
I think you should change the sentence as:
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 * from LIBNAME/FCMST# as A
Where Q.ITNBR = A.ITNBR and Q.CTYPE = A.CTYPE and
A.FISYY = 2008)
Saludos,
---------------------------
Santiago Martí
Dusen, S.A.
---------------------------
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.
--
This is the RPG programming on the IBM i / System i (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.
midrange.com
