SQL & PL/SQL :: Find Date When Attributes Modified?
Sep 14, 2012
The requirement is to find the last date when either ATTRIB1 or ATTRIB2 were modified for each ID. There are many other columns in the table, but I'm not interested in other columns.
Test Case
SET LINES 100
SET PAGES 100
DROP TABLE test_log
/
CREATE TABLE test_log
[code]....
Expected Output
ID LAST_CHANGE_DATE
---------- --------------------
11 02-SEP-2012 10:58:32
35 05-AUG-2012 10:58:32
I have written the below query, to get the required output.
SELECT MAX(i_date) last_change_date
FROM
(
SELECT seq,id, attrib1, attrib2, i_date,
row_number() OVER (PARTITION BY attrib1 ORDER BY i_date) rn1,
row_number() OVER (PARTITION BY attrib2 ORDER BY i_date) rn2
I have a small question is it possible to find the details of a user who modified the structure of a table, including what command he ran to change the structure of the table?
I have multiple project databases where their tables are similar. I wanted to know just a single date from each project databases is modified/updated. If I run a query like select * from component order by eng_proj_id, chg_date desc then it will return many records sorted by their eng_proj_id and chg_date in their descending but I only need just a single record with last update from each project databases.
building SQL query to get the result as shown below.
Create Table Temp
CREATE TABLE TEMP ( CASEID NUMBER, SATUS VARCHAR2(1 BYTE), TRANS_DATE DATE ) Insert data
[Code]...
I want to build a query which should give output as shown below. Basically i want to select those rows which having minimum trans_date for a given CASEID & Status.
I am getting daily basis data from third party in excel format which i am converting into CSV format and then uploading into oracle tables using External tables.Now problem is that every time i getting the dates in diff format i.e. sometimes dd-mon-yyyy , dd/mm/yyyy etc.
Now every time i have to open my code and change it there ...to make it as oracle date format. IS there way i can find out format of date and based on format i can do operations with getting errors every time.
I am storing the TP(Excel date) date into varchar columns only and then varchar2 column value i m inserting/updating into date format using to_Date ().
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
I require to find the years between any given date range. For example what are the years between the dates '01/12/2010' and '01/02/2012'? Answer must be '2010,2011,2012'. how to code the query for this result?
My table has two date columns EFF_DT which is the start date and TERM_DT is the end date. The EFF_DT of the next record should be the next date of the TERM_DT record.
In the fourth record, the effective date should be 1-Oct-13 which is the next date to the last TERM_DT 30-Sep-13.As the is the break in the date, the output should show 15-Oct-13 sa the second start date.
Note: Refer to the PI_ID columns, there is a break in the date for the sale PI_ID 'ABC'.
Here I am trying to generate a pseudo column, so that the table with the pseudo column looks like as shown below. and I can use first_value and LAST_value by partitioning on the pseudo column to get the desired output.
1) CNT_VAL is the pseudo column: ----------------------------- CK_IDPI_IDEFF_DT TERM_DT CNT_VAL Mem1ABC1-Jan-1331-Mar-131 Mem1ABC1-Apr-1331-May-131 Mem1ABC1-Jun-1330-Sep-131
[code].....
My Query : ----------
I not getting the desired output here as the value in pseudo column is 3.
select CK_ID, PI_ID,EFF_DT,TERM_DT, (case when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) = 0 then to_char(case_CONT) when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) <> 0 then to_char(LAG(case_CONT,1) over (ORDER BY EFF_DT) + 1) else to_char(nvl(case_CONT,0))
say for example there is a column called 'date_txn' in a table .When i select that particular column it display output as 'June 2013'.But i want output "with date and time".
I have a table which contains some data. But we are dropped and recreated the same table several times. Now I wanted to know that when this table created initially. How to findout the date of creation(very first time).
I have to create a function. I need to find the max last logout date for each agent daily. For example, if an agent logged in for the first time at 9:00 and he logged out at 12:00 and he logged in again in 14:00 and he logged out at 15:00 the time I need my report to show is 15:00. How can I do that?In order to make it easiest for you to understand I am sending you this query:
select a.login as login2, To_Char(max(s.endtime), 'dd/MM/yyyy, HH24:MI:SS') as lastLogout from cti.agent a inner join cti.agentsessionlog s on s.agentid = a.agentid and To_Char(s.endtime) != '31-DEC-99 11.59.59.000000 PM' group by a.login;
This query returns the agent's login and the agent's last logout time. It works fine if I enter a date between but I cannot do that. If a use this query as it is and I try to export a report for 31/5 it shows as lastlogout the logout for 01/06 or 2/06. Is there a function I can use? I have a deadline.
I want to find the row with invalid day, month which are not matching with calendar day and month. Also the program should capture the data if the year <1900
with xx as (select 101 as ID, '24/05/1899' as create_date from dual union all select 101, '32/03/2012' from dual union all select 102 ,'30/02/2012' from dual union all select 101 , '29/02/2013' from dual
I given the table name,column name,datatype and sample record in the table. I have given the sample record for 01-jan-2008 to 8-Jan-2008, but in the real thing it will be for 30 years.
My Requirement:
For each class_no (202,203..), I need the missing date excluding weekends (sat, sun), I have provided the sample output below.
Table Name : ABC
Column Name : Class_no Data Type : Number Column Name : Class_DateData Type : Date
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';
MY requirment is: I want the first three nullable attributes. For Eg: If I have 60 columns in table, I need to fetch the first three null data in a row.
i have also another concern as per below xml file.
My requirement is to identify perticular node ,whose having PriorValue attribute present in <pi:the Actual_Comp_Change> tag,those record should return.
He had created an object type and wanted to populate it in a query. So far so good. But then he wanted to select some attributes from that object in the same query. Basically, he wanted to do something like this:
With his_view As ( select object_type( attr1, attr2) theobj From his_table Where ... ) Select theobj.attr1 From his_view
But somehow he was hitting ORA-00904: "THEOBJ"."ATTR1": invalid identifier over and over again.Here's a test script:
Create type mhe_type As Object( col1 Number , col2 Varchar2(30) ) / -- Basic select With mhe_view [code]...
Specify a correlation name, which is alias for the table, view, materialized view, or subquery for evaluating the query. This alias is required if the select list references any object type attributes or object type methods. link...
I have created a pl sql type as object with five attributes as below.Now im trying to insert only 3 attributes with the attribute names ( As like insert into statement with column names).
Create type address_test as object ( Streetno varchar2(20), Locality varchar2(20), City varchar2(20), Pin varchar2(7), Country varchar2(20)) [code]....
Is that possible to use attribute names in the insert statement of column object type ?
