× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. October 2006

Re: Null values not allowed in column or variable

Hi Birgitta
I still keep getting same problem though I changed (select distinct....)to what you suggested 

"Result of SELECT more than one row. "
I posted a query on midrange(CL), I will try Rob's solution to see whether I can prevent null values first of all into XLFILE. 
Null values came into the file XLFILE after I did CPYFRMIMPF I believe.


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 20:55:12 +0200

Hi,

try to add DISTINCT to the first SELECT-Statement:
update FILE a
        set OPTCOD = (select Distinct b.nPTCOD from XLFILE b where
 a.OPTCOD=b.iOPTCOD)
        where exists (select c.iOPTCOD from XLFILE C where
 a.OPTCOD=c.iOPTCOD)


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 19:23
Subject: Re: Null values not allowed in column or variable


> Hi Birgitta
>
> I ran the below as per your solution:
> update FILE a
>        set OPTCOD = (select b.nOPTCOD from XLFILE b where
> a.OPTCOD=b.iOPTCOD)
>        where exists (select c.iOPTCOD from XLFILE C where
> a.OPTCOD=c.iOPTCOD)
>
> 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.
>>
>
> _________________________________________________________________
> 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.



_________________________________________________________________
Don't just search. Find. Check out the new MSN Search! http://search.msn.com/

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.