Re: [sqr-users] Insert duplicates

[Al H <maillists@starbuckk.net>]

I've found that the -9 in #sql-status is unreliable in Oracle
environments. Use $sql-error instead.  Try this:

   if instr($sql-error,'unique constraint',0) =  0
       ! Not a duplicate key
  else
       ! duplicate key
  end-if


On Fri, 15 Apr 2005 11:54:48 -0400, you wrote:

>Hi Gina,
>Thanks for your response.
>The value of #sql-status is -9
>Even if I added break its doing the same thing.
>I don't believe that the Current value of -9 and when-other are
>executing the code under the when-other clause.
>Any suggestions.
>
>Thanks,
>Murali
>
>-----Original Message-----
>From: Gina Bencke [mailto:g.bencke@csuohio.edu] 
>Sent: Friday, April 15, 2005 11:28 AM
>To: Kaithi, Murali
>Subject: RE: [sqr-users] Insert duplicates
>
>
>Kaithi,
>
>What is the value of #sql-status?  If it is -9 and you want to skip the
>error you need to add a break statement 
>    when = -9      !unique constraint for Oracle Environments
>     break
>
>Currently values of -9 and when-other are executing the code under the
>when-other clause
>
>Gina Bencke
>216-875-9630
> 
> 
>-----Original Message-----
>From: sqr-users-bounces+gina.work=bencke.com@sqrug.org
>[mailto:sqr-users-bounces+gina.work=bencke.com@sqrug.org] On Behalf Of
>Kaithi, Murali
>Sent: Friday, April 15, 2005 11:15 AM
>To: This list is for discussion about the SQR database reportinglanguage
>fromHyperion Solutions.
>Subject: [sqr-users] Insert duplicates
>
>Hi,
>I am trying to insert into a table from another table.
>If I get across the duplicates, I would like to skip them and insert
>rest of the rows.
>I remember the below code worked before but is not now, I am confused.
>Could someone help me.
>My insert statement would start like 
>
>begin-sql ON-ERROR=SQL-Error1
>
>And below is my SQL-Error1 procedure
>
>begin-procedure SQL-Error1
>  show '#sql-status is ' #sql-status
>  evaluate #sql-status
>#ifdef DB2
>    when = 6100    !DB2 error for empty-table result set
>      break
>#end-if
>
>#ifdef DB2UNIX
>    when = 6100    !DB2 error for empty-table result set
>      break
>#end-if
>
>    when = -99999  !Token "when" clause for non-DB2 environments
>    when = -9      !unique constraint for Oracle Environments
>    when-other
>      display $sqr-program noline
>      display ': ' noline
>      display $ReportID noline
>      display ' - SQL Statement = '
>      display $SQL-STATEMENT
>      display 'SQL Status =' noline
>      display #sql-status 99999 noline
>      display ' ' noline
>      display 'SQL Error  = ' noline
>      display $sql-error
>      display $Sql-Msg
>   SHOW  $loadrecord
>      Do Rollback-Transaction
>      stop
>  end-evaluate
>end-procedure SQL-Error1
>
>Thanks,
>Murali
>


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