Home » SQL & PL/SQL » SQL & PL/SQL » Could you please help me in joining these tables (Oracle Applications)
| Could you please help me in joining these tables [message #410727] |
Tue, 30 June 2009 00:21  |
aiyaz_ma
Messages: 56
Registered: May 2009
Location: Hyderabad
|
Member |
 
|
|
Could you please help me in joining these tables
ra_customers
oe_order_headers_all
oe_order_lines_all
mtl_system_items_b
mtl_item_categories
mtl_categories
I wrote the condition like this.Is there any more possibilities for join
Tell me what more to join in this query....?
select x.customer_name,x.segment1,x.description,x.segment2,sum(x.week1) week1, sum(x.week2) week2,
sum(x.week3) week3, sum(x.week4) week4, sum(x.week5) week5
from
(
select a.customer_name,d.segment1,d.description,f.segment2,
case
when to_char(to_date(c.request_date,'DD-MON-YYYY'),'w')= 1 then
nvl(c.ordered_quantity,0)
else null
end as WEEK1,
case
when to_char(to_date(c.request_date,'DD-MON-YYYY'),'w')= 2 then
nvl(c.ordered_quantity,0)
else null
end as WEEK2,
case
when to_char(to_date(c.request_date,'DD-MON-YYYY'),'w')= 3 then
nvl(c.ordered_quantity,0)
else null
end as WEEK3,
case
when to_char(to_date(c.request_date,'DD-MON-YYYY'),'w')= 4 then
nvl(c.ordered_quantity,0)
else null
end as WEEK4,
case
when to_char(to_date(c.request_date,'DD-MON-YYYY'),'w')= 5 then
nvl(c.ordered_quantity,0)
else null
end as WEEK5
from
ra_customers a,
oe_order_headers_all b,
oe_order_lines_all c,
mtl_system_items_b d,
mtl_item_categories e,
mtl_categories f
where
a.customer_id=b.sold_to_org_id and
b.header_id=c.header_id
and c.request_date between sysdate-7 and sysdate+7
and c.inventory_item_id=d.inventory_item_id
and d.inventory_item_id = e.inventory_item_id
and e.category_id = f.category_id
and d.organization_id=e.organization_id
and e.category_set_id=1
) x
group by x.customer_name,x.segment1,x.description,x.segment2;
|
|
|
|
|
|
|
|
|
|
| Re: Could you please help me in joining these tables [message #411011 is a reply to message #410888] |
Wed, 01 July 2009 08:28  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| aiyaz_ma wrote on Wed, 01 July 2009 00:24 | Is there any problem in equating a character to a number when its working fine
|
It's sloppy and poor coding to rely on implicit conversion, and all of a sudden, one day it will not work. You should either convert the CHAR to a NUMBER of vice versa.
|
|
|
|
Goto Forum:
Current Time: Fri Sep 05 17:35:51 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
