Home » SQL & PL/SQL » SQL & PL/SQL » TOUGH QUERY
TOUGH QUERY [message #186933] Thu, 10 August 2006 03:39 Go to next message
neeraj_kaun
Messages: 2
Registered: August 2006
Junior Member
i have a table
t1(name varchar2(4),month number,sal number)
table consist of name of employee month in number from 1 to 12 and in sal column whenever they get a hike in salary that month salry eneterd

for ex
name month sal
aa 1 1500
bb 1 2000
cc 1 3000
aa 4 2000
aa 8 3000
aa 11 4500
bb 3 2800
bb 6 3700
bb 9 4200
bb 11 5300
cc 6 5700
cc 10 6200


i want to know the sal of employee when i eneterd the month and name

for ex employeee aa salary in month 6 is 2000

how i write this query

[mod-ed]Removed blank pages

[Updated on: Thu, 10 August 2006 04:59] by Moderator

Report message to a moderator

Re: TOUGH QUERY [message #186938 is a reply to message #186933] Thu, 10 August 2006 04:02 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm far from being an expert, but this really seems to be almost impossible to solve. Tough, as you say.

Did you try to write such a query? How does it look like? Did you use the WHERE clause? AND? What was the result?
Re: TOUGH QUERY [message #186949 is a reply to message #186933] Thu, 10 August 2006 04:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Were the 3 pages of blank lines at the end of the post absolutely necessary?

Littlefoot is right in a sense - the only way to solve this in straight SQL would be to have some sort of row generator function that you could use to fill in the missing gaps to let you build a complete picture of the salary.

Fortunately, there is a solution if you're willing to use a little pl/sql, and a really neat idea that I learned from AskTom
This lets you pass a parameter into your sql, allowing you to work round the missing data.

create table temp_sal (emp  varchar2(10), sal_month  number, salary  number);

insert into temp_sal values ('aa', 1, 1500);
insert into temp_sal values ('bb', 1, 2000);
insert into temp_sal values ('cc', 1, 3000);
insert into temp_sal values ('aa', 4, 2000);
insert into temp_sal values ('aa', 8, 3000);
insert into temp_sal values ('aa', 11, 4500);
insert into temp_sal values ('bb', 3, 2800);
insert into temp_sal values ('bb', 6, 3700);
insert into temp_sal values ('bb', 9, 4200);
insert into temp_sal values ('bb', 11, 5300);
insert into temp_sal values ('cc', 6, 5700);
insert into temp_sal values ('cc', 10, 6200);

begin
  dbms_application_info.set_client_info('6');
end;
/

PL/SQL procedure successfully completed.

select distinct emp
      ,first_value(salary) over (partition by emp order by sal_month desc) salary
from   temp_sal
where  sal_month <= sys_context('userenv','client_info');

EMP       |    SALARY
----------|----------
aa        |      2000
bb        |      3700
cc        |      5700


begin
  dbms_application_info.set_client_info('5');
end;
/

PL/SQL procedure successfully completed.

select distinct emp
      ,first_value(salary) over (partition by emp order by sal_month desc) salary
from   temp_sal
where  sal_month <= sys_context('userenv','client_info');

EMP       |    SALARY
----------|----------
aa        |      2000
bb        |      2800
cc        |      3000
icon7.gif  Re: TOUGH QUERY [message #186969 is a reply to message #186938] Thu, 10 August 2006 06:19 Go to previous messageGo to next message
anithalakshmi
Messages: 9
Registered: August 2006
Junior Member
This query can fulfill your need

select * from t1 where rowid=(select max(rowid) from t1 where name='aa' and month<=7)
Re: TOUGH QUERY [message #186977 is a reply to message #186969] Thu, 10 August 2006 07:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How exactly can a max(rowid) ensure you have the latest month??
Re: TOUGH QUERY [message #186979 is a reply to message #186977] Thu, 10 August 2006 07:25 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I apologize if I misunderstood the problem, but this is how I see it:

Here are AA's salaries in the table:
SQL> select * From temp_sal where name = 'aa' order by mon;

NA        MON        SAL
-- ---------- ----------
aa          1       1500
aa          4       2000
aa          8       3000
aa         11       4500
It means that his salary raised in April, August and November. Through the whole year, his salary was:
             Not this!
 1 - 1500    1500
 2 - 1500    1666.67
 3 - 1500    1833.33
 4 - 2000    2000
 5 - 2000    etc.
 6 - 2000
 7 - 2000
 8 - 3000
 9 - 3000
10 - 3000
11 - 4500
12 - 4500

To me, it seems that the OP's request was much simpler than your Space Shuttle solution, JRowbottom - salary was the same until raised.

Therefore, perhaps such a simple query might return what OP needed:
SQL> SELECT t.name, t.sal
  2  FROM temp_sal t
  3  WHERE t.mon = (SELECT MAX(t1.mon) FROM temp_sal t1
  4                 WHERE t1.name = t.name
  5                   AND t1.mon < '&month'
  6                )
  7  ORDER BY t.name;
Enter value for month: 6
old   5:                             AND t1.mon < '&month'
new   5:                             AND t1.mon < '6'

NA        SAL
-- ----------
aa       2000
bb       2800
cc       3000

SQL>
Or am I very wrong about it?
Re: TOUGH QUERY [message #186981 is a reply to message #186979] Thu, 10 August 2006 07:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you need to replace the < with a <=, but that looks like a workable solution, and somewhat less complex. Cool

In retrospect, the sys_context wasn't needed unless the OP wanted to use this in a view. I've just been doing a load of application tracing with this sort of thing lately, so it leapt to mind.

Realistically, the OP is going to have some way of passing the month and Emp into this query, so eith your solution or
select distinct emp
      ,first_value(salary) over (partition by emp order by sal_month desc) salary
from   temp_sal
where  sal_month = :b1
and    emp       = :b2
would do the trick.

You might have misread my example - I'm not interpolating the salary between the months, I was just bringing back the data for all the employees (it looks similar).
Re: TOUGH QUERY [message #187413 is a reply to message #186933] Sun, 13 August 2006 15:22 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
I think you need to replace the < with a <=, but that looks like a workable solution, and somewhat less complex.

In retrospect, the sys_context wasn't needed unless the OP wanted to use this in a view. I've just been doing a load of application tracing with this sort of thing lately, so it leapt to mind.

Realistically, the OP is going to have some way of passing the month and Emp into this query, so eith your solution or

select distinct emp
,first_value(salary) over (partition by emp order by sal_month desc) salary
from temp_sal
where sal_month = :b1
and emp = :b2
would do the trick.

You might have misread my example - I'm not interpolating the salary between the months, I was just bringing back the data for all the employees (it looks similar).
Re: TOUGH QUERY [message #187415 is a reply to message #187413] Sun, 13 August 2006 16:10 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
?
Re: TOUGH QUERY [message #187464 is a reply to message #187415] Mon, 14 August 2006 02:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Possibly the worst attempt at plagarism ever?
Re: TOUGH QUERY [message #192928 is a reply to message #186977] Thu, 14 September 2006 04:48 Go to previous messageGo to next message
anithalakshmi
Messages: 9
Registered: August 2006
Junior Member
Hi,

For the row that is inserted latest will have a rowid greater than the one that is inserted earlier so i had taken max(rowid)
Re: TOUGH QUERY [message #192936 is a reply to message #192928] Thu, 14 September 2006 05:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The statement
Quote:

the row that is inserted latest will have a rowid greater than the one that is inserted earlier
is untrue.
If there are blocks in the table that can accept new records, then these will be used instead of extending the table.
Re: TOUGH QUERY [message #192982 is a reply to message #192928] Thu, 14 September 2006 08:41 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I just could not resist to prove (otherwise I somehow got the idea that it just would not get through Wink)
SQL> create table faq
  2  ( text char(2000)
  3  )
  4  /

Table created.

SQL> insert into faq values ('1');

1 row created.

SQL> insert into faq values ('2');

1 row created.

SQL> insert into faq values ('3');

1 row created.

SQL> insert into faq values ('4');

1 row created.

SQL> insert into faq values ('5');

1 row created.

SQL> insert into faq values ('6');

1 row created.

SQL> insert into faq values ('7');

1 row created.

SQL> delete faq where trim(text) in ('2', '3', '4', '5', '6');

5 rows deleted.

SQL> 
SQL> insert into faq values ('8');

1 row created.

SQL> 
SQL> select rowid
  2  ,      text
  3  from   faq
  4  order  by rowid
  5  /

ROWID               TEXT
------------------  ------------------------------------------
AAADd9AAEAAAAD1AAA  1
AAADd9AAEAAAAD1AAD  8
AAADd9AAEAAAAD3AAA  7
Re: TOUGH QUERY [message #193117 is a reply to message #186933] Thu, 14 September 2006 23:21 Go to previous messageGo to next message
tmlaio
Messages: 6
Registered: September 2006
Junior Member
Is this what you require:

select sal
from t1
where name = 'aa'
and month = (
select min(month)
from t1
where name = 'aa'
and month <= 6)
Re: TOUGH QUERY [message #193125 is a reply to message #192982] Fri, 15 September 2006 00:01 Go to previous message
anithalakshmi
Messages: 9
Registered: August 2006
Junior Member
Hi,
Thank you ..
I was in a view that whichever row is inserted recently will be having a rowid greater than the one that is inserted earlier
now I am clear.
Previous Topic: select query with date = null??
Next Topic: Can clarify pls
Goto Forum:
  


Current Time: Sun Dec 04 02:21:17 CST 2016

Total time taken to generate the page: 0.09234 seconds