SQL & PL/SQL :: LAST-VALUE Analytical Function?
Sep 11, 2012
I'm posting below test case in which I'm not able to understand output for LAST_VALUE function. I'm expecting maximum value for the salary in a department. Because I'm partitioning by department and ordering a partition as assending so being last value it should give me maximum value within a partition i.e. department in this case.
CREATE TABLE EMP_MST
(
EMP_ID NUMBER(5),
EMP_NAME VARCHAR2(30),
CONSTRAINT PK_EMP_MST PRIMARY KEY(EMP_ID)
[code]...
View 4 Replies
ADVERTISEMENT
Jul 11, 2011
Here is the test-table creation script:
CREATE TABLE TEST1 (AGG_DATE DATE, COL1 NUMBER(9), COL2 NUMBER(9), COL3 NUMBER(9));
Here is the test-data population script:
insert into TEST1 (AGG_DATE, COL1, COL2, COL3)
values (to_date('01-01-2012', 'dd-mm-yyyy'), 1, 1, 1);
[code]....
The problem is when I wrote an analytical query, it is giving the BEGIN_DATE and END_DATE by taking all the partition values together and so instead of the values above, it is creating an answer as follows:
Wrong Dataset
BEGIN_DATEEND_DATECOL1COL2COL3
1/1/20121/8/2012111
1/1/20121/8/2012111
1/1/20121/8/2012111
1/1/20121/8/2012111
1/4/20121/11/2012222
1/4/20121/11/2012222
[code]....
Only the last row is correct. What can I do to get the right answer as I know am falling short? Here is my current query:
SELECT MIN(AGG_DATE) OVER(PARTITION BY COL1, COL2, COL3) BEGIN_DATE,
MAX(AGG_DATE) OVER(PARTITION BY COL1, COL2, COL3) END_DATE,
COL1,
COL2,
COL3
FROM TEST1;
View 6 Replies
View Related
Oct 5, 2010
I need to calculate the sum of values over a period of exactly one month (including the current row). Now if I use a windowing clause of "range between interval '1' month preceding and current row", the total period length is 1 month plus one day (being the day in the current record).
Basically, I want to sum over a period starting at "add_months(startdate, -1) + 1" up until startdate of each row.
drop table window_tst;
create table window_tst
( id number primary key
[Code]....
So instead of having 01-feb going back to 01-jan, it should only include 02-jan till 01-feb
I could of course recalculate the period length back to a number of days for each row, but that is not really what I would prefer, as it would make the code rather unreadable.
View 5 Replies
View Related
Sep 18, 2012
I want to use Analytical function instead of group by clause for below query..
select
CASE
WHEN ADMT.SOURCESYSTEM ='CLU'
THEN COUNT(ADMT.TOTAL_COUNT)*5
ELSE COUNT(ADMT.TOTAL_COUNT)
END TOTAL_COUNT
from ESMARTABC.ABC_DRVR_MFAILS_TMP ADMT
group by ADMT.SOURCESYSTEM
View 1 Replies
View Related
Mar 8, 2012
can we use distinct keyword with the count and sum analytical functions?
View 5 Replies
View Related
Dec 22, 2012
how to delete duplicated records from a table without using row_id. I found the duplicated rows from a table using Analytical Function. But i could not use the Analytical function in the where condition.
My table(tab2) Structure is
DEPTNODEPT_NAMEEMPIDSEXID1
107jadf 1F1
40asdf 55
10purchase 2M2
10sales 3M3
30HR 4F4
I found the Duplicate Record by using the query
with a as
(select deptno,dept_name,empid,sex,id1,row_number()over(partition by deptno order by deptno) rnum from tab2)
select * from a where rnum >1
how to delete duplicate record .
View 19 Replies
View Related
Jun 10, 2010
I was reading a tutorial for analytical function and i found something like this
sum(princial) keep(dense_rank first order by d_date) over partition by (userid, alias, sec_id, flow, p_date)
How to translate this into simple queries / subquery? i am aware that analytical function are faster but i would like to know how this can translate to using query without analytical function.
View 12 Replies
View Related
Dec 6, 2012
Is there any way to apply the restriction on analytical functions, just like WHERE and HAVING .AS we know that we can apply the restriction on table by using WHERE and grouping functions by using HAVING clause .
For Ex: Departments wise count including all employees record :
SQL> select count(*) over(partition by deptno) dept_Count, ce.*
2 from scott.emp ce
3 order by deptno, job;
DEPT_COUNT EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
3 7934 MILLER CLERK 7782 1/23/1982 1300.00 10
3 7782 CLARK MANAGER 7839 6/9/1981 2450.00 10
3 7839 KING PRESIDENT 11/17/1981 5000.00 10
5 7788 SCOTT ANALYST 7566 4/19/1987 3000.00 20
[code]....
View 4 Replies
View Related
Sep 27, 2012
I have simplified this for ease of understanding. I have a Data column and a Month_ID column like this:
Values Month_ID
--------- -------------------------------------------------------
AAA 1
BBB 2
I split this out to values per year like this
Value_2011 Value_2012 Month_ID
-------------------------------------------------------------------------
AAA 1
BBB 2
Now i am trying to get the max(Value_2011) keep (dense_rank Last order by Month_ID) but i get a NULL. I can understand its because the Month_ID accomodates all years but i only need it to look at Month_ID for 2011 and return me the last dense_rank value, how can i achieve this?
I tried a couple of different methods like Last_Value() but i have group by in my original statement and i think analytical functions dont like GROUP by if they are not part of it. How can i achieve this?
View 2 Replies
View Related
Aug 10, 2013
I need to calcaulate the salary avarage for three days prior, leaving the current row. That should happen to every row moving back words.I have given all the details.
create table Employee(
ID VARCHAR2(4 BYTE) NOT NULL,
name varchar(20),
Start_Date DATE,
Salary Number(8,2),
mv_avg number(8,2)
[code]....
View 17 Replies
View Related
Jul 2, 2013
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.
My table looks like this.
Input Table:
-----------
CK_IDPI_IDEFF_DT TERM_DT
Mem1ABC1-Jan-1331-Mar-13
Mem1ABC1-Apr-1331-May-13
Mem1ABC1-Jun-1330-Sep-13
Mem1ABC15-Oct-1331-Dec-13
Mem1ABC1-Jan-1431-Mar-14
Mem1XYZ1-Apr-1430-Jun-14
Mem1XYZ1-Jul-1431-Dec-14
Expected Output:
----------------
CK_IDPI_IDEFF_DT TERM_DT
Mem1ABC1-Jan-1330-Sep-13
Mem1ABC15-Oct-1331-Mar-14
Mem1XYZ1-Apr-1431-Dec-14
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))
[code].....
Scripts:
--------------------
Create table lead_test(
CK_ID varchar2(10),
PI_IDvarchar2(10),
EFF_DTDate,
TERM_DT date);
[code].....
View 1 Replies
View Related
Nov 18, 2011
We've got a query which returns one row, but uses an IN statement. The IN statement links to more than one row in the subquery. When we use a combination of DISTINCT and an ANALYTICAL sum, the sum total is multiplied by the number of rows in the sub query. Remove the DISTINCT and we get a single value.
A simplified example of the problem is below.
I can't see how a query which returns a single row then returns multiple values with the addition of a DISTINCT. Removing the analytical sum also provides a single row, but we need this in the actual query we're running. So it seems to be some combination of DISTINCT, ANALYTICAL SUM and IN query is causing multiple values to be returned.
CREATE TABLE go_test_distinct1
(gtd_value NUMBER);
-- Three identical values
-- To replicate the three identical values returned by
[code].....
View 12 Replies
View Related
Nov 30, 2010
I have a table which has the attached data.
Sample data is here
LOGON_DATE NUMBER_OF_LOGINS
11/28/2010 02:00:001
11/28/2010 03:00:001
11/28/2010 04:00:002
11/28/2010 06:00:004
11/28/2010 07:00:002
11/28/2010 08:00:003
11/28/2010 09:00:006
[Code] ........
I am trying to do a report like this.
Date PeakUsersBetween6AMand6PM AVGUsersBetween6AMand6PM PeakUsersBetween6PMand6AM AVGUsersBetween6PMand6AM
Output should be
11/28/2010 25 11 49 27
11/29/2010...
I am using analytical function to do this, It throws an error range cannot be used for dates.
View 5 Replies
View Related
Jan 3, 2012
what is the purpose of over and partition by keywords in analytical functions
View 3 Replies
View Related
Jan 22, 2013
I am having a table with 5 lakhs transactions. I want to fetch the last balance for a particular date. So i have have returned a query like below.
SELECT curr_balance
FROM transaction_details
WHERE acct_num = '10'
[Code]...
This has to be executed for incrementing of 12 months to find the last balance for each particular month. But this query is having more cpu cost, 12 times it is taking huge time. how to remodify athe above query to get the results in faster way using analytical query. Whether this can be broken into two part in PL/SQL to achive the performance. ?
View 9 Replies
View Related
Feb 16, 2013
i have employee table i want to update salary with all employee 5 percent
View 4 Replies
View Related
Jun 15, 2012
Is anyway to create function based index for group function columns.
For example
select max(timestamp),min(age),averge(sal).... ... .. from tab;
View 5 Replies
View Related
Feb 4, 2013
I have the following C code:
class Factorial {
public:
int getVal (int a);
};
[code]....
/When I am trying to execute this function always get the ORA-06521. I changed the data types - but nothing changed.
Just in case, listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
[code]....
View 6 Replies
View Related
Mar 11, 2010
What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.
View 2 Replies
View Related
Jun 10, 2010
What is advantage of Deterministic function over normal function?
What is the diff B/W Deterministic function and normal function and also give me a example in which scenario we use Deterministic function?
View 4 Replies
View Related
Apr 4, 2007
i have a query where i am using the max function to find the most recent record. What i want to do is use that query as part of an insert statement into a different table, however, i don't want to insert the column that i used the max function on. Is there anyway to use the max function without having the column it is being used on showing in the results?
View 1 Replies
View Related
Mar 24, 2009
I have the following query.
sql
Original
- sql Code
SELECT class, COUNT (class)
FROM nv_table
WHERE l_id IN (
SELECT l_id
FROM n_table
WHERE id IN (1234)
GROUP BY class
ORDER BY class
SELECT class, COUNT (class)
FROM nv_table
WHERE l_id IN ( SELECT l_id FROM n_table WHERE id IN (1234)
GROUP BY class
ORDER BY class
It returns two columns: class and the total number of values in that class.
In some cases, there might not be any values under a certain class, so the query won't return anything.
In such a case I want the query to return 0. So what I want to see is:
class:A, COUNT(class):0
I'm trying to use the NVL function here, but either it doesn't work in this context, or it's not the correct syntax the way I'm writing it.
sql
Original
- sql Code
SELECT class, NVL(COUNT (class), 0)
FROM nv_table
WHERE l_id IN (
SELECT l_id
FROM n_table
WHERE id IN (1234)
[Code]....
View 3 Replies
View Related
Dec 16, 2011
I am creating an stored function which has to do some inserts in the meanwhile, and return after all the work has done, an UDT (2 or 3 columns of NUMBER datatype).
With this scenario I have an problem. The DML operations are not supported by and "SELECT * FROM Table(MyProc(args))". I have to use this "SELECT * FROM Table(MyProc(args))" because I need to pass the stored function results directly to an dataset.
Using a Stored Procedure it gives no errors, but the arguments must be passed like OUT params, and it is not what I want.
My question is:
Is there any other way to get a result (UDT) of an Stored Function (that makes Inserts) into a DataSet?
View 12 Replies
View Related
Jul 14, 2010
I've been tasked to parse tags from a string that look like the following:
{Date + XXX}
where XXX represents a numeric value. I have to replace this, including the brace characters with
SYSDATE + XXX
which will ultimately calculate SYSDATE plus the number of days suggested by XXX. The problem is that I am trying to use regexp_replace to achieve this goal but since XXX is completely arbitrary, I cannot search for it as a fixed value. So, ultimately, I would like to use a regular expression that ignores the numeric part of my search and only replaces the starting brace, the "Date + " part and the ending brace, leaving the numeric portion intact. I was trying to do something like the following
myString := regexp_replace(myString, '{(Date + [^[::digit:]]{1,})}', to_char(SYSDATE, 'FMMONTH DD, YYYY'));
in hopes of making it ignore the numeric part but it, instead, treats occurrences as a non match. Alternatively, the call below
myString := regexp_replace(myString, '{(Date + [[:digit:]{1,})}', to_char(SYSDATE, 'FMMONTH DD, YYYY'));
matches correctly but replaces the numeric portion as well, so I'm left with just today's date instead of the calculated future date...
View 14 Replies
View Related
Jun 14, 2011
can i have more than one pl sql blocks in a function and can i use the variable of one cursor into another cursor of the same function?
View 2 Replies
View Related
Oct 20, 2011
There is an 'emp' table with a column name as 'mgr' with datatype 'number'. following is the detailed description of the table:
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ---------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
Now when I run the 'select mgr from emp e;' query the output which I get is:
7902
7698
7698
7839
7698
7839
7839
7566
7698
7788
7698
7566
7782
Note: One value in between here is null, the required to me is that I want to print a character value 'President' in place of null .
View 7 Replies
View Related
Feb 22, 2010
I have two tables where I have to find the record for Max value of the column sap_pkid for every sap_id as in given table create script. This script is giving correct value but looking for a better way so that when data increses it doesn't hit the performance.
way where max can be avoided or a more tuned query .
create table tab1 (sapid number,
denid number);
create table tab2 (sap_pkid number ,sapid number,
denid number,
[code]...
View 2 Replies
View Related
Apr 13, 2013
I am creating a function to sum five numbers (less 1). Is it possible to have an array of numbers in an SQL function, and how would this be implemented?
Here is the screenshot of my output (I cannot embed links until 5 posts!): flic.kr/p/eaSHBP
CREATE OR REPLACE FUNCTION sumfivenumbers ( n1 NUMBER, n2 NUMBER, n3 NUMBER, n4 NUMBER, n5 NUMBER)
RETURN NUMBER
IS
Sumnums NUMBER;
BEGIN
SELECT SUM(n1+n2+n3+n4+n5-1) INTO Sumnums FROM DUAL;
DBMS_OUTPUT.PUT_LINE(Sumnums);
RETURN 1;
END sumfivenumbers;
/
SELECT sumfivenumbers(5,5,5,5,5) AS "Five Numbers less 1" FROM DUAL;
View 7 Replies
View Related
Jul 15, 2011
I am trying to create a function that when called will add the salary and commission a certain way to return an employee's annual salary.Here's my code
create or replace function Get_Annual_Comp
(Sal in number, Commission in number)
return number
as
[code]...
When I run the query, I get the proper rows return; however, my function does no calculation. If I input random numbers, I get the proper value returned. What I want is for my function to return the salary and commission of the employee specified in my select's where clause to be calculated as an annual salary.
View 9 Replies
View Related
Aug 5, 2010
if function having retrun statement, why we need to use out or inout parameters.
View 16 Replies
View Related
