topic Re: SQL to select / insert records in Operating System - HP-UX

SQL to select / insert records

Declan Heerey — Thu, 30 Sep 2004 08:28:06 GMT

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

Re: SQL to select / insert records

Jean-Luc Oudart — Thu, 30 Sep 2004 09:22:23 GMT

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

Re: SQL to select / insert records

Declan Heerey — Thu, 30 Sep 2004 09:33:53 GMT

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

Re: SQL to select / insert records

Jean-Luc Oudart — Thu, 30 Sep 2004 09:52:25 GMT

Sorry,

but could you answer :
is the added_date DATE format ?

Regards
Jean-Luc

Re: SQL to select / insert records

Declan Heerey — Thu, 30 Sep 2004 09:57:33 GMT

Jean Luc,

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

Declan

Re: SQL to select / insert records

Jean-Luc Oudart — Thu, 30 Sep 2004 10:09:54 GMT

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

Re: SQL to select / insert records

Declan Heerey — Thu, 30 Sep 2004 10:13:28 GMT

At the column level ?

Re: SQL to select / insert records

Jean-Luc Oudart — Thu, 30 Sep 2004 10:24:51 GMT

no.
in the query

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

Regards,
Jean-Luc

Re: SQL to select / insert records

Declan Heerey — Thu, 30 Sep 2004 10:29:43 GMT

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?

Re: SQL to select / insert records

Jean-Luc Oudart — Thu, 30 Sep 2004 10:35:21 GMT

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

Re: SQL to select / insert records

Declan Heerey — Thu, 30 Sep 2004 10:50:45 GMT

Jean Luc,

You are indeed correct. I have changed the DATE datatype to CHAR with the following syntax

WHERE TRUNC(TO_CHAR(max_date + 1, 'RRRR-MM-DD-SS-MI-HH24')) = TRUNC(a.added_date);

Seems to be working now!

Thanks

Re: SQL to select / insert records

Jean-Luc Oudart — Thu, 30 Sep 2004 10:55:55 GMT

Declan,

I suppose you can assign points that show the thread has been answered and solution found.

Regards
Jean-Luc
PS : no point here