Oracle join syntax

Hello, I am new to SQL/Oracle.

I am trying to troubleshoot a query that someone else wrote and I am getting an error message:

ORA 01417 a table may be outer joined to at most one other table

I can see where it's failing, but I am not sure how to perform the proper joins since table D is a derived table.

Here's where it's failing:

WHERE  ( Program.ID(+)= Detail.Program  )                  
  AND  ( Division.ID(+)= Detail.Division  )                    
  AND  ( Activity.ID(+)= Detail.Activity  )      

And this is how I think I should rewrite the code:

Program p
RIGHT JOIN Detail dt
ON p.ID = dt.program

Dividion d
RIGHT JOIN Detail dt
ON d.id = dt.division
 
Activity a
RIGHT JOIN Detail dt
ON a.id = dt.activity

But I get another error that the table does not exist. It turns out that the Detail table is a derived table. There is other stuff going on in this query as well. Views, which point to synonyms which select from tables in another database. But I really want to figure out how to do these joins properly to a derived table.

Thanks.
bi_consultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cjpereiraCommented:
Hi!
Check if the table name is correctly in the query
ON ORACLE
is case sensisitve
If the table is
TABLE_Name
you must query as
TABLE_Name, not table_name or TABLE_NAME

do it with fields also....

BR!
0
sdstuberCommented:
I would reverse them,  DETAIL is only  joined in once
also, there was a spelling error in your join to division
try this...

from detail dt
left join Program p
ON p.ID = dt.program

left join Division d
ON d.id = dt.division
 
left join Activity a
ON a.id = dt.activity
0
sdstuberCommented:
Check if the table name is correctly in the query
ON ORACLE
is case sensisitve

this is not correct with normal naming

case sensitivity in object names is only enforced when the object names are enclosed in double quotes.

Thus

table = TABLE = Table

but

table != "table"
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

bi_consultantAuthor Commented:
cjpereira - it is not a case issue, I have changed table and field names to post on here and the case in my original script is correct.

sdstuber - part of the issue is that the detail table is a derived table created in the from clause. So if I try to move my joins to the from clause, I get a table/view missing error. If I try to create a view instead of using the derived table, the query takes forever to run.

Thanks.
0
sdstuberCommented:
RIGHT/LEFT JOIN syntax is only legal in the FROM clause

What I posted should be correct for a translation of the three criteria you posted
but, in the context of the rest of the query there could be more to it.

can you post your entire query?
0
bi_consultantAuthor Commented:
I cannot post the entire query because it's very complex and several pages, and there are confidentiality issues, but I stated in my initiating post that when I tried to rewrite it with my syntax, it's failing because of the derived table. Which means it would fail with your syntax as well. If the derived table is created in the from clause, and then I have to move my joins from the where clause to the from clause, there's nowhere to create my derived table (I think?) and so the query with fail with a missing table/view error.

Currently it is structured like this:

Select ....

From
Division Table,
Program Table,
Activity Table,
(Select <derived table) Detail Table

Where
Program (+) = Detail
Division (+) = Detail
Activity (+) = Detail
0
PortletPaulfreelancerCommented:
A derived table is no different from a join perspective, except that you surround the subquery that produces it by parentheses, give it the alias you want (e.g. "detail" as been used below), then join to the other tables.
SELECT
        *
FROM (
        /* the subquery the produces the derived table here */
        SELECT
                1 AS id
              , 1 as program
              , 1 as division
              , 1 as activity
              , 'derived information is here' as whatever
        FROM dual
     ) detail
LEFT JOIN Program  ON detail.program = Program.ID    -- << list the 'previous' table first
LEFT JOIN Division ON detail.division = Division.id  -- << list the 'previous' table first
LEFT JOIN Activity ON detail.activity = Activity.id  -- << list the 'previous' table first
;

Open in new window

attitudes differ on the convention that applies to how you write-up the joins. I prefer to list the previously listed table first, this way "the left" in a left join makes more sense (well, it does to me).

{+edit}
Sometimes overlooked point with ANSI join syntax is that the order does matter.
Here "detail" must be before all the other tables.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
>>  it's failing because of the derived table. Which means it would fail with your syntax

No,  I believe you that your rewrite produced an error, but that's not because what I posted is wrong, it's in how you implemented it.

If you can't post the entire query, then post a sufficient portion that represents  what you're trying to do. And then I can help reform it into syntax that will work.

You need to put the LEFT/RIGHT (or other ANSI forms) in the FROM clause.  That's not optional.  A "derived table" or "inline view" has no bearing on where you put the joins or how that syntax works.
0
PortletPaulfreelancerCommented:
>>in the FROM clause.  That's not optional.
absolutely!
0
sdstuberCommented:
wouldn't a split be in order?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.