Re: SQL Help

[Jim Minisce <jminisce@xxxxxxxxx>]

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.