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

Also I have done
select * from XLFILE  where IOPTCOD  IS  NULL

It didnt fetch me any result as no records to display!


Warm Regards,
JoeP

From: "Joseph Pascoguin" <joe.pascoguin@xxxxxxxxxxx>
Reply-To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Subject: Re: NOT allowing null value into PF fields(big puzzle)
Date: Thu, 05 Oct 2006 04:56:37 +0800

CREATE TABLE XFILE
(IFUTCOD CHAR (4 ),
  NOPTCOD CHAR (6 ) NOT NULL,
  IOPTCOD CHAR (6 ) NOT NULL WITH DEFAULT)
RCDFMT XFILER

Hi Rob As you said I created the table as above  and
1)I have done CPYFRMIMPF
===> CPYFRMIMPF FROMSTMF('/etc/dalc/apps/testfile1.csv') TOFILE(JPASCO/
XFILE) MBROPT(*REPLACE) RCDDLM(*CRLF)

and tried to execute the simple SQL:

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

The sql displays line "      Null values not allowed in column or variable
optcod"

---as per Birgitta's advise I tried to execute the below sql, but i get same

problem
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."

I have introduced distinct in select statement above , but still same
problem...
Do you think any solution for this?


Warm Regards,
JoeP





>From: rob@xxxxxxxxx
>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...
>Date: Wed, 4 Oct 2006 15:24:33 -0400
>
>You have a few options on each field.  Here's the three options
>CREATE TABLE XFILE
>  (IFUTCOD CHAR (4 ),
>   NOPTCOD CHAR (6 ) NOT NULL,
>   IOPTCOD CHAR (6 ) NOT NULL WITH DEFAULT)
>  RCDFMT XFILER
>IFUTCOD allows null
>NOPTCOD will not allow null
>IOPTCOD will not allow null but if nothing is passed will default it to
>blanks.
>
>Like,
>
>INSERT INTO ROB/JOEP (ifutcod) VALUES('A')
>Null values not allowed in column or variable *N.
>Failed because NOPTCOD is NOT NULL and does not specify a default.
>
>INSERT INTO ROB/JOEP (NOPTCOD) VALUES('A')
>1 rows inserted in JOEP in ROB.
>worked fine because IFUTCOD allows a null.  And IOPTCOD got defaulted to
>blanks.
>

>Problem with DDS created files. DDS created files are built to allow crap

>in most fields.  Some legacy thing when people got off grunting, thumping
>their chest and writing batch programs that checked fields down to the
>byte level for data that is right.  Like the first COBOL program I wrote
>in a class - make sure a field that is supposed to be numeric, is.  DDS
>files are fast to write to, but slow to read from because they don't care
>what kind of data you write to them.  But they'll check the data quality
>on a read.  When you think about it, most of your data access is reading,
>right?
>SQL created files check data on a write.  Not on a read.
>Recently saw a classic example of this.
>Compile this:
>                 R JOEPR
>                   MYCHAR         5A
>                   MYPACK         9P 2
>with CRTPF FILE(ROB/JOEP) SRCFILE(ROB/QDDSSRC)
>Run this command:  CRTPF FILE(ROB/JOEP2) RCDLEN(10)
>STRSQL
>INSERT INTO ROB/JOEP2 VALUES('NOWISTHETI')
>
>CREATE TABLE ROB/JOEP3
>(MYCHAR CHAR (5 ) NOT NULL WITH DEFAULT,
>  MYPACK DEC (9 , 2) NOT NULL WITH DEFAULT)
>RCDFMT JOEP3R
>(exit sql)
>
>CPYF FROMFILE(JOEP2) TOFILE(JOEP) MBROPT(*ADD) FMTOPT(*NOCHK)
>Notice:  Allowed crap into JOEP.  You have a packed decimal field with
>alpha characters in it.
>
>But CPYF FROMFILE(JOEP2) TOFILE(JOEP3) MBROPT(*ADD) FMTOPT(*NOCHK)
>Tells me to bugger off with CPF5035-Data mapping error on member JOEP3.
>
>Now if I attempt to read the corrupted DDS created file with a simple
>RPGLE cycle program
>      FJOEP      IP   E             DISK
>      FQSYSPRT   O    F   80        PRINTER
>      OQSYSPRT   D
>      O                       MYCHAR
>      O                                              ' '
>      O                       MYPACK
>I will get RNQ0907-Decimal-data error occurred.
>
>Verifying that DDS files check data integrity on read.  SQL created files
>check data integrity on write.
>
>
>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: midrange-l-bounces+rob=dekko.com@xxxxxxxxxxxx
>10/04/2006 02:02 PM
>Please respond to
>Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
>
>
>To
>midrange-l@xxxxxxxxxxxx
>cc
>
>Subject
>NOT  allowing null value into PF fields...
>
>
>
>
>
>
>Hi All,
>
>Below is my physical file,
>
>R XFILER
>   Ifutcod        4
>   Idescr        25
>   Iclient        4
>   Ioptcod        6
>   Noptcod        6
>   reason        25
>
>I donot want to allow NULL values into the fields NOPTCOD , IOPTCOD when
>copying or importing data into this file.
>
>what changes I should be making in this PF. Iam creating this file to
>import
>data from an xl sheet(CPYFRMIMPF).
>
>JoeP
>
>_________________________________________________________________
>FREE pop-up blocking with the new MSN Toolbar - get it now!
>http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/
>
>--
>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.
>
>
>--

>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.
>

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

--
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.

_________________________________________________________________