RE: [sqr-users] Insert duplicates

["Gallagher, Neil \(IT HR Support\)" <ngallagher@unumprovident.com>]

You might try adding a clause to your Insert SQL to say something like

        and not exists 
                (Select * from target-table
                        Where target.key1= source.key1
                        And  target.key2= source.key2
                        And  target.key3= source.key3
                )

This would nip the errors in the bud.

Neil Gallagher
UNUM Provident Corporation
Corporate HR Systems Support Team
(207) 575-8046
Mailstop T211
Colonel Westbrook Executive Park
Westbrook, Maine 04092
 

-----Original Message-----
From: sqr-users-bounces+ngallagher=unumprovident.com@sqrug.org
[mailto:sqr-users-bounces+ngallagher=unumprovident.com@sqrug.org] On
Behalf Of Kaithi, Murali
Sent: Friday, April 15, 2005 11:55 AM
To: g.bencke@csuohio.edu
Cc: This list is for discussion about the SQR database
reportinglanguagefromHyperion Solutions.
Subject: RE: [sqr-users] Insert duplicates

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


______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________

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


______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________

_______________________________________________
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