Re: Null values not allowed in column or variable

Hi Birgitta

I ran the below as per your solution:
update FILE a

I end up with an error which as below:
"Result of SELECT more than one row."

Any further advise...



Warm Regards,
JoeP

From: "HauserBirgitta" <Hauser@xxxxxxxxxxxxxxx>
Reply-To: RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
Subject: Re: Null values not allowed in column or variable
Date: Wed, 4 Oct 2006 17:58:58 +0200

Hi,

try to change your query to the following syntax:

update MyTable2 A
       set (A.Field1, A.Field2) = (select B.Field1, B.Field2
                                   from Mytable1 B
                                   where     A.Key1 = B.Key1
                                         and A.Key2 = B.Key2)
       where exists (select C.Key1, C.Key2
                       from MyTable1 C
                       where     A.Key1 = C.Key1
                             and A.Key2 = C.Key2)

Mit freunlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)




----- Original Message -----
From: "Joseph Pascoguin" <joe.pascoguin@xxxxxxxxxxx>
To: <rpg400-l@xxxxxxxxxxxx>
Sent: Wednesday, October 04, 2006 17:48
Subject: Re: Null values not allowed in column or variable


> Your assumption is correct.
> I donot want to allow null values into file FILE for OPTCOD.. where as
> BSCFILE allows null for NOPTCOD..
>
> I tried execute but Iam not correct as per SQL. How do I modify my sql
> further...
> update file a set optcod=(select
> noptcod from bscfile b
> where a.optcod=b.ioptcod and b.noptcod is not null
>
>
> Warm Regards,
> JoeP
>
>
>
>
>
>>From: Peter Levy <plevy@xxxxxxxxxxxx>
>>Reply-To: RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
>>To: RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
>>Subject: Re: Null values not allowed in column or variable
>>Date: Wed, 04 Oct 2006 11:41:16 -0400
>>
>>I'm going to assume that file BSCFILE allows NOPTCOD to be null, while
>>file
>>OPTCOD in file FILE isn't allowed that feature, and that in the former
>>file
>>one or more of the NOPTCOD fields are null.
>>
>>You can either change the file to allow null on OPTCOD or add "and
>>b.noptcod is not null" to your WHERE clause.
>>
>>Joseph Pascoguin wrote:
>>>Hi
>>>

>>>when I run below qry I get "Null values not allowed in column or variable

>>>optcod"..how do I solve this?
>>>
>>>update file a set optcod=(select
>>>noptcod from bscfile b
>>>where a.optcod=b.ioptcod)
>>>
>>>The sql displays line "      Null values not allowed in column or
>>>variable
>>>optcod"
>>>
>>>
>>>Kindly respond asap.
>>>
>>>
>>>Warm Regards,
>>>JoeP
>>>
>>>
>>>
>>>
>>>
>>>
>>>>From: rob@xxxxxxxxx

>>>>Reply-To: RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>

>>>>To: RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
>>>>Subject: Re: sql question
>>>>Date: Tue, 3 Oct 2006 11:51:36 -0400
>>>>
>>>>Go to the FAQ at http://faq.midrange.com and file how to process xls
>>>>spreadsheets in RPG.  Maybe search for hssf.  Then read those bad boys
>>>>and
>>>>UPDATE them out using traditional RPG, or change your update statement
>>>>to:
>>>>UPDATE file SET optcod=:NewOptcod WHERE futcod=:ifsFutcod
>>>>AND  Client=:ifsClient and optcod=:oldOptcod
>>>>
>>>>
>>>>Rob Berendt
>>>>--
>>>>Group Dekko Services, LLC
>>>>Dept 01.073
>>>>PO Box 2000
>>>>Dock 108
>>>>6928N 400E
>>>>Kendallville, IN 46755
>>>>http://www.dekko.com
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>"Joseph Pascoguin" <joe.pascoguin@xxxxxxxxxxx>
>>>>Sent by: rpg400-l-bounces@xxxxxxxxxxxx
>>>>10/02/2006 02:24 PM
>>>>Please respond to
>>>>RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
>>>>
>>>>
>>>>To
>>>>rpg400-l@xxxxxxxxxxxx
>>>>cc
>>>>
>>>>Subject
>>>>sql question
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>Hi,
>>>>
>>>>I ve a file in which futcod, descr, client & optcod are few of the
>>>>fields.
>>>>I 've an xl sheet from the client to replace optcod values with new
>>>>optcod
>>>>
>>>>values.
>>>>xl sheet contains 5 columns futcod, descr, client &optcod
>>>>
>>>>I did simple sql
>>>>
>>>>UPDATE file SET optcod='OPTWS' WHERE futcod='FUT01'
>>>>AND  Client='GOLDMAN' and optcod='INXMO'
>>>>

>>>>Since the number of entries(around 50) in the xl sheet are less, I opted

>>>>for
>>>>this simple manual SQL.
>>>>

>>>>How to handle this differently by using a program when the list is big

>>>>or
>>>>

>>>>some times we get 3 to 4 xl sheets from the client(optcod values changes

>>>>
>>> >from time to time!)?
>>>
>>>>Warm Regards,
>>>>JoeP
>>>>
>>>>_________________________________________________________________

>>>>Express yourself instantly with MSN Messenger! Download today it's FREE!

>>>>http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
>>>>
>>>>--
>>>>This is the RPG programming on the AS400 / iSeries (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.
>>>>
>>>>
>>>>--
>>>>This is the RPG programming on the AS400 / iSeries (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.
>>>>
>>>>
>>>
>>>_________________________________________________________________

>>>Express yourself instantly with MSN Messenger! Download today it's FREE!

>>>http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
>>>
>>>
>>
>>--
>>Peter Levy
>>Alliance Shippers, IT Dept
>>Englewood Cliffs, NJ
>>Voice: 201-227-0400
>>Fax: 201-227-0925
>>Email: plevy@xxxxxxxxxxxx
>>AIM: pklevyalliance2
>>
>>---------------
>>There are 10 kinds of people in the world;
>>Those who understand binary and those who don't.
>>
>
>
>>--

>>This is the RPG programming on the AS400 / iSeries (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.
>>
>
> _________________________________________________________________
> Express yourself instantly with MSN Messenger! Download today it's FREE!
> http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
>
> --

> This is the RPG programming on the AS400 / iSeries (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.
>
>
>


--
This is the RPG programming on the AS400 / iSeries (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.

_________________________________________________________________