Re: SQL Help

[Jim Essinger <dilbernator@xxxxxxxxx>]

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.