RE: [sqr-users] Insert duplicates

["Raja Iqbal" <riqbal@roosevelt.edu>]

Any duplicates can be ignored using flag concept. 

e.g: Before inserting record into table, check flag value True/False.
i.e: var1, var2, var3    --> Recordset variables
     fvar1, fvar2, fvar3 --> Flag Variables or Temp Variables.

If var1 = fvar1 and var2 = fvar2 and var3 = fvar3 Then
   ** It means previous record selected or inserted is same 
   --- Ignore Insert statement.
Else
   --- execute insert statement 
   --- assign variable values to flag variables.
End If


Try out this.

Raja

-----Original Message-----
From: sqr-users-bounces+riqbal=roosevelt.edu@sqrug.org
[mailto:sqr-users-bounces+riqbal=roosevelt.edu@sqrug.org]On Behalf Of
Turner, Ivan
Sent: Friday, April 15, 2005 1:37 PM
To: This list is for discussion about the SQR database reportinglanguage
fromHyperion Solutions.; g.bencke@csuohio.edu
Subject: RE: [sqr-users] Insert duplicates


To really, truly  do it in one pass, use "exists"

INSERT INTO INVC_CUST_REPORT (CUSTOMER_ACCT_ID, REPORT_ID)
SELECT #customer_id, 1 FROM DUAL
WHERE NOT EXISTS (SELECT 'X' FROM INVC_CUST_REPORT
                WHERE CUSTOMER_ACCT_ID = #customer_id
                AND   REPORT_ID = 1)
and exists (
            select 'x' from customer_acct
            where customer_acct_id = #Customer_ID
           )

-----Original Message-----
From: sqr-users-bounces+ivan.turner=qwest.com@sqrug.org
[mailto:sqr-users-bounces+ivan.turner=qwest.com@sqrug.org] On Behalf Of
Gallagher, Neil (IT HR Support)
Sent: Friday, April 15, 2005 12:17 PM
To: This list is for discussion about the SQR database reportinglanguage
fromHyperion Solutions.; g.bencke@csuohio.edu
Subject: RE: [sqr-users] Insert duplicates

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

_______________________________________________
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