SQL Update Null Value

[FaStOnE]

Can someone explain this to me?

Update TOFILE A                      
 Set ( iGRP3, iGRP4 )                
   = ( Select coalesce( iGRP3, ' ' ),
              coalesce( iGRP4, ' ' ) 
         from FROMFILE B             
        Where A.iACTV  = B.iACTV     
          and A.iSTOR  = B.iSTOR     
          and A.iITEM  = B.iITEM  ) 

Gives me the message:

Null values not allowed in column or variable IGRP3.

I'm forcing the value to be either the actual value or *blank...

What gives??

[MichaelCatalani]

Re: SQL Update Null Value

what are the odds it has something to do with the freaking parenthesis....

[FaStOnE]

Re: SQL Update Null Value

Don't EVEN go there...

[MichaelCatalani]

Re: SQL Update Null Value

I would almost be tempted to chage the ' ' to something like '#' to simply to see if the program runs without an error.

[MichaelCatalani]

Re: SQL Update Null Value

Wait, these arent numeric fields are they?

[FaStOnE]

Re: SQL Update Null Value

Nope .. 5 byte chars

[FaStOnE]

Re: SQL Update Null Value

I tried it without the "(" after the = sign ... no go. *WHEW*

I have it now using "***" as the replacement value .. still says it's trying to use a NULL value. This is REALLY driving me nuts... specially after 10 hours!

[FaStOnE]

Re: SQL Update Null Value

OK .. strangeness has set in ...

I was able to get this to run

Update E3SLB.E3SITM A                    
 Set ( A.iGRP3, A.iGRP4 )                
   = ( Select coalesce( B.iGRP3, '***' ),
              coalesce( B.iGRP4, '***' ) 
         from CPELIB.TMPITM B            
        Where A.iACTV  = B.iACTV         
          and A.iSTOR  = B.iSTOR         
          and A.iITEM  = B.iITEM  )      
Where ( iACTV || iSTOR || iITEM ) in (   
   Select iACTV || iSTOR || iITEM        
     from CPELIB.TMPITM ) 

Knowing there aren't any NULL values in iGRP3 or iGRP4

I'm Brain-Fried....

[MichaelCatalani]

Re: SQL Update Null Value

OK .. strangeness has set in ...

I was able to get this to run

Update E3SLB.E3SITM A 
Set ( A.iGRP3, A.iGRP4 ) 
= ( Select coalesce( B.iGRP3, '***' ),
coalesce( B.iGRP4, '***' ) 
from CPELIB.TMPITM B 
Where A.iACTV = B.iACTV 
and A.iSTOR = B.iSTOR 
and A.iITEM = B.iITEM ) 
Where ( iACTV || iSTOR || iITEM ) in ( 
Select iACTV || iSTOR || iITEM 
from CPELIB.TMPITM ) 

Knowing there aren't any NULL values in iGRP3 or iGRP4

I'm Brain-Fried....

Yea, you were going down the same route I was, with applying the file prefix on the fields of the select statement. I'm guessing that because the prefix was not there, it was using a field that was not getting populated, and if the file is created so that the fields are null capable, thats why they were being initialized with nulls.

[FaStOnE]

Re: SQL Update Null Value

Ya know .. this wouldn't be such a pain in the patookey if the table didn't have over 31 million + records in it

[Mercury]

Re: SQL Update Null Value

Why not plainly

Update TOFILE A                      
 Set ( iGRP3, iGRP4 )                
   = ( Select coalesce( iGRP3, ' ' ),
              coalesce( iGRP4, ' ' ) 
         from FROMFILE B             
        Where A.iACTV  = B.iACTV     
          and A.iSTOR  = B.iSTOR     
          and A.iITEM  = B.iITEM  ) 
[COLOR="Red"]Where Exists ( Select 1 
         from FROMFILE B             
        Where A.iACTV  = B.iACTV     
          and A.iSTOR  = B.iSTOR     
          and A.iITEM  = B.iITEM  )[/COLOR]

If you don't use "Where Exists" together with the last sub select (in red above), the fields IGRP3 and IGRP4 return NULL value if the "Where Fields" in the first sub select don't match.