Home » SQL & PL/SQL » SQL & PL/SQL » query to get first and last values (merged) (10g R2 )
icon11.gif  query to get first and last values (merged) [message #439467] Sun, 17 January 2010 05:41 Go to next message
micro_oracle
Messages: 41
Registered: December 2007
Location: UAE
Member

I need a query TO GET a last month values fo each employee

Ok go thru this code :

create table EMP_MONTHLY_ACCRUAL 
(
EMP_CODE	 VARCHAR2(6) NOT NULL,
EMP_ACCR_YEAR		NUMBER(4),
EMP_ACCR_MONTH		NUMBER(2),
EMP_ACCR_MON_VALUE	NUMBER(8,2),
EMP_ACCR_RUN_TOTAL	NUMBER(9,2));


INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100001',2009,01,750.00,750.00)	
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100001',2009,02,700.00,1450.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100001',2009,03,750.00,2200.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100001',2009,04,650.00,2850.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100001',2009,05,550.00,3400.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100001',2009,06,700.00,4100.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100002',2009,01,750.00,750.00) 

INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100002',2009,02,750.00,1500.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100002',2009,03,750.00,2250.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100002',2009,04,750.00,3000.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100002',2009,05,750.00,3750.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100002',2009,06,750.00,4500.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100002',2009,07,750.00,5250.00) 

INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100003',2009,01,750.00,750.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100003',2009,02,700.00,1450.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100003',2009,03,650.00,2100.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100003',2009,04,600.00,2700.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100003',2009,05,850.00,3550.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100003',2009,06,800.00,4350.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100003',2009,07,750.00,5100.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100003',2009,08,700.00,5800.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100003',2009,09,650.00,6450.00) 

INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100004',2009,01,1050.00,1050.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100004',2009,02,900.00,1950.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100004',2009,03,850.00,2800.00) 
INSERT INTO EMP_MONTHLY_ACCRUAL VALUES ('100004',2009,04,800.00,3600.00) 

COMMIT;


EMP_CODE	EMP_ACCR_YEAR	EMP_ACCR_MONTH	EMP_ACCR_MON_VALUE	EMP_ACCR_RUN_TOTAL

100001	2009	1	750	750
100001	2009	2	700	1450
100001	2009	3	750	2200
100001	2009	4	650	2850
100001	2009	5	550	3400
100001	2009	6	700	4100
100002	2009	1	750	750
100002	2009	2	750	1500
100002	2009	3	750	2250
100002	2009	4	750	3000
100002	2009	5	750	3750
100002	2009	6	750	4500
100002	2009	7	750	5250
100003	2009	1	750	750
100003	2009	2	700	1450
100003	2009	3	650	2100
100003	2009	4	600	2700
100003	2009	5	850	3550
100003	2009	6	800	4350
100003	2009	7	750	5100
100003	2009	8	700	5800
100003	2009	9	650	6450
100004	2009	1	1050	1050
100004	2009	2	900	1950
100004	2009	3	850	2800
100004	2009	4	800	3600



then I need I need to have a query to retreive the following result :


EMP_CODE EMP_ACCR_YEAR EMP_ACCR_MONTH EMP_ACCR_MON_VALUE EMP_ACCR_RUN_TOTAL

100001	2009	6	700	4100
100002	2009	7	750	5250
100003	2009	9	650	6450
100004	2009	4	800	3600



and again what if I need the first row for each employee
like :

100001	2009	1	750	750
100002	2009	1	750	750
100003	2009	1	750	750
100004	2009	1	1050	1050


Can any one help me to get this queries,
Thanks
Re: query to get first and last values (merged) [message #439469 is a reply to message #439467] Sun, 17 January 2010 05:54 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Hint: Try using PARTITION BY,ORDER BY,ROW_NUMBER
You should get the result you are looking for.


Read this article

Based on your test data, we can write fetch the data based on
emp_accr_month also. find max and min for the month and fetch the data

Regards,
Ved

[Updated on: Sun, 17 January 2010 06:08]

Report message to a moderator

Re: query to get first and last values (merged) [message #439470 is a reply to message #439467] Sun, 17 January 2010 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's one way, not the most efficient:
SQL> select distinct 
  2         EMP_CODE, EMP_ACCR_YEAR,
  3         first_value(EMP_ACCR_MONTH) 
  4           over (partition by EMP_CODE, EMP_ACCR_YEAR order by EMP_ACCR_MONTH 
  5           rows between unbounded preceding and unbounded following)
  6           first_month,
  7         first_value(EMP_ACCR_MON_VALUE) 
  8           over (partition by EMP_CODE, EMP_ACCR_YEAR order by EMP_ACCR_MONTH 
  9           rows between unbounded preceding and unbounded following) 
 10           first_value,
 11         first_value(EMP_ACCR_RUN_TOTAL) 
 12           over (partition by EMP_CODE, EMP_ACCR_YEAR order by EMP_ACCR_MONTH 
 13           rows between unbounded preceding and unbounded following) 
 14           first_total,
 15         last_value(EMP_ACCR_MONTH) 
 16           over (partition by EMP_CODE, EMP_ACCR_YEAR order by EMP_ACCR_MONTH 
 17           rows between unbounded preceding and unbounded following) 
 18           last_month,
 19         last_value(EMP_ACCR_MON_VALUE) 
 20           over (partition by EMP_CODE, EMP_ACCR_YEAR order by EMP_ACCR_MONTH 
 21           rows between unbounded preceding and unbounded following) 
 22           last_value,
 23         last_value(EMP_ACCR_RUN_TOTAL) 
 24           over (partition by EMP_CODE, EMP_ACCR_YEAR order by EMP_ACCR_MONTH 
 25           rows between unbounded preceding and unbounded following) 
 26           last_total
 27  from EMP_MONTHLY_ACCRUAL 
 28  order by EMP_CODE, EMP_ACCR_YEAR 
 29  /
EMP_CO EMP_ACCR_YEAR FIRST_MONTH FIRST_VALUE FIRST_TOTAL LAST_MONTH LAST_VALUE LAST_TOTAL
------ ------------- ----------- ----------- ----------- ---------- ---------- ----------
100001          2009           1         750         750          6        700       4100
100002          2009           1         750         750          7        750       5250
100003          2009           1         750         750          9        650       6450
100004          2009           1        1050        1050          4        800       3600

4 rows selected.

Regards
Michel
Re: query to get first and last values (merged) [message #439471 is a reply to message #439467] Sun, 17 January 2010 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's another one:
SQL> with 
  2    data as (
  3      select EMP_CODE, EMP_ACCR_YEAR, EMP_ACCR_MONTH, EMP_ACCR_MON_VALUE, EMP_ACCR_RUN_TOTAL,
  4             row_number() over(partition by EMP_CODE, EMP_ACCR_YEAR order by EMP_ACCR_MONTH) rn,
  5             count(*) over(partition by EMP_CODE, EMP_ACCR_YEAR) cnt
  6      from EMP_MONTHLY_ACCRUAL 
  7    )
  8  select EMP_CODE, EMP_ACCR_YEAR, 
  9         max(decode(rn,1,EMP_ACCR_MONTH)) first_month,
 10         max(decode(rn,1,EMP_ACCR_MON_VALUE)) first_value,
 11         max(decode(rn,1,EMP_ACCR_RUN_TOTAL)) first_total,
 12         max(decode(rn,cnt,EMP_ACCR_MONTH)) last_month,
 13         max(decode(rn,cnt,EMP_ACCR_MON_VALUE)) last_value,
 14         max(decode(rn,cnt,EMP_ACCR_RUN_TOTAL)) last_total
 15  from data
 16  group by EMP_CODE, EMP_ACCR_YEAR
 17  order by EMP_CODE, EMP_ACCR_YEAR 
 18  /
EMP_CO EMP_ACCR_YEAR FIRST_MONTH FIRST_VALUE FIRST_TOTAL LAST_MONTH LAST_VALUE LAST_TOTAL
------ ------------- ----------- ----------- ----------- ---------- ---------- ----------
100001          2009           1         750         750          6        700       4100
100002          2009           1         750         750          7        750       5250
100003          2009           1         750         750          9        650       6450
100004          2009           1        1050        1050          4        800       3600

4 rows selected.

Regards
Michel

[Updated on: Sun, 17 January 2010 06:25]

Report message to a moderator

Re: query to get first and last values (merged) [message #439474 is a reply to message #439469] Sun, 17 January 2010 06:12 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Please provide hint not a solution.
Re: query to get first and last values (merged) [message #439475 is a reply to message #439474] Sun, 17 January 2010 06:37 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
./fa/7176/0/

sriram Smile
Re: query to get first and last values (merged) [message #439552 is a reply to message #439470] Mon, 18 January 2010 05:20 Go to previous messageGo to next message
micro_oracle
Messages: 41
Registered: December 2007
Location: UAE
Member

thanks Michel


the query which involve first_value and last value it's working fine.

but the query which using with data .... is not working in Toad 8.6 and sqlplus. it gives the following erfror message " SQL statement doesn't return rows"

and in sqlplus the following happend

SQL>  with data as (select EMP_CODE, EMP_ACCR_YEAR, EMP_ACCR_MONTH, EMP_ACCR_MON_VALUE, EMP_ACCR_RUN
_TOTAL,
unknown command beginning "with data ..." - rest of line ignored.
SQL>                row_number() over(partition by EMP_CODE, EMP_ACCR_YEAR order by EMP_ACCR_MONTH) 
rn,
unknown command beginning "row_number..." - rest of line ignored.
SQL>                count(*) over(partition by EMP_CODE, EMP_ACCR_YEAR) cnt
unknown command beginning "count(*) o..." - rest of line ignored.
SQL>         from EMP_MONTHLY_ACCRUAL)
unknown command beginning "from EMP_M..." - rest of line ignored.
For a list of known commands enter HELP
and to leave enter EXIT.
SQL>     select EMP_CODE, EMP_ACCR_YEAR, 
  2             max(decode(rn,1,EMP_ACCR_MONTH)) first_month,
  3            max(decode(rn,1,EMP_ACCR_MON_VALUE)) first_value,
  4            max(decode(rn,1,EMP_ACCR_RUN_TOTAL)) first_total,
  5            max(decode(rn,cnt,EMP_ACCR_MONTH)) last_month,
  6            max(decode(rn,cnt,EMP_ACCR_MON_VALUE)) last_value,
  7            max(decode(rn,cnt,EMP_ACCR_RUN_TOTAL)) last_total
  8     from data
  9     group by EMP_CODE, EMP_ACCR_YEAR
 10     order by EMP_CODE, EMP_ACCR_YEAR ;
   from data
        *
ERROR at line 8:
ORA-00942: table or view does not exist




Re: query to get first and last values (merged) [message #439553 is a reply to message #439467] Mon, 18 January 2010 05:34 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
What version of oracle are you using?
Re: query to get first and last values (merged) [message #439554 is a reply to message #439552] Mon, 18 January 2010 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your SQL*Plus version must be of version 9 or more.

Otherwise, just put the data subquery as an inline view in FROM clause.

Regards
Michel
Re: query to get first and last values (merged) [message #439578 is a reply to message #439467] Mon, 18 January 2010 08:08 Go to previous messageGo to next message
micro_oracle
Messages: 41
Registered: December 2007
Location: UAE
Member

I have oracle 10.2 and sqlplus 10.2
Re: query to get first and last values (merged) [message #439594 is a reply to message #439578] Mon, 18 January 2010 09:44 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not possible you got this error with a SQL*Plus 10.2.
In addtion, SQL*Plus returns an error number with its message:
SP2-0042: unknown command "tttt" - rest of line ignored.

Regards
Michel
Re: query to get first and last values (merged) [message #439643 is a reply to message #439578] Mon, 18 January 2010 21:37 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Yes, it is not possible to the error for "with" clause in your version.even if you have spaces or enter character in your code...(normally if you have line breaks then it will through unknown command error but here thats not the situation)...

see the below...
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jan 19 08:50:43 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>       with  data as ( select EMP_CODE, EMP_ACCR_YEAR, EMP_ACCR_MONTH, EMP_ACCR_MON_VALUE, EMP_A
CCR_RUN_TOTAL,
  2  
SQL> row_number() over(partition by EMP_CODE, EMP_ACCR_YEAR order by EMP_ACCR_MONTH) rn,
SP2-0734: unknown command beginning "row_number..." - rest of line ignored.
SQL> 
SQL>               count(*) over(partition by EMP_CODE, EMP_ACCR_YEAR) cnt
SP2-0734: unknown command beginning "count(*) o..." - rest of line ignored.
SQL> 
SQL>        from EMP_MONTHLY_ACCRUAL 
SP2-0734: unknown command beginning "from EMP_M..." - rest of line ignored.
SQL> 
SQL>      )
SP2-0042: unknown command ")" - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SQL>    select EMP_CODE, EMP_ACCR_YEAR, 
  2            max(decode(rn,1,EMP_ACCR_MONTH)) first_month,
  3            max(decode(rn,1,EMP_ACCR_MON_VALUE)) first_value,
  4            max(decode(rn,1,EMP_ACCR_RUN_TOTAL)) first_total,
  5            max(decode(rn,cnt,EMP_ACCR_MONTH)) last_month,
  6            max(decode(rn,cnt,EMP_ACCR_MON_VALUE)) last_value,
  7            max(decode(rn,cnt,EMP_ACCR_RUN_TOTAL)) last_total
  8     from data
  9     group by EMP_CODE, EMP_ACCR_YEAR
 10     order by EMP_CODE, EMP_ACCR_YEAR 
 11     /
   from data
        *
ERROR at line 8:
ORA-00942: table or view does not exist


SQL>       with  data as ( select EMP_CODE, EMP_ACCR_YEAR, EMP_ACCR_MONTH, EMP_ACCR_MON_VALUE, EMP_A
CCR_RUN_TOTAL,
  2  row_number() over(partition by EMP_CODE, EMP_ACCR_YEAR order by EMP_ACCR_MONTH) rn,
  3               count(*) over(partition by EMP_CODE, EMP_ACCR_YEAR) cnt
  4         from EMP_MONTHLY_ACCRUAL 
  5   )
  6     select EMP_CODE, EMP_ACCR_YEAR, 
  7            max(decode(rn,1,EMP_ACCR_MONTH)) first_month,
  8            max(decode(rn,1,EMP_ACCR_MON_VALUE)) first_value,
  9            max(decode(rn,1,EMP_ACCR_RUN_TOTAL)) first_total,
 10            max(decode(rn,cnt,EMP_ACCR_MONTH)) last_month,
 11            max(decode(rn,cnt,EMP_ACCR_MON_VALUE)) last_value,
 12            max(decode(rn,cnt,EMP_ACCR_RUN_TOTAL)) last_total
 13     from data
 14     group by EMP_CODE, EMP_ACCR_YEAR
 15     order by EMP_CODE, EMP_ACCR_YEAR 
 16     /

no rows selected


sriram Smile
Re: query to get first and last values (merged) [message #439648 is a reply to message #439467] Mon, 18 January 2010 23:34 Go to previous messageGo to next message
micro_oracle
Messages: 41
Registered: December 2007
Location: UAE
Member

Ok but I tried the same in toad /benthic-golden and pl/sql developer and I got the same result

No row selected, it means it's working fine but it's not returning data.

can any body give me a good referenes about with clause and inline queries

thanks
Re: query to get first and last values (merged) [message #439650 is a reply to message #439648] Mon, 18 January 2010 23:47 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
No rows selected means i have no data in the table EMP_MONTHLY_ACCRUAL as i did n`t inserted rows into that table.

With
Oracle base
Inline_view
The power of inline views

sriram Smile

[Updated on: Mon, 18 January 2010 23:56]

Report message to a moderator

Previous Topic: Logical equivalence
Next Topic: Accessing Constants from a package
Goto Forum:
  


Current Time: Tue Sep 27 14:32:14 CDT 2016

Total time taken to generate the page: 0.14706 seconds