Home » SQL & PL/SQL » SQL & PL/SQL » Cartesian Join
Cartesian Join [message #273026] Mon, 08 October 2007 14:16 Go to next message
californiagirl
Messages: 79
Registered: May 2007
Member
Hi all be easy on me I am a little confused on my joins.

It seems like I have all the joins correct, but the query is running for ever in Toad, so I assumed I'm having a cartesian issue. In order to query data from more than one table, do I need to identify common columns within each table that relates to all the tables?

See query below:


SELECT pa.segment1, gra.task_id, a.award_id
  FROM apps.gms_resource_assignments gra,
       apps.gms_budget_lines bl,
       apps.pa_resource_list_members rlm,
       apps.pa_resources r,
       apps.pa_projects_all pa,
       apps.gms_awards_all a
 WHERE rlm.resource_id = r.resource_id
   AND gra.resource_list_member_id = rlm.resource_list_member_id
   AND bl.resource_assignment_id = gra.resource_assignment_id
   AND pa.project_id = gra.project_id(+)
   AND pa.project_id = a.award_project_id(+)


Re: Cartesian Join [message #273027 is a reply to message #273026] Mon, 08 October 2007 14:28 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>SELECT pa.segment1, gra.task_id, a.award_id
since you are getting data from 3 table, then only those 3 table should be in the FROM clause.
Subordinate the remaining tables into the WHERE clause & use either EXISTS or IN with these 3 tables.

> AND pa.project_id = gra.project_id(+)
> AND pa.project_id = a.award_project_id(+)

On the surface both of these just don't seem to belong here.
What benefit does either OUTER JOIN add to this SELECT?

[Updated on: Mon, 08 October 2007 14:38] by Moderator

Report message to a moderator

Re: Cartesian Join [message #273028 is a reply to message #273027] Mon, 08 October 2007 15:15 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
Sorry I forgot to add the other columns to the query. Here is the new query.


SELECT pa.segment1, gra.task_id, a.award_id, gra.BUDGET_VERSION_ID, bl.BURDENED_COST budget_total,
decode(substr(r.name,1,10) ,'Award Reve',nvl(bl.BURDENED_COST,0),
                           'F & A Cost',nvl(bl.BURDENED_COST,0),0)indirect_budget,
               decode(substr(r.name,1,10) ,'Award Reve',0,
                           'F & A Cost',0,
                           'Cost Share',0, 
                            nvl(bl.BURDENED_COST,0)) direct_budget,
               decode(substr(r.name,1,10),
                          'Cost Share',nvl(bl.BURDENED_COST,0),0)cs_budget
  FROM apps.gms_resource_assignments gra,
       apps.gms_budget_lines bl,
       apps.pa_resource_list_members rlm,
       apps.pa_resources r,
       apps.pa_projects_all pa,
       apps.gms_awards_all a
 WHERE rlm.resource_id = r.resource_id
   AND gra.resource_list_member_id = rlm.resource_list_member_id
   AND bl.resource_assignment_id = gra.resource_assignment_id
   AND pa.project_id = gra.project_id(+)
   AND pa.project_id = a.award_project_id(+)







Re: Cartesian Join [message #273029 is a reply to message #273027] Mon, 08 October 2007 15:23 Go to previous messageGo to next message
californiagirl
Messages: 79
Registered: May 2007
Member
The main problem now is that no award_id appears in the column once I run the query. There should be some award_ids, so I'm wondering if my joins are not setup properly. Any advice?


Re: Cartesian Join [message #273042 is a reply to message #273026] Mon, 08 October 2007 21:51 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I am afraid that without knowing your data you will get only guesses.

But if you would rewrite the WHERE condition into the order tables are joined:
WHERE pa.project_id = a.award_project_id(+)
  AND pa.project_id = gra.project_id(+)
  AND gra.resource_assignment_id = bl.resource_assignment_id
  AND gra.resource_list_member_id = rlm.resource_list_member_id
  AND rlm.resource_id = r.resource_id

you can figure out, that:
PA is OUTER JOINED with A
PA is OUTER JOINED with GRA; but as GRA is INNER JOINED with other tables, this reduces the PA-GRA JOIN to INNER too.
Try to OUTER JOIN GRA with other tables (adding (+) operator into the right sides of this WHERE condition).

As for query performance, first read Performance Tuning tab in the OraFAQ Forum Guide; if you get lost, post here appropriate information (at least EXPLAIN PLAN, number of rows in the tables/resultset, primary keys/indexes).

Previous Topic: View created different between instances
Next Topic: Need PL/SQL FAQ
Goto Forum:
  


Current Time: Thu Dec 08 04:02:02 CST 2016

Total time taken to generate the page: 0.05344 seconds