RE: NOT allowing null value into PF fields(big puzzle)

It worked out!
Thanks very much Elvis.
Your help is greatly appreciated.




Warm Regards,
JoeP

From: "Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
Reply-To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
Subject: RE: NOT allowing null value into PF fields(big puzzle)
Date: Wed, 4 Oct 2006 17:46:30 -0500

update file a set optcod=
  ifnull((select MIN(B.noptcod)
   from xlfile b
   where a.optcod=b.ioptcod),' ')

NOTE that I used blanks (' ') for default value of 'optcode'.  If this is a
numeric field, you'll have to use a 0 (zero) or something.

Elvis

-----Original Message-----
Subject: RE: NOT allowing null value into PF fields(big puzzle)

update file a set optcod=
  (select MIN(B.noptcod)
   from xlfile b
   where a.optcod=b.ioptcod)
where exists
  (select 1
   from xlfile b
   where a.optcod=b.ioptcod)

Great! Thanks for the response.
As per your advise I have done the above sql, it has updated 252 rows when
actually it has to update 297 rows..
In order to update correctly in all rows do you have any further advise
without getting the problmes (as discussed in below posts)..

I appreciate your help!

Warm Regards,
JoeP


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

_________________________________________________________________