RE: [sqr-users] Insert duplicates

["Karunakaran Perumbrayoor" <karunp@hexaware.com>]

Murali,
 
          Did u try printing the #sql-status? Why do U need to use procedures 
like SQL-Error1. There will be at present only an Unique Constraint Error to be 
taken care of. I dont see ur code checking for other conditions. I think by 
some way, your code is executing when-other loop and processing 
RollBack-Transaction Procedure which will be seen in sqlerr.sqc. Then a Stop is 
issued. In the end, the Program aborts due to Unique Constraint. So what do U 
do for that??
 
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
 
 
It is better, U do a display of the error message and status. Dont do any such 
activities like Rollback or Stop. That should serve the purpose.
Your select will continue to process rest of the rows.
 
Regards...
Karun

        -----Original Message----- 
        From: Al H [mailto:maillists@starbuckk.net] 
        Sent: Sat 16-Apr-2005 1:08 AM 
        To: sqr-users@sqrug.org 
        Cc: 
        Subject: Re: [sqr-users] Insert duplicates
        
        
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



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