We have employee salary table which will have salary of an employee on daily basis (only working days). Below is the table structure:
CODEemp_salary
----------
emp_id NUMBER(15) NOT NULL
effective_date DATE NOT NULL
salary NUMBER(15) NOT NULL
Primary key - emp_id, effective_date..This table is yearly partitioned...I have to find out how long the salary is not changed for an employee from given date, and last salary. I am using below query to do this:
CODEWITH salary_tab AS
(SELECT effective_date, salary,
(CASE
WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) =
salary
[code]....
For emp_id 1, if we ran this query for 10/31/2011, then it has to compare the 10/31 salary with 10/29 and do the same until the salary mismatches. In this case, salary salary mismatch occurs on 10/20, so the stale salary period is from 10/31 to 10/21 which is 7 days.Below query will give that result:
CODE
WITH salary_tab AS
(SELECT effective_date, salary,
(CASE
WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) =
salary
We have employee salary table which will have salary of an employee on daily basis (only working days). Below is the table structure:
emp_salary ---------- emp_id NUMBER(15) NOT NULL effective_date DATE NOT NULL salary NUMBER(15) NOT NULL Primary key - emp_id, effective_date This table is yearly partitioned
I have to find out how long the salary is not changed for an employee from given date, and last salary. I am using below query to do this:
WITH salary_tab AS (SELECT effective_date, salary, (CASE WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) = salary ) THEN 0 ELSE 1 END ) changed_ind FROM emp_salary WHERE emp_id = 12345 [code]....
The cost of this query is 1677 and it is taking around 60 msec to complete. When I run this query for around 2000 employees in a loop it is taking around 3 minutes to complete.
The main bottleneck of this query is in the with clause where I am processing the entire history instead of stopping after first change.
I am trying to set the min date if there is no gap between dates.compare previous date2 value with current date1,if they are same then my new date will be min(date1).
insert into a values(1,3,'Y',5); insert into a values(2,2,'Y',4); insert into a values(3,3,'N',3); insert into a values(4,4,'Y',3); insert into a values(5,1,'N',6); insert into a values(6,2,'N',5); insert into a values(7,2,'Y',4); insert into a values(8,3,'N',2); insert into a values(9,4,'Y',2); insert into a values(10,1,'Y',3); . SQL>select * from v$version; Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
My logic should have the output as follows without 5th column: sourcerowleveldpnqtyremark13Y522Y4This row in main table shouldn't be displayed in the query result as it has
dpn='N'44Y3*3=9In this row qty=9 will bedisplayed.
It will compare the value of level column with its previous row level col,if it is the parent of the current(ex.current is 4 and previous is 3 parent of 4),then it will check the dpn of previous row ,if dpn='N' then qty of parent will be multiplied with qty of current row and displayed under qty column.this row will not be displayed as dpn='N'this row will not be displayed as dpn='N'72Y4*6=24in its previous row level value is same so it will check the previous to previous row where level is 1(parent of current row) and dpn='N' ,then it will multiply the qty of that row with current row and display the value in qty column.this row will not be displayed as dpn='N'94Y2*2=4In this row qty=4 will be displayed.It will compare the value of level column with its previous row level col,if it is the parent of the current(ex.current is 4 and previous is 3 parent of 4),then it will check the dpn of previous row ,if dpn='N' then qty of parent will be multiplied with qty of current row and displayed under qty column.101Y3It will not check for the previous rows as level 1 doesn't have any parent.
I need to update the current column with sum of the previous column values. Following are the creation scripts
DROP TABLE TEST_LOG; CREATE TABLE TEST_LOG (
[Code]....
Above query is working fine to retrieve the previous column values.But when we are updating the SUM_PRE_COLS column with those values it's not working fine.
I tried by using the following query
UPDATE TEST_LOG T SET SUM_PRE_COLS = ( SELECT LAG(T2.KEY0, 1, 0) OVER(ORDER BY T2.KEY0) + LAG(T2.KEY1, 1, 0) OVER(ORDER BY T2.KEY0) FROM TEST_LOG T2 WHERE T2.ROWID= T.ROWID);
We want to find out difference of data for some tables between current day & previous day. We can use query with minus operation but it will take lot of time since table size is in range from 200 to 500 GB. We have to do this exercise every day.
Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).
yr mnth location volume actual_missing expected_missing estimated_missing --------------------------------------------------------------------------------------------------------------------------------- 2013 January loc1 48037 24 57 2013 February loc1 47960 3660 53 24 2013 March loc1 55007 78 57 28 2013 April loc1 54345 72 58 77The code:
UPDATE missing_volume g
[Code]....
The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:
yr mnth location volume actual_missing expected_missing estimated_missing --------------------------------------------------------------------------------------------------------------------------------- 2013 January loc1 48037 24 57 2013 February loc1 47960 3660 53 2013 March loc1 55007 78 57 2013 April loc1 54345 72 58 77
why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).
I need to verify if the current date is grater than the 15th of the current month. If its grater than the 15th of the current month i need to do an action or if else its lesser than 15th of the current month i need to do an other operation.
I've 2 identical tables of 300+ columns each,and each of table is having pk as combination of 2 fields.There are some fields where data is not matching. need a query which will use these two tables and return the following-
We are trying insert records from a select query in to temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?
It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.
Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.
CREATE OR REPLACE TYPE MF_RECORD_TYPE IS OBJECT (name VARCHAR2(6), . . . CREATE OR REPLACE TYPE MF_TABLE_TYPE IS TABLE OF mf_record_type;
Then in my stored procedure:
v_mf_record MF_TABLE_TYPE := MF_TABLE_TYPE(); . . OPEN mf_detail; FETCH mf_detail BULK COLLECT INTO v_mf_record; CLOSE mf_detail;
[code]....
I am getting the error: PLS-00386: type mismatch found at 'V_MF_RECORD' between FETCH cursor and INTO variables.
The thing is, that type I defined is like 90 columns, and I'm trying to avoid having to list all those columns individually in the FETCH or when I retrieve the data.
I had two installations of Oracle on my server (10.2.0.4 and 11.2.0.1) under different linux users. The Databases used different listeners on different ports.
I upgraded my Oracle 11g to 11.2.0.3 successfully two weeks ago. However today I just checked that I couldnt reload my Oracle 10g Listener. On further analysis (lsnrctl status) I discovered that my 10g listener's version was "TNSLSNR for Linux: Version 11.2.0.3.0" . My Start Date from lsnrctl gives me the date when my Oracle 11g was upgraded.
Now I have tried to reload my 11g listener which works normally. I cannot reload my 10g listener (TNS-01190: The user is not authorized to execute the requested listener command). When i run ps command there is no process on my server for my 10g listener. I can only see my 11g listener process after running ps command. However everybody can ping my databases on Oracle 10g but i cannot identify or kill my listener process even on server level.
I am using an query to fetch the data from oracle DB and fill dataset using oledb dataadapter in ASP.net.When i run the same query in PL/SQL i am getting 14952 records,but when i am filling it to dataset i am getting only 13700 records.
ORA-31180: DOM Type mismatch in invalid PL/SQL DOM handle
I am getting this error while debugging one of the package in PL/SQL developer or in TOAD. Is there any setting which we need to perform at the database level to debug the packages which have the code related to XML DOM. I am asking as the same code works exactly fine at one of the test database and it gives the above error on the other database.
How to get the previous value of row with calling function to add value in SELECT statement for the row value.
Consider the example Table A1 having column a with values 1,NULL,NULL,NULL
SELECT CASE WHEN a IS NULL THEN (prev_row_value+function_return_Value) ELSE a END as A from A1
And my result-set should be like
a ---------------------- 1 1+(Return Value Of Function) Prev_Row_Value+(Return Value Of Function) Prev_Row_Value+(Return Value Of Function) Below is sample code but doesn't fulfill my criteria
I need to get data from a table in which dates is equal from previous month. The dates in this table has a formula DD-MMM-YY (CRE_DTTM is the name for date column).
I've already achieve getting data from the previous month by using this formula:
My problem now is what if the current month is for example JAN 2011.. I need to get the data from DEC 2010. How can I query the previous year in this case?
