SQL to select / insert records

[Declan Heerey]

I need to write some SQL or PL/SQL that will perform the following function;

Select from table A B C and insert into table D E F– which I can do quiet easily but I need to select the row that was created yesterday and insert it into the destination table

Also on the last day of the month I need to insert the previous days row and delete all of the other rows from the table

Tables A, B and C contain a column added_date (data format = 2004081906254600) which is what I am using to define the rows needed to insert into table D, E and F

Am I making sense?

All help appreciated

Decl

[Jean-Luc Oudart]

Declan,

is the added_date DATE format ?

then the process seems to be the same :
You always pick up rows from previous day , but on the last day of the month you must truncate the table beforehand.
This could be check with :
TO_CHAR(SYSDATE,'MM') <> TO_CHAR(SYSDATE + 1,'MM')

Regards,
Jean-Luc

[Declan Heerey]

Jean-Luc,

I should have mentioned that i need to keep the last record of the month to start the next months history log.

I suppose i could truncate the table and then reselect the data for the previous day.

At the moment i have something like this

CREATE OR REPLACE PROCEDURE history
AS
max_date DATE;
number_of_days_to_do NUMBER;
i NUMBER;

CURSOR lc_get_max_date
IS
SELECT MAX(added_date)
FROM table_history;

BEGIN

OPEN lc_get_max_date;
FETCH lc_get_max_date INTO max_date;
IF lc_get_max_date%NOTFOUND THEN
max_date:= TO_DATE('01-'||TO_CHAR(SYSDATE,'MON-RR'),'DD-MON-RR');
END IF;
CLOSE lc_get_max_date;

number_of_days_to_do := SYSDATE-max_date-1;

FOR i IN 1..number_of_days_to_do LOOP

INSERT INTO table_history(account_id, net_asset_value, status,
added_by, added_date, updated_by, updated_date)
SELECT account_id, net_asset_value, status, added_by, added_date,
updated_by, updated_date
FROM table
**** WHERE TRUNC(max_date)+1 = TRUNC(added_date);

END LOOP;
END;

BUT THE TRUNC line does not work!

Thanks for the help

[Jean-Luc Oudart]

Sorry,

but could you answer :
is the added_date DATE format ?

Regards
Jean-Luc

[Declan Heerey]

Jean Luc,

Sorry Jean Luc the answer is no the column is VARCHAR2(40)

Declan

[Jean-Luc Oudart]

I think the pb is you have 2 different datatypes :
WHERE TRUNC(max_date)+1 = TRUNC(added_date);

max_date DATE
added_date VARCHAR2

you need to convert one or the other

Regards,
Jean-Luc

[Declan Heerey]

At the column level ?

[Jean-Luc Oudart]

no.
in the query

where TRUNC(TO_CHAR( + 1, ')) = TRUNC(...)

Regards,
Jean-Luc

[Declan Heerey]

Apologies Jean Luc i'm having a paddy with this. I realised my mistake after i'd sent my last reply :o) What do you mean by format? VARCHAR2?

[Jean-Luc Oudart]

Well, let's illustrate with this
SQLWKS> select user
2> from dual
3> where trunc(SYSDATE)=trunc('20040930')
4>
where trunc(SYSDATE)=trunc('20040930')
*
ORA-00932: inconsistent datatypes


You cannot comapre 2 different datatypes, you must convert the char dataype to a date format or vice versa.

Regards
Jean-Luc