× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.