• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

Duplicate Check

I  have a table

CREATE TABLE BCLUB1868.SCHEME
(
  SCHEME_ID          NUMBER(20) CONSTRAINT NN_SCHEME_1 NOT NULL,
  DESCRIPTION        VARCHAR2(100 BYTE) CONSTRAINT NN_SCHEME_2 NOT NULL,
  FLAG               NUMBER(1)                  DEFAULT 0 CONSTRAINT NN_SCHEME_3 NOT NULL,
  CREATEDDATE        DATE CONSTRAINT NN_SCHEME_4 NOT NULL,
  CREATEDUSER_ID     NUMBER(5) CONSTRAINT NN_SCHEME_5 NOT NULL,
  EFFECTIVEDATEFROM  DATE CONSTRAINT NN_SCHEME_6 NOT NULL,
  EFFECTIVEDATETO    DATE CONSTRAINT NN_SCHEME_7 NOT NULL,
  POINTOFSALE_ID     NUMBER(5) CONSTRAINT NN_SCHEME_8 NOT NULL,
  MODIFIEDDATE       DATE,
  MODIFIEDUSER_ID    NUMBER(5)
)

EFFECTIVEDATEFROM  and EFFECTIVEDATETO   are datetime field

Now I need to ensure that no other record is inserted between the same dates and falling within the time (EFFECTIVEDATEFROM  and EFFECTIVEDATETO   )

Not able to understand what should be my query
0
GRChandrashekar
Asked:
GRChandrashekar
  • 5
  • 4
  • 3
  • +1
1 Solution
 
Swadhin RaySenior Technical Engineer Commented:
Try to find how many duplicates records do you based on both columns :

SELECT COUNT(*) FROM BCLUB1868.SCHEME
WHERE ROWID IN (SELECT ROWID FROM (SELECT ROW_NUMBER() OVER (PARTITION BY EFFECTIVEDATEFROM   ORDER BY EFFECTIVEDATEFROM  ) eff
                                       FROM   BCLUB1868.SCHEME)
                WHERE eff > 1);
0
 
GRChandrashekarAuthor Commented:
I dont need any duplicate to be inserted at all

so before insert i need to write a select statement by passing effective dates values !
0
 
OP_ZaharinCommented:
- you can use the following sql to check if any record exist for effectivedatevaluefrom and effectivedatevalueto on existing data between EFFECTIVEDATEFROM AND EFFECTIVEDATETO. you can then within your program to check IF RECFOUND > 0 to check if it returns any match records between those dates.  

SELECT count(*) AS RECFOUND FROM BCLUB1868.SCHEME 
WHERE (effectivedatevaluefrom BETWEEN EFFECTIVEDATEFROM AND EFFECTIVEDATETO) OR
(effectivedatevalueto BETWEEN EFFECTIVEDATEFROM AND EFFECTIVEDATETO) 

Open in new window

0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
GRChandrashekarAuthor Commented:
SELECT count(*) AS RECFOUND FROM BCLUB1868.SCHEME
WHERE ('25/04/2011 2:36:38 PM' BETWEEN EFFECTIVEDATEFROM AND EFFECTIVEDATETO) OR
('29/04/2011 2:36:38 PM' BETWEEN EFFECTIVEDATEFROM AND EFFECTIVEDATETO)



Not a valid month
0
 
johanntagleCommented:
OP_Zaharin's query should work but I would recommend against using count(*) unless you really need to know how many rows match the criteria.  Since you only need to check if there is at least one row, then I would do the following instead:

SELECT SCHEME_ID FROM BCLUB1868.SCHEME
WHERE (effectivedatevaluefrom BETWEEN EFFECTIVEDATEFROM AND EFFECTIVEDATETO) OR
(effectivedatevalueto BETWEEN EFFECTIVEDATEFROM AND EFFECTIVEDATETO)

Just get the first row.  If you retrieve one row, then you know you cannot insert your new entry.  

The problem with doing a count(*) is, what if the given effectivedatevaluefrom and effectivedatevalueto define a very big time range, resulting in thousands of matches, the database will look for all these rows when all you need is one.
0
 
GRChandrashekarAuthor Commented:
Fine but what about this error

SELECT  SCHEME_ID  FROM BCLUB1868.SCHEME
WHERE ('25/04/2011 2:36:38 PM' BETWEEN EFFECTIVEDATEFROM AND EFFECTIVEDATETO) OR
('29/04/2011 2:36:38 PM' BETWEEN EFFECTIVEDATEFROM AND EFFECTIVEDATETO)
0
 
GRChandrashekarAuthor Commented:
Not a valid month
0
 
OP_ZaharinCommented:
- use TO_DATE() function on 25/04/2011 2:36:38 PM and 29/04/2011 2:36:38 PM
0
 
GRChandrashekarAuthor Commented:
SELECT  SCHEME_ID  FROM BCLUB1868.SCHEME
WHERE (TO_DATE('25/04/2011 2:36:38 PM') BETWEEN EFFECTIVEDATEFROM AND EFFECTIVEDATETO) OR
(TO_DATE('29/04/2011 2:36:38 PM') BETWEEN EFFECTIVEDATEFROM AND EFFECTIVEDATETO)

SAME ERROR
0
 
johanntagleCommented:
to_date syntax is to_date(date_string, formatting_string).  You can check out what to put in the formatting_string here: http://www.techonthenet.com/oracle/functions/to_date.php
0
 
OP_ZaharinCommented:
SELECT  COUNT(1) FROM BCLUB1868.SCHEME
WHERE
(
TO_DATE('25/04/2011 2:36:38 PM', 'DD/MM/YYYY:HH:MI SSPM')
BETWEEN TO_DATE(EFFECTIVEDATEFROM, 'DD/MM/YYYY:HH:MI SSPM')
AND TO_DATE(EFFECTIVEDATETO, 'DD/MM/YYYY:HH:MI SSPM')
)
OR
(
TO_DATE('29/04/2011 2:36:38 PM', 'DD/MM/YYYY:HH:MI SSPM')
BETWEEN TO_DATE(EFFECTIVEDATEFROM, 'DD/MM/YYYY:HH:MI SSPM')
AND TO_DATE(EFFECTIVEDATETO, 'DD/MM/YYYY:HH:MI SSPM')
)
0
 
johanntagleCommented:
so that would be TO_DATE('25/04/2011 2:36:38 PM','DD/MM/YYYY HH:MI:SS PM')
0
 
OP_ZaharinCommented:
Grchandrashekar,
- you can format the TO_DATE formatting properly such as posted by johanntagle. sorry my formatting not properly done due to i'm posting from my mobile but it will still work :)
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now