PL/SQL :: Last Or Oldest Record In A Database Table
Sep 6, 2012
I want to fetch oldest record in database table based on llast updated date ; I tried following queries..it gave me different results
SELECT EVENT_ID, RETENTION_TS
FROM ( SELECT EVENT_ID, RETENTION_TS, RANK() OVER (ORDER BY RETENTION_TS) RETENTION_TS_RANK
FROM EVENT )
WHERE RETENTION_TS_RANK <= 1;
select * from EVENT where rowid=(select min(rowid) from EVENT);
SELECT *
FROM (select * from EVENT ORDER BY RETENTION_TS) EVENT2
WHERE rownum <= 1
ORDER BY rownum DESC;
Above query takes like 10 to 20 mins to return me the record.
I have one table that have many records. For the maintenance purpose I like to delete old record based on Customer No.-That is Mobile NO.If each Customer have more than 300 records, I like to delete by everyday batch process.can't figure out how to apply each Customer No.(Specific Column), I could sort (order by few column - SAVE_DT or SMS_ARV_CLC) how to write this kind of query? I try rownum but no more progress.
Here is my table CREATE TABLE TM_060_SMS_TEST ( SMS_SEQ VARCHAR2(18 BYTE), SMS_RCV_CLC VARCHAR2(14 BYTE), CUST_NUM VARCHAR2(12 BYTE),
I like to increase speed to delete our table. Is it possible to use BULK COLLECT or FORALL this query? This is not single delete or select, maybe I got the error? Is it possible to use BULK method to this query?
delete from TM_060_SFS_TEST WHERE rowid in ( SELECT [code]......
Oracle 11gI have a large table of 125 million records - t3_universe. This table never gets updated or altered once loaded, but holds data that we receive from a lead company. I need to select records from this large table that fit certain demographic criteria and insert those into a smaller table - T3_Leads - that will be updated with regard to when the lead is mailed and for other relevant information. select records from this 125 million record table to insert into the smaller table.
I have tried a variety of things - views, materialized views, direct insert into smaller table...I think I am probably missing other approaches. My current attempt has been to create a View using the query that selects the records as shown below. Then use a second query that inserts into T3_Leads from this View V_Market. This is very slow. Can I just use an Insert Into T3_Leads with this query - it did not seem to work with the WITH clause? My Index on the large table is t3_universe_composite and includes zip_code, address_key, household_key.
CREATE VIEW V_Market asWITH got_pairs AS ( SELECT /*+ INDEX_FFS(t3_universe t3_universe_composite) */ l.zip_code, l.zip_plus_4, l.p1_givenname, l.surname, l.address, l.city, l.state, l.household_key, l.hh_type as l_hh_type, l.address_key, l.narrowband_income, l.p1_ms, l.p1_gender, l.p1_exact_age, l.p1_personkey, e.hh_type as filler_data, 1.p1_seq_no, l.p2_seq_no , ROW_NUMBER () OVER ( PARTITION BY l.address_key ORDER BY l.hh_verification_date DESC ) AS r_num FROM t3_universe e JOIN t3_universe l ON l.address_key = e.address_key AND l.zip_code = e.zip_code AND l.p1_gender != e.p1_gender
I developed one form having 3 blocks(query_find,Summary and detail). If i do some changes in the 3rd block then press F4 key it's prompting "Do u want to save the Changes?[YES/NO/CANCEL].
If i press "YES" it displays message "1 Records applied and Saved. But the record is not saved in the database.
I want to display my query select * from Clinical_Chart_CCHPI in non database item which is 1000 Char. I want to display that query when my forms execute.below is the test case.
i have master -detail form. both are database blocks.
i have inserted values for the master block bt not for the detail record my problem is.. "user should not be allowed" to move to next record of the master block before saving the current record
displaying an alter message after inserting a record into the database...i have designed form through forms builder.i want 2 display a alert message for showing a message 'RECORD INSERTED SUCCESFULLY ' along with the value entered in the textitem.this is the code i used
declare d varchar2(30); begin go_block('block3'); insert into patient(ptno,ptname,dob,age,ptel,blood,address)values(:block3.item4,:block3.item 5,:block3.item6,:block3.item7,:block3.item8,:block3.item9,:block3.item10); commit_form; set_alert_property('alert2',alert_message_text,'RECORD INSERTED'); d:=show_alert('alert2'); end;
BUT I NEED TO SHOW THE VALUE FROM THE TEXT ITEM INTO MY ALERT MESSAGE..
I have 3 tables in the Oracle database( emp, employee, emp1) which has following record values in it.
empidenamejob 7369, 'SMITH', 'CLERK'
I would like to list these 3 tables thru SQL/PLSQL, having the above record values combination. Also, the name of the columns could be different in all the tables i.e. name could be 'ename' in Emp table , and 'name' in Employee table. Is there way to do this in SQL or PLSQL ?
select * from test where col2=123 will give me null because It doesnt have any values, But can we display some harcoded value when I do not have anything ?
I looking for a cursor where i can find duplicate rows in a table. Like i have emp table in which i have deptno column. I have four with same deptno e.g 10. Now my requirement is after i get the 1 record with deptno 10 , i need a message that shows remaining 3 record as duplicate entry except the very first entry.
I have looked at the code you pointed me to, and have attempted to get it to work using a package, but I cant even get the package to compile..
CREATE OR REPLACE PACKAGE BODY trigger_api AS PROCEDURE tab1_row_change (p_numass IN varchar2, p_datcre IN date) IS BEGIN INSERT INTO tempjob (numass, datecre) VALUES (p_numass, p_datcre); END tab1_row_change; [code]....
Doing this process from code is not an option and MUST happen automatically via triggers.The mutating trigger error can sometimes be avoided: URL....
I have a table where there is no sequence maintained and there is no time stamp column to track the inserted record.How do I find the latest inserted record in the table.
I am trying to create a trigger which does the following : A flag in the initial able is set to Y. When this happens, the record needs to be inserted into a history table and then DELETED from the calling table.
It must happen in triggers, but I keep getting the mutating error.I have tried to use a Compound trigger, but with no luck and just dont really understand how to get this to work.
Doing this process from code is not an option and MUST happen automatically via triggers.
i need to find the parent and child from the table in this case the parent is Classics & Poetry and child is Literature & Anthologies..the way of getting only the parent and child record from this table.
CREATE TABLE ID_comments ( ID CHAR(10 BYTE) NOT NULL, S_COMMENTS VARCHAR2(255 BYTE), P_COMMENTS VARCHAR2(255 BYTE), C_COMMENTS VARCHAR2(255 BYTE) );
For each Id, I can have multiple records.
Below is the insert script of one of the ID:
Insert into ID_comments values ('0813654254','','JR/0813653606 single',''); Insert into ID_comments values ('0813654254','','JR/0813653606 single',''); Insert into ID_comments values ('0813654254','','JR/0813653606 SINGLE',''); Insert into ID_comments values ('0813654254','','JR',''); [code].......
Now I want to select only one record from this table for an ID, which will have "not null" values for s_comments,p_comments,c_comments columns. If for some ID , there is no "not null" row for any column, then pick up the "null" row/value for that column.
Is it possible to apply primary key on table having some duplicate record?I can do this by deleting duplicate record, But I don't want to delete exisitng data.
writing a trigger body. My requirement is i need to insert a new record in a task table when ever a new record is inserted into employee table.Here in the trigger i need to select the name of the employee in the last inserted row in employee table and insert the name in task table.I tried to write the code as below
insert into task(name, date, type) values ((select name from employee where emp_id=(select max(emp_id) from employee), sysdate, 'document'));
When i am trying to insert record using trigger, it is taking last but one record from the employee table.
