Home » SQL & PL/SQL » SQL & PL/SQL » regarding fact tab
icon9.gif  regarding fact tab [message #315734] Tue, 22 April 2008 10:28 Go to next message
amritaseema
Messages: 47
Registered: January 2008
Member
Hi All,
Actually i want to create one fact table from these base tables,where i hard coded the booked_date like where BOOKED_DATE>='1-oct-2001' in order_header_table while creation of dimension table....

oe_order_headers_all ooh,
oe_order_lines_all ool,
oe_transaction_types_tl otl,
mtl_item_categories mic,
mtl_category_sets_b mcs,
mtl_categories_b mcb,
mtl_sytem_items_b msib,
hz_parties hp,
hz_cust_accounts_all hca

from these above table i want to create one fact table which 'll show these data
header_id,
ool.line_id,
ool.unit_selling_price,
otl.transaction_type_id,
msib.inventory_item_id,
mcb.category_id,
mcs.category_set_id,
hca.cust_account_id,
hp.party_id
bt while creating joins its retriving too many records because of that i am unable to create the fact table..THIS ONE IS MY QUERY...CAN U PLZZ CHK IT...

select ooh.header_id,
ool.line_id,
ool.unit_selling_price,
otl.transaction_type_id,
msib.inventory_item_id,
mcb.category_id,
mcs.category_set_id,
hca.cust_account_id,
hp.party_id,
time_id
from oe_order_headers ooh,
oe_order_lines_allool,
oe_transaction_types_tl otl,
mtl_item_categories mic,
mtl_category_sets_bmcs,
mtl_categories_bmcb,
mtl_sytem_items_bmsib,
hz_parties hp,
hz_cust_accounts hca,
times t
where ooh.header_id=ool.header_id
and ooh.order_type_id = otl.transaction_type_id
and ool.ship_from_org_id=mic.organization_id
and ool.ship_from_org_id=msib.organization_id
and ool.inventory_item_id=msib.inventory_item_id
and mic.organization_id=msib.organization_id
and mcs.category_set_id=mic.category_set_id
and mcb.category_id=mic.category_id
and hca.cust_account_id=ooh.sold_to_org_id
and hca.party_id=hp.party_id
and trunc(ooh.booked_date)=t.time_id

this query retriving nearly 8 lakh records and while i am trying to create its showing the error that "UNABLE TO EXTEND TEMP SEGMENT BY 18206 IN TABLESPACE SYSTEM"


any one plzzz help me out....
i think some mistake in joins.......
its really urgent for me......i need it immediately
thanks in advance

Thanks

[Updated on: Tue, 22 April 2008 10:29]

Report message to a moderator

Re: regarding fact tab [message #315927 is a reply to message #315734] Wed, 23 April 2008 04:44 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Firstly, if it's urgent, hire and pay a specialist to solve this for you instead of posting on a forum.

Secondly, you have more chance on a good answer if this question was posted on the SQL forum (or perhaps EBS forum), since this actually has nothing to do with business intelligence. I mean, regardless of the front-end/reporting tool you use, you have to get this query to work, right?

I'll ask a moderator to move this post.
And will have a look at your statement.
Re: regarding fact tab [message #315954 is a reply to message #315927] Wed, 23 April 2008 06:07 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Had a quick look, but this is too complex to check without knowing the EBS setup you use. For example, you join to OOL just based on sold_to_org_id, whereas your organization might be using sold_to_location_id too.

Two small things I notices though:
oe_order_headers ooh -- you do use oe_order_lines_all, why not oe_order_headers_all?
oe_transaction_types_tl otl -- you get all languages, is that intended?
hz_parties hp -- not used in select, why is it here?

icon7.gif  Re: regarding fact tab [message #315963 is a reply to message #315927] Wed, 23 April 2008 06:20 Go to previous message
amritaseema
Messages: 47
Registered: January 2008
Member
thanks for the suggestion.
i got that.
Previous Topic: % concatenation problem in string
Next Topic: multiple values in a field
Goto Forum:
  


Current Time: Fri Dec 09 17:27:00 CST 2016

Total time taken to generate the page: 0.24669 seconds