Re: ora-01400: cannot insert NULL...

[Alan Ljungberg <Alan.Ljungberg@CNPL.ENBRIDGE.COM>]

Maria,

In PS v8  on Oracle all but the date fields are usually defined as NOT
NULL.  Having a NOT NULL constraint means you are required to provide a
value on each row inserted, even if only a space.  In your INSERT statement
you are inserting a row and providing values by variables for 5 fields. Any
columns unspecified in the INSERT will insert NULL values.  Inserting NULL
(no value) in a column designated as NOT NULL produces the error you
received.  Either you are missing columns in your insert, or the one or
more of variables used has NULL value.
-Alan.




                    "Brickl,
                    Maria"                To:     SQR-USERS@list.iex.net
                    <mbrickl@UWC.E        cc:
                    DU>                   Subject:     ora-01400: cannot insert 
NULL...
                    Sent by:
                    "Discussion of
                    SQR, Brio
                    Software's
                    database
                    reporting
                    language"
                    <SQR-USERS@lis
                    t.iex.net>


                    06/24/2002
                    03:00 PM
                    Please respond
                    to sqr-users






I am getting the following error and just can't figure out what is wrong
with the procedure it errors out in.

$Sql-Failed in CREATE-EMPLID-VENDOR-ID
sql error: ORA-01400: cannot insert NULL into
("SYSADM"."PS_PERS_SF_VENDOR"."VENDOR_SETID")

This is what is in CREATE-EMPLID-VENDOR-ID

!*******************************************
! create-emplid-vendor-id: Create vendor info for individual
!*******************************************
BEGIN-PROCEDURE  CREATE-EMPLID-VENDOR-ID
#debuga Show '     Entering procedure CREATE-EMPLID-VENDOR-ID for ID: '
$READ_EMPLID
  Let $print_error_par = 'CREATE-EMPLID-VENDOR-ID'
BEGIN-SQL on-error = Bad-SQL
INSERT INTO PS_PERS_SF_VENDOR
(EMPLID
,BUSINESS_UNIT
,VENDOR_ID
,EFFDT
,EFF_STATUS)
Values($READ_EMPLID
,$BUSINESS_UNIT
,$VENDOR_ID
,$ASOFTODAY
,'A')
END-SQL


   Let $VENDOR_CREATED = 'Y'
! Now get the Name for the vendor
!M000176 UW-MIL 05-15/02 - Mod to also get CAMPUS_ID
BEGIN-SELECT
GETNAME.NAME
GETNAME.CAMPUS_ID
!M000176
UW-MIL 05-15/02
   Let $NAME1 = Substr(Ltrim(Rtrim(&GETNAME.NAME,' '),' '),1,40)
   Let $uw_campus_id = &GETNAME.CAMPUS_ID
!M000176
UW-MIL 05-15/02
FROM PS_PERSONAL_DATA GETNAME WHERE GETNAME.EMPLID=$READ_EMPLID
END-SELECT
#debuga Show '     Exiting procedure CREATE-EMPLID-VENDOR-ID'

*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~
Maria K Brickl
mbrickl@uwc.edu <mailto:mbrickl@uwc.edu>
Information Systems Programmer/Analyst
University of Wisconsin Colleges
780 Regent St, PO Box 8680, Madison, WI  53708-8680
Phone: 608.265.2677 / Fax: 608.265.5770
*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*