[sqr-users] Problem with data being inserted into a table from anarray

["Akiko Flores" <Akiko.Flores@lcra.org>]

I'm loading a file that lists a vendor number and a duns number into an array 
which works fine.  Here's the catch ...some vendors do not have duns numbers 
but my goal is when I insert data into the table from the array it will not 
insert a row for any vendor that does not have a duns number (other words, if 
duns number is blank .. it will not insert this row of data).  It looks like my 
if statement is being ignore ...  Below is my code.

begin-procedure Insert-Dunsnbr-Array 
  #DEBUGH show 'Insert-Dunsnbr-Array'

  let $_sql_statement = 'Insert Dunsnbr: INSERT' 

  #Ifdef DEBUGH

      show 'vendor id         :' $in_vendor_id          '*'
      show 'DUNS Nbr            :' $in_std_id_num               '*'
  #End-if 

if ($in_vendor_id <> ' ' and $in_std_id_num <> ' ')
 
 #Ifdef DEBUGV
      Show 'in if-stmt vendor id: '$in_vendor_id ' dns number:' $in_std_id_num 
  #End-if
 
 begin-sql  on-error=SQLError

      insert into PS_VENDOR_ID_NBRS
       (SETID,
        VENDOR_ID,
        STD_ID_NUM_QUAL,
        STD_ID_NUM,
        STD_ID_SETID)
      values
             (
            '1LCRA',       
            $in_vendor_id,   
            'DNS',
            $in_std_id_num, 
            ' ')
           
   end-sql
end-if

   if #sql-count > 0
      add 1 to #_insert_ctr
   end-if

end-procedure Insert-Dunsnbr-Array 

here's my error message
Get-Dunsnbr-Array
Vendor Id         :000004569*
DNS Number     :000000001*
Insert-Dunsnbr-Array
vendor id         :000004569*
DUNS Nbr       :000000001*
in if-stmt vendor id: 000004569 dns number:000000001
Vendor Id         :001688577*
DNS Number     :000000002*
Insert-Dunsnbr-Array
vendor id         :001688577*
DUNS Nbr       :000000002*
in if-stmt vendor id: 001688577 dns number:000000002
Vendor Id         :003360884*
DNS Number     :*
Insert-Dunsnbr-Array
vendor id         :003360884*
DUNS Nbr       :*
in if-stmt vendor id: 003360884 dns number:
SQLError
 SQR Program   : C:\DOCUMENTS AND SETTING\DESKTOP\LCRA_3327\DUNSNR_L.SQR
 SQL Statement : 
 SQL Status    :  1400
 SQL Error     : ORA-01400: cannot insert NULL into 
("SYSADM"."PS_VENDOR_ID_NBRS"."STD_ID_NUM")
 

Error on line 166:
   (SQR 3301) Program stopped by user request.


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users