SQL & PL/SQL :: Max Date Using Selected Columns - Millions Of Records
Dec 16, 2011
I am having a requirement like below,
Scene 1:
If duplicate records found for SSN,BWE with SAME DATE_CREATED than take the record with HIGHEST DATE_MODIFIED.
Table Structure:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014
123 01-JAN-2008 02-JAN-2009 03-JAN-2013
Output needed:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014
Scene 2:
If duplicate records found for SSN,BWE with different DATE_CREATED than take the record with HIGHEST DATE_CREATED.
Table Structure:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 02-JAN-2009 03-JAN-2014
123 01-JAN-2008 04-JAN-2009 03-JAN-2013
Output needed:
SSN BWE DATE_CREATE DATE_MODIFIED
123 01-JAN-2008 04-JAN-2009 03-JAN-2013
How to achieve this requirement. My source data has 25 million of records like this.
View 2 Replies
ADVERTISEMENT
Dec 8, 2010
There is a table in Database with millions of records and a query --- Select rowid, ANI, DNIS, message from tbl_sms_talkies where rownum<=:"SYS_B_0" ---- using the high CPU and also this query having high number of executions.
View 10 Replies
View Related
May 8, 2012
which is the fast way of inserting 60 millions of records from a view to a table.
method 1:
create table t_temp_table as select * from v_dump_data;
method 2:
through bulk collect
---Bulk_Collect With FORALL----------
DECLARE
TYPE srvc_tab IS TABLE OF t_temp_table%ROWTYPE;
l_srvc_tab srvc_tab := srvc_tab();
l_start_time NUMBER;
l_end_time NUMBER;
l_error_count NUMBER;
[code].....
View 4 Replies
View Related
Mar 6, 2013
somehow cannot understand why index is not used
in Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
1. Included only indexed column and got a perfect plan
explain plan for2 select s.x_cnt
3 from reported_summary s
4 where s.x_cnt>0;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2674489506
-------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-------------------------------------------------------
| 0 | SELECT STATEMENT | | 306 (8)|
|* 1 | INDEX FAST FULL SCAN| S_NUI01 | 306 (8)|
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("s"."x_CNT">0)
2. Included some other column and got TABLE ACCESS FULL
explain plan for2 select s.x_cnt,s.ru_id
3 from reported_summary s
4 where s.x_cnt>0;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2142873335
---------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2421 (3)|
|* 1 | TABLE ACCESS FULL| REPORTED_SUMMARY | 2421 (3)|
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("s"."x_CNT">0)
3. Included all other columns and got TABLE ACCESS FULL as well explain plan for2 select s.x_cnt,s.* 3 from reported_summary s 4 where s.x_cnt>0;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2142873335
---------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2421 (3)|
|* 1 | TABLE ACCESS FULL| REPORTED_SUMMARY | 2421 (3)|
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("s"."x_CNT">0)
View 5 Replies
View Related
Mar 6, 2012
I have 2 blocks, namely addsubject3rd and chosensubjects. I'm copying the selected records(this comes with checkboxes) from the 1st block to the second block. But what happened is that it copied all the records that are on the addsubject3rd. How can I copy only selected records(checkboxes) from addsubject3rd block to chosensubjects blocks?
View 5 Replies
View Related
Nov 23, 2012
From the previous forums i came to know about the "FILLER" option to skip any columns from loading into table.
In my case,i have columns say: col1,col2,col3,col4.....coln
Here i want to load only col3 and col4 and skip rest of the columns.I don't want to use FILLER option for each "n" number of columns to be skipped.Instead is there any options to load only the required 2 columns.
View 1 Replies
View Related
Jun 18, 2013
I am trying to run the following queries in my database.
Q-1)select * from item where trunc(approval_date)='21-MAY-13'
When Q-1 is executed it returned row that has approval_date of 21-MAY-13 from database.
Q-2)select * from item where approval_date=to_date('21-MAY-13')
When Q-2 is executed it says no rows selected.
difference or if any links related to this.
View 4 Replies
View Related
Dec 19, 2012
We have built an interactive report based on dynamic query. Normally, all the selected columns are shown in "Display" Section of the interactive report. We have a requirement to move some of the columns to the "Do not Display" when the search is done. Is it possible to do it programmatically ?
View 1 Replies
View Related
Mar 19, 2013
I have 3 types list items:
1) :1 Day -> 31
2) Month: 1 -> 12
3) Year :1990 -> 2013
I want to check that the user has selected a valid date .. if not an error message will be displayed, for example: 31-02-2013: will be rejected
View 9 Replies
View Related
Sep 20, 2011
i have a requirement to create a query which gets the same month from the previous year of the selected date .
Example : i select SEP-2011 ( as sysdate )
it will return the SEP-2010 as well .
here is the code which works fine with sysdate, dual :
SELECT
TO_CHAR(ADD_MONTHS(SYSDATE,-12),'MON-YYYY') TMLY, TO_CHAR(SYSDATE,'MON-YYYY') TMTY FROM DUAL;
in my schema table Ive got a filed named PERIOD_NAME (varchar2) , which holds date values in ( Mon-YYYY ) format (e.g Sep-2011)
am unable to apply the above code on it , i guess its the data type pf the field .
View 5 Replies
View Related
Jul 19, 2010
I got a form with few columns and a check_box. If the user selects n number of check boxes and click the button Save. The corresponding records should be inserted to XX_AP_CUSTOM table.
I have written the following below code in when-button-pressed trigger. With this i am able to insert only one record i.e where the current record indicator is there, even though multiple check-boxes(records) are selected.
declare
begin
IF :XXFBI_INV_QUOTE_ANAL_BLK.CHECK_BOX = 'Yes' THEN
--IF CHECKBOX_CHECKED(:XXFBI_INV_QUOTE_ANAL_BLK.CHECK_BOX) = TRUE Then
--app_insert.insert_record('WHEN-BUTTON-PRESSED');
insert into xxfbi.XXFBI_INV_QUOTE_ANAL(Item,
[code]......
I know that i have to use last_record and first_record and for loop to insert multiple selected records, but dont know how to do it.
View 5 Replies
View Related
Nov 24, 2011
Load the selected records from the flat file using SQL*Loader.
I have a flat file it's having 100 records, I want to load first 10 records from the file using SQL*LOADER.
View 8 Replies
View Related
Dec 14, 2007
I joined the forum just today, i need some tips on deleting the millions of rows from a huge table having 25 millions of rows.
View 4 Replies
View Related
Apr 27, 2013
create table call(id number(10) primary key,mobile_no number(10), other_no number(10), call_type varchar2(10),call_date_time date, duration number(10));
insert into call values(1,9818764535,9899875643,'IN','24-APR-13 02:10:43',10);
insert into call values(1,9818764535,9898324234,'IN','24-APR-13 05:06:78',10);
insert into call values(1,9818764535,9215468734,'IN','24-APR-13 15:06:78',10);
insert into call values(1,9818764535,9899875643,'OUT','25-APR-13 01:06:78',10);
insert into call values(1,9899875643,9899875643,,'OUT','25-APR-13 22:06:78',10);
Query : need to find first and last call of '9818764535' mobile number and of call_date between '24-apr-13' and '25-apr-13';
Result :
date ,mobile_no , other_no, call_type, duration
24/apr/13 , 9818764535,9899875643,'IN',10
24/apr/13 ,9818764535,9215468734,'IN',10
[Code]....
View 5 Replies
View Related
Jul 2, 2011
how to display check box selected records in one column of form when push button is pressed.
View 1 Replies
View Related
Feb 8, 2013
I want to learn how to make columns out of records?
View 8 Replies
View Related
Oct 8, 2010
I need to compare records in the two columns (varchar). I need to find almost the same names from that columns.
For instance:
'MAPE STUD' <-> MAPE STUDY
And I should see that these records are the same because different is only in the one word.
View 31 Replies
View Related
May 3, 2012
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
"CORE10.2.0.5.0Production"
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
See attached file for creation script and data load.Each staff member is required to complete at least one task every three years. The source table contains an EID (aka User ID) and a date column for each task with a date of when the task was completed. If a task has never been started/completed the date value is "NULL".
If a row looks like this:
EID,DATE01,DATE02,DATE03,DATE04,DATE05,DATE06,DATE07,DATE08,DATE09,DATE10,DATE11,DATE12,DATE13
68,NULL,11/10/2009,5/3/2010,4/22/2012,NULL,NULL,4/14/2010,NULL,NULL,NULL,NULL,4/14/2010,4/14/2010
The the result set would look like this:
EID,MaxDate,Within_Last_3_Years
68,4/22/2012,'YES'
The result set will show the EID, date of latest task completed and if the task was completed within the last 3 years from given date (for example June 30, 2012).
View 4 Replies
View Related
Feb 26, 2013
I need to fetch Data from a table X where current date(Sysdate) lies between the datecolumns Active_From and Active_To.
Active_From and Active_To are date columns.
Create table X(
ID number,
Active_From date,
Active_To date
)
Insert into X values (1, sysdate-3,sysdate + 3);
Insert into X values (1, sysdate-2,sysdate + 3);
Insert into X values (1, sysdate-3,sysdate +3);
View 5 Replies
View Related
Jul 3, 2012
I need to display the record when all the columns have matching records,If one of them doesn't match then it should not be displayed
The following is the example
WITH t1 as
(select 159435 ky from dual)
,t3 as
(select 78 id ,'Z-' rk,'SL' cd from dual union all
select 78 id ,'Z+' rk,'SL' cd from dual union all
select 78 id ,'Z-' rk,'SL' cd from dual union all
[code].....
In the above data bg.rk= 'Z-' but one of the record in T3 is having Z+ ,So this should not be displayed (same condition with column CD) in this example cd column in both table matches I tried like above query but i'm getting the record.
View 8 Replies
View Related
Jul 27, 2010
I have two columns in database
INPUTDATE DDMMYYYY
and
OUTPUTDATE YYYYMMDD
I want to compare both the columns because of format of columns i am getting problem to compare.
View 2 Replies
View Related
Jun 26, 2013
I want to do 'ORDER BY' by concatenating the two columns(date +varchar2).But not working and geting the error - ORA-01855: AM/A.M. or PM/P.M. required
SQL> create table dat2 (
mod_date date,
am_pm varchar2 (10) ) ; 2 3
Table created.
SQL> INSERT INTO DAT2 ( MOD_DATE, AM_PM ) VALUES (
TO_Date( '06/05/2003 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), '7:25AM'); 2
1 row created.
[code]...
ERROR at line 2:
ORA-01855: AM/A.M. or PM/P.M. required
View 25 Replies
View Related
May 25, 2011
As we know that date datatype can store both date part and time part. If I specify the Date format for my database as 'DD-MM-YYYY HH@$:MI:SS' can i ensure i anyways for a particular columns in the database containing date values the format is 'DD-MM-YYYY' i.e without the time part.Can we specify seperate date formats for specific columsn in database during table creation?
View 1 Replies
View Related
Sep 6, 2012
Oracle version : 11.2.0.3
Some DBA_views are huge with lots of columns ; a DESC command's output becomes less readable ; example DESC command's output of V$SESSION view.
Lets say I want to see only the date columns in a DBA_view or V$view ; How can I do that in a DESC like output or similair ?
View 2 Replies
View Related
Jul 17, 2012
write a query to delete similar records in particular fields(columns) in different tables.
View 2 Replies
View Related
Nov 16, 2012
create table ref_tbl (col1 varchar2(10),
col2 varchar2(10),
QUEST_ID_1 varchar2(10),
ANS_ID_1 varchar2(10),
QUEST_ID_2 varchar2(10),
ANS_ID_2 varchar2(10)
);
[code]...
Now i need to write a query to transpose the Quest_id and ans_id columns into rows for matching records...my output should be like
CUS_IDCOL1COL2QUEST_IDANS_ID
------------------------------------------------
1PMP1234Q101A1234
1PMP1234Q102A2345
1STR2345Q201A2234
2PMP1234Q101A1234
2PMP1234Q102A2345
2STR2345Q201A2234
View 11 Replies
View Related
Jan 26, 2011
PART_REF REGISTERED_BYREGISTERED_DATE
DCA544519 SVK 1/3/2011
DCA544520 SJA 1/3/2011
DCA544539 SJA 1/3/2011
DCA544572 THS 1/3/2011
DCA544608 GKA 1/3/2011
DCA544610 GKA 1/3/2011
DCA544611 THS1 1/3/2011
I just want a to get the total number of records registerd on particular date by particular user...
View 4 Replies
View Related
May 18, 2011
I am trying to get records from a table based on date column. I should retrieve records if the date column is NULL
and if it is not null ,i shud get the records of MAX date.
How would i do this. Below is my query,by which i can get records based on either maxdate or Null date
select c.id, c.status,c.u_gr_code,
from
(
select id, status,u_gr_code,date_col,
max(record_valid_from) over (partition by entity_user_gr_id, status_id) max_date
[code].........
View 10 Replies
View Related
Feb 11, 2013
I am trying to delete the one month data from the table , which contains the end customer sales data.The total data count in the table is 30 crores. And the data of one month is nearly 10 Crores.I am using oracle 10g , The date field to be used in the condition of delete is indexed.
best way to delete such huge data count.
View 2 Replies
View Related
Apr 13, 2004
I have two tables
table_1
--Emp_id--|--Sup_id--|Sup_name|--Date--|
-------------------------------------------------
--00001--|--00005 --|---ABCD--|01-MARCH-2004
--00002--|--00006 --|---BCDE--|02-MARCH-2004
--00003--|--00007 --|---CDEF--|03-MARCH-2004
--00001--|--00008 --|---DEFG--|04-APRIL-2004
--00003--|--00009 --|---EFGH--|05-APRIL-2004
table_2
--Emp_id--|Emp_name|
--------------------------------
--00001--|--QWER--|
--00002--|--ASDF--|
--00003--|--ZXCV--|
--00004--|--POIU--|
table_1 contain records on employee and the supervisor they are under at a certain date.
As some employee(00001 & 00003) have a different supervisor from different date, I'll like to extract from table_1 the record of each employee in the table that only contain the supervisor info on the most recent date.And from table_2, i'll like to extract the employee's name. These records extracted from both the tables would the be put into a new table,table_3
Example: For employee 00001, only extract record that have the most recent date which is 04-APRIL-2004 and not on 01-MARCH-2004
table_3
Emp_id|Emp_name|Sup_id|Sup_name|Date|
------------------------------------------------
00001 |--QWER--|00008 |--DEFG---|04-APRIL-2004
00002 |--ASDF-- |00006 |--BCDE---|02-MARCH-2004
00003 |--ZXCV-- |00009 |--EFGH---|05-APRIL-2004
How to write an SQL statement to perform this?
View 6 Replies
View Related
