Using Interval datatype in oracle 9i

[mariocq]

Hello:

I am trying to run a script that delete messages from a table older than six months:

--------
ALTER SESSION SET TIME_ZONE = DBTIMEZONE;

SELECT SEQUENCEID FROM MESSAGE_TRACE WHERE CURRENT_TIMESTAMP - MESSAGE_TRACE.TIME_STAMP > INTERVAL '6' MONTH;
--------------
When I run the script I got the following error:

--------

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected INTERVAL got INTERVAL
----------


Oracle 9.2.0.6.0

Regards.

 

[Thargy]

mariocq,

you can't get an interval by magic, you have to declare the table from which you are selecting, and declare the interval type of the relevant column, when you create the table.

Try running the following script, as a demo of what I mean. In your question, you've got two timestamps, so all you can do is date arithmetic. Without an interval data type, you can't do interval arithmetic.

The delete statement below will remove all records where there's a timestamp less than six months old.

CREATE TABLE MARIOCQ
	(
	mariocq_id INTEGER PRIMARY KEY,
	CURRENT_DATE DATE,
	subsequent_date DATE
	);
	
INSERT INTO MARIOCQ
VALUES (1, TO_DATE('04/03/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/07/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MARIOCQ
VALUES (2, TO_DATE('04/03/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/07/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MARIOCQ
VALUES (3, TO_DATE('04/03/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/07/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MARIOCQ
VALUES (4, TO_DATE('04/03/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/07/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MARIOCQ
VALUES (5, TO_DATE('04/03/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/07/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

INSERT INTO MARIOCQ
VALUES (6, TO_DATE('04/03/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/24/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MARIOCQ
VALUES (7, TO_DATE('04/03/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/24/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MARIOCQ
VALUES (8, TO_DATE('04/03/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/24/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MARIOCQ
VALUES (9, TO_DATE('04/03/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/24/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
INSERT INTO MARIOCQ
VALUES (10, TO_DATE('04/03/2006 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/24/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

SELECT ROUND(MONTHS_BETWEEN(CURRENT_DATE, subsequent_date)) months
  FROM MARIOCQ;
  
DELETE FROM MARIOCQ
 WHERE ROUND(MONTHS_BETWEEN(CURRENT_DATE, subsequent_date)) < 6;

Don't forget to 'drop table mariocq' at the end.

Regards

Tharg

Grinding away at things Oracular

 

[mariocq]

Hello:

This script seems to do what I want:
--------------
SELECT time_stamp FROM MESSAGE_TRACE WHERE CURRENT_TIMESTAMP > MESSAGE_TRACE.TIME_STAMP + INTERVAL '92' day;
-------------------------

Thanks anyway.

Regards,
Mario.