RE: [sqr-users] Problem with data being inserted into a tablefromanarray

["Knapp, Richard" <KnappR@umsystem.edu>]

I didn't know SQR could do this.  Have you used this method before?

I would put your insert into its own procedure and call that procedure
conditionally.

Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu

-----Original Message-----
From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org
[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of
Akiko Flores
Sent: Monday, February 27, 2006 9:43 AM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] Problem with data being inserted into a
tablefrom anarray

Richard,

I'm using an if statement .. see below in red.

Akiko

**********************

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 

>>> KnappR@umsystem.edu 2/27/2006 9:38:16 AM >>>

I can't make out where your insert is conditional.

Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu 
-----Original Message-----
From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org 
[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of
Akiko Flores
Sent: Monday, February 27, 2006 9:24 AM
To: sqr-users@sqrug.org 
Subject: [sqr-users] Problem with data being inserted into a table from
anarray

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 

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


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

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