- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: SQL to select / insert records
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-30-2004 01:28 AM
тАО09-30-2004 01:28 AM
SQL to select / insert records
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-30-2004 02:22 AM
тАО09-30-2004 02:22 AM
Re: SQL to select / insert records
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-30-2004 02:33 AM
тАО09-30-2004 02:33 AM
Re: SQL to select / insert records
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-30-2004 02:52 AM
тАО09-30-2004 02:52 AM
Re: SQL to select / insert records
but could you answer :
is the added_date DATE format ?
Regards
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-30-2004 02:57 AM
тАО09-30-2004 02:57 AM
Re: SQL to select / insert records
Sorry Jean Luc the answer is no the column is VARCHAR2(40)
Declan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-30-2004 03:09 AM
тАО09-30-2004 03:09 AM
Re: SQL to select / insert records
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-30-2004 03:13 AM
тАО09-30-2004 03:13 AM
Re: SQL to select / insert records
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-30-2004 03:24 AM
тАО09-30-2004 03:24 AM
Re: SQL to select / insert records
in the query
where TRUNC(TO_CHAR(
Regards,
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-30-2004 03:29 AM
тАО09-30-2004 03:29 AM
Re: SQL to select / insert records
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-30-2004 03:35 AM
тАО09-30-2004 03:35 AM
Re: SQL to select / insert records
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