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 Go to next message
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 #410732 is a reply to message #410727] Tue, 30 June 2009 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Could you please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Could you please help me in joining these tables [message #410824 is a reply to message #410727] Tue, 30 June 2009 08:17 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
aiyaz_ma wrote on Tue, 30 June 2009 01:21

when to_char(to_date(c.request_date,'DD-MON-YYYY'),'w')= 1 then



A TO_CHAR can never equal a number.
Re: Could you please help me in joining these tables [message #410888 is a reply to message #410824] Tue, 30 June 2009 23:24 Go to previous messageGo to next message
aiyaz_ma
Messages: 56
Registered: May 2009
Location: Hyderabad
Member

Hi joy,

After your question that a TO_CHAR will never equal to a number. But that case statement returns a number of the week of a particular date. Still i know that's a character.

But when i equate that to a number still the output is coming and my query is working fine. Is there any problem in equating a character to a number when its working fine else i need to change the number to character

Please help me.

I have got cleared with joining of table.

Thanks
Aiyaz
Re: Could you please help me in joining these tables [message #411011 is a reply to message #410888] Wed, 01 July 2009 08:28 Go to previous message
joy_division
Messages: 4643
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.
Previous Topic: select query
Next Topic: Update in PL/SQL procedure
Goto Forum:
  


Current Time: Thu Dec 08 19:59:32 CST 2016

Total time taken to generate the page: 0.15400 seconds