We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Duplicate Check

GRChandrashekar
on
Medium Priority
363 Views
Last Modified: 2012-06-21
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
Comment
Watch Question

CERTIFIED EXPERT

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);

Author

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 !
Top Expert 2011

Commented:
- 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

Author

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
Top Expert 2012

Commented:
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.

Author

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)

Author

Commented:
Not a valid month
Top Expert 2011

Commented:
- use TO_DATE() function on 25/04/2011 2:36:38 PM and 29/04/2011 2:36:38 PM

Author

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
Top Expert 2012

Commented:
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
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Top Expert 2012

Commented:
so that would be TO_DATE('25/04/2011 2:36:38 PM','DD/MM/YYYY HH:MI:SS PM')
Top Expert 2011

Commented:
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 :)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.