Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 17-11-2009
Member
 
Join Date: Nov 2009
Posts: 47
SQL problem if tuple is already in the database

Hello everyone, I am having a problem that I could not resolve. When I insert tuples in a table and call test, I am having the problems. But I do not want to insert a tuple since because it is already in the test database. I also could not use distinct in SELECT because I have to check each tuple. The DECLARE section is declared and so not to worry.

Code:
BEGIN
Open c1;
FETCH c1 INTO read_nameusu, read_namepat, read_prename, read_dtenai, read_depcol, read_worddep;
co_nameusu := read_nameusu;
co_namepat := read_namepat;
co_prenname := read_prename;
co_dtenai := read_dtenai;
co_depcol := read_depcol;
co_worddep := read_worddep;
   LOOP
        FETCH c1 INTO read_nameusu, read_namepat, read_prename, read_dtenai, read_depcol, read_worddep;
        IF read_namepat = co_namepat AND read_prename = co_prename AND read_dtenai = co_dtenai THEN
          IF read_depcol IS NULL AND co_depcol IS NULL THEN
            INSERT INTO ANOMALY VALUES
              (
              read_nameusu, read_namepat, read_prename, read_dtenai, read_depcol, read_worddep
              );
            INSERT INTO ANOMALY VALUES
              (
              co_nameusu, co_namepat, co_prename, co_dtenai, co_depcol, co_worddep
              );
          END IF;
          IF read_depcol IS NULL THEN
            ins_nameusu := read_nameusu;
            ins_namepat := read_namepat;
            ins_prename := read_prename;
            ins_dtenai := read_dtenai;
            ins_depcol := read_depcol;
            ins_worddep := read_worddep;
          ELSE 
            IF read_depcol > co_depcol AND co_depcol IS NOT NULL THEN
              ins_nameusu := read_nameusu;
              ins_namepat := read_namepat;
              ins_prename := read_prename;
              ins_dtenai := read_dtenai;
              ins_depcol := read_depcol;
              ins_worddep := read_worddep;
            END IF;
          END IF;
        ELSE
          IF /* If not in table */ THEN 
            INSERT INTO ATime VALUES           
            (
            ins_nameusu, ins_namepat, ins_prename, ins_dtenai, ins_depcol, ins_worddep
            );
          END IF;
        END IF;
        co_nameusu := read_nameusu;
        co_namepat := read_namepat;
        co_prename := read_prename;
        co_dtenai := read_dtenai;
        co_depcol := read_depcol;
        co_worddep := read_worddep;
        EXIT WHEN c1%NOTFOUND;
    END LOOP;
    CLOSE c1;
END;
Reply With Quote
  #2  
Old 17-11-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
Re: SQL problem if tuple is already in the database

First of all put your primary key or unique index on your key and manage it in a block apart on the right error code.

Secondly, for the rest part I simply do not understand what you are trying to achieve and where exactly are you having/facing the problems. Can you explain me in a simpler manner about what you are trying to do and where exactly you got blocked.
Reply With Quote
  #3  
Old 17-11-2009
Member
 
Join Date: Nov 2009
Posts: 47
Re: SQL problem if tuple is already in the database

In fact I must be careful to code the lines. Among all the duplicates I want to find a mistake, if depcol is null (date field) then I save the fields of tuple read, and in the next iteration I look when compared to tuple before the person is the same, if so then I look at the date, if still null I save both, if the field is filled depcol then I compare with the old tuple saved. And I take the most recent two and so on.
Reply With Quote
  #4  
Old 17-11-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
Re: SQL problem if tuple is already in the database

There is no 30000 solutions:
- to be a constraint and wait that results in a block of exception
- either make an insert or merge if you can afford an update
- be tested if a similar record exists before you insert
Reply With Quote
  #5  
Old 17-11-2009
Member
 
Join Date: May 2008
Posts: 2,008
Re: SQL problem if tuple is already in the database

select variable := count(id)
from thetableinquestion
where ...

you look at the variable value, if it is > 0 you have already inserted a value ...

In transactSQL, the "if" statements exists, I never know if it exists also in Oracle or not
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "SQL problem if tuple is already in the database"
Thread Thread Starter Forum Replies Last Post
How to use Listings and Tuple in Python? Dante i Software Development 4 04-01-2011 03:50 AM
How to insert value into tuple? MKAIF Software Development 5 22-02-2010 07:20 PM
What is tuple variables in SQL? Shophia_D Software Development 3 26-11-2009 12:51 PM
Problem With SQL Database Kusagra Software Development 3 26-03-2009 02:06 PM
Problem with DATABASE Celaeno Software Development 3 29-12-2008 07:33 PM


All times are GMT +5.5. The time now is 11:11 PM.