Home » SQL & PL/SQL » SQL & PL/SQL » Help needed in building sql query
Help needed in building sql query [message #218193] Wed, 07 February 2007 04:13 Go to next message
sreedevi_83
Messages: 43
Registered: October 2005
Location: India
Member
If i have two tables with data
Table Name: sree_t1
PROJECT_ID TASK_ID EXP_ID
1	     1	    11
2	     1	    21
3	     1	    31

Table Name : sree_t2

PROJECT_ID TASK_ID Rate
1	     1	    10 
2	     1	    15
2	     	    20

Result Expected

PROJECT_ID TASK_ID EXP_ID  Rate
1	     1	    11      10     
2	     1	    21      15 
3	     1	    31


sree_t1 has project,task,expenditure information.

sree_t2 has information about the bill rates, bill rate for a particular project can be defined at project level or at task level or may not be defined at all.

In the example
for project 2, there are two rates
rate 15 defined at task level, rate 20 defined at project level
for project 1, rate is defined only at tasklevel.

Result:
If a project has two rates one at project level and one at task level , then only task level data should be displayed. sree_t1 information which is not having any rate in sree_t2 also should be displayed in the result.

I tried writing a query as mentioned below
create table sree_t1 
   (project_id number,task_id number,exp_id number);
   
   create table sree_t2
   (project_id number,task_id number,rate number);
   
   insert into sree_t1
   values(1,1,11);
   
      insert into sree_t1
   values(2,1,21);
   
   insert into sree_t1
   values(3,1,31);
   
   insert into sree_t2
   (project_id,rate)
   values(1,10);
   
   insert into sree_t2
   (project_id,task_id,rate)
   values(2,1,15);
   
   insert into sree_t2
   (project_id,rate)
   values(2,20);
   
   commit;
   
   select t1.*,t2.rate 
   from sree_t1 t1,
        sree_t2 t2
   where t1.project_id=t2.project_id (+)
   and t1.task_id=nvl(t2.task_id(+),t1.task_id)
   order by t1.project_id


But it didn't give me expected result.
Any suggestions for writing the correct query?

Thanks in Advance,
Sreedevi.
Re: Help needed in building sql query [message #218198 is a reply to message #218193] Wed, 07 February 2007 04:37 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

does this work.
SELECT S1.PROJECT_ID,S1.TASK_ID,S1.EXP_ID,S2.RATE
FROM SREE_T1 S1 LEFT OUTER JOIN SREE_T2 S2
ON (S1.PROJECT_ID=S2.PROJECT_ID)
AND S2.TASK_ID IS NOT NULL
ORDER BY 1 



regards,
Re: Help needed in building sql query [message #218199 is a reply to message #218198] Wed, 07 February 2007 04:54 Go to previous messageGo to next message
sreedevi_83
Messages: 43
Registered: October 2005
Location: India
Member
Hi Dhananjay,
Thank you for responding, but i still dont get expected result.
please insert using

insert into sree_t1
values(4,1,41);

insert into sree_t2
(project_id,rate)
values(4,10);

commit;

In the result i must get another row with data
4,1,41,10
but i just got 4,1,41 in the result

Here the intention is, for project 4, as there is no task level rate defined, it must display project level rate 

Re: Help needed in building sql query [message #218204 is a reply to message #218199] Wed, 07 February 2007 05:17 Go to previous messageGo to next message
sreedevi_83
Messages: 43
Registered: October 2005
Location: India
Member
to be more clarified..
Table Name: sree_t1
PROJECT_ID TASK_ID EXP_ID
1	     1	    11
2	     1	    21
3	     1	    31
4            1      41

Table Name : sree_t2

PROJECT_ID TASK_ID Rate
1	     1	    10 
2	     1	    15
2	     	    20
4                   40

Result Expected

PROJECT_ID TASK_ID EXP_ID  Rate
1	     1	    11      10     
2	     1	    21      15 
3	     1	    31
4            1      41      40

Task level Rate should be picked if it is defined at task level, else it should pick up rate defined at project level from sree_t2 table.
Re: Help needed in building sql query [message #218214 is a reply to message #218204] Wed, 07 February 2007 06:11 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Sreedevi,
Try these.
SELECT S1.PROJECT_ID,S1.TASK_ID,S1.EXP_ID,S2.RATE
FROM SREE_T1 S1 LEFT OUTER JOIN (select * from
(select a.*,rank() over (partition by project_id order by task_id) rank_task 
from sree_t2 a)
where rank_task = 1)S2
ON (S1.PROJECT_ID=S2.PROJECT_ID)
ORDER BY 1;
select c.project_id,c.task_id,c.exp_id,b.rate 
from sree_t1 c,
(select * from (select a.*,rank() over (partition by project_id order by task_id) rank_task 
from sree_t2 a)where rank_task = 1)b
where c.project_id = b.project_id (+);
By
Vamsi
Re: Help needed in building sql query [message #218215 is a reply to message #218204] Wed, 07 February 2007 06:12 Go to previous messageGo to next message
yd_261076
Messages: 19
Registered: February 2007
Junior Member
Try this:


select a.*,b.rate
from sree_t1 a, (select b.*, row_number() over (partition by b.project_id order by rate) rnum
from sree_t2 b) b
where a.project_id=b.project_id(+)
and b.rnum(+) = 1
order by a.project_id
Re: Help needed in building sql query [message #218217 is a reply to message #218215] Wed, 07 February 2007 06:19 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Seems it is a simple one.....

By
Vamsi
Re: Help needed in building sql query [message #218220 is a reply to message #218215] Wed, 07 February 2007 06:28 Go to previous messageGo to next message
sreedevi_83
Messages: 43
Registered: October 2005
Location: India
Member
Wonderful all the solutions given worked. Thanq so much Vamsi and yd_261076. But I would like to know if there is any other possibility of writing the same.

I am trying to write query in such a way that,
the select clause and from clause should be same
and modification should exist only in the where conditions..

something like this..
select t1.project_id,t1.task_id,t1.exp_id,t2.rate
FROM t1,t2
where
conditions

The purpose why I am asking such a solution is..
I already have t1 and t2 as two different folders in discoverer and I can just write my own conditions in the condition window and query builds itself using the conditions, the columns and table names. I cant modify select and from clauses.

Any more suggestions please?
-Sree
Re: Help needed in building sql query [message #218425 is a reply to message #218220] Thu, 08 February 2007 03:09 Go to previous messageGo to next message
yd_261076
Messages: 19
Registered: February 2007
Junior Member
soory for the delayed response....
Hope this would work out for you.......

select t1.*,t2.rate
from sree_t1 t1,sree_t2 t2
where t1.project_id=t2.project_id(+)
and t2.rate is null
UNION
select t1.*,t2.rate
from sree_t1 t1,sree_t2 t2
where t1.project_id=t2.project_id(+)
and t2.rate in (select max(t2.rate)
from sree_t2 t2
where t2.project_id=t1.project_id)
Re: Help needed in building sql query [message #219163 is a reply to message #218193] Tue, 13 February 2007 08:03 Go to previous message
sreedevi_83
Messages: 43
Registered: October 2005
Location: India
Member
Hi,
The above query suggested may not give correct results as we are not aware which rate will be maximum.
initial query suggested.. 
select a.*,b.rate 
from sree_t1 a, (select b.*, row_number() over (partition by b.project_id order by rate) rnum 
from sree_t2 b) b
where a.project_id=b.project_id(+)
and b.rnum(+) = 1 
order by a.project_id; 

is not retrieving correct results as well :-(

For example:
let sree_t1 contains following records

PROJECT_ID   TASK_ID	EXP_ID

1	        1	11
2	        1	21
3	        1	31
4	        1	41
2	        2	22

and sree_t2 contains following records


PROJECT_ID  TASK_ID	RATE

1	 	          10
2	      1	          15
2		          20
4		          10

Ouput which i get when i use your query is 
PROJECT_ID  TASK_ID	EXP_ID	RATE

1	       1	11	10
2	       2	22	15
2	       1	21	15
4	       1	41	10
3	       1	31	

Correct output should be 
PROJECT_ID  TASK_ID	EXP_ID	RATE

1	       1	11	10
2	       2	22	20
2	       1	21	15
4	       1	41	10
3	       1	31


For task 2 of project 2 there is no rate defined at task level so it should take project level defined rate..which is equal to 20.. but it is taking 15.

Any suggestions please?
Thanks in advance.
Regards,
Sreedevi
Previous Topic: Group by on nested query
Next Topic: Dynamic SQL and Loop question
Goto Forum:
  


Current Time: Fri Dec 09 13:31:15 CST 2016

Total time taken to generate the page: 0.24510 seconds