Date:         Tue, 6 Mar 2001 08:23:46 +0100
Reply-To:     Bas Pruijn <bas.pruijn@CMG.NL>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
Comments:     To: Michael Davis <sas-l@BASSETTCONSULTING.COM>
From:         Bas Pruijn <bas.pruijn@CMG.NL>
Subject:      Re: problem updating Oracle tables in-place
Comments: To: SAS-L@LISTSERV.VT.EDU
Content-Type: text/plain; charset="iso-8859-1"

Why not use the SAS access interface to BaaN and not update the oracle
tables below? The access to baan would give you the possibility to use the
metadata defined in BaaN. I haven't used it (yet), but would be an
alternative.

You can find some  help when you install the access to baan.

Greetings,
Bas

-----Original Message-----
From: Michael Davis [mailto:sas-l@BASSETTCONSULTING.COM]
Sent: donderdag 1 maart 2001 22:40
To: SAS-L@AKH-WIEN.AC.AT
Subject: problem updating Oracle tables in-place


Hello Friends,

The following problem occurs with SAS 8.1 run on an Windows NT server with
Oracle 7.4.1.

I'm working with a client that has an ERP (in this case, BaaN) whose data
is stored in Oracle databases.  They would like to have the ability to use
SAS to update those tables outside the ERP with data that must come from
other (usually legacy) systems.  Here is the problem.

Some of the Oracle tables to be updated have date or date-time
columns.  These columns are usually specified in Oracle as being not
null.  The ERP is apparently putting in a value that is not null but which
SAS interprets as an invalid date or date-time value.

So when we do something like:

   data ora_devl.<table name> ;
      modify ora_devl.<table name> <update table> ;
      by <key vars> ;
   run ;

SAS reads in the Oracle data and sets those variables to missing.  When
Oracle attempts to update the table in-place, it refuses to perform the
update and reports an error.  This occurs even when the update table omits
the troublesome columns.  Why?  When a MODIFY statement is processed, an
UPDATE statement is generated including all columns found on the database
table.

We're looking at some cumbersome work-a-rounds and talking to the ERP
vendor but if anyone else has hit this type of problem and cares to offer a
better solution than we might have thought of, please share.

BTW, PROC SQL updating does work for us but is unacceptably slow when
updating a large table.

- Michael "Mad Doggy" Davis


Michael L. Davis
Vice President
Bassett Consulting Services, Inc.
10 Pleasant Drive
North Haven  CT  06473-3712
E-Mail:  michael@bassettconsulting.com
Web:  http://www.bassettconsulting.com
Telephone:    203-562-0640
Facsimile:    203-498-1414
Messages:     888-477-1412