Solved

Inner Join Problem

Posted on 2004-09-06
8
2,042 Views
Last Modified: 2010-08-05
I have modified the below SQL statement that was originally built for SQL Server.  I am trying to make it work for oracle 8i.  When I run it in Toad, it highlights the WS_Worksheet directly after the FROM and before the A, and tells me it is an invalid SQL statement.  Any ideas?

CREATE OR REPLACE VIEW WS_Worksheet_Detail
AS
SELECT
    A.WorksheetID, A.ShortDesc, A.LongDesc, A.WorksheetTypeID, A.Enabled, A.BodySQL,
    A.TotalSQL, A.OutOfSync, A.HeaderURL, A.FooterURL, A.IncludeURL, A.DisplayTypeID, A.IsConsolidated,
    B.ShortDesc AS WorksheetTypeDesc, B.Enabled AS WorksheetTypeEnabled,
    ISNULL(D.FrameID, C.FrameID) AS FrameID, E.FieldName AS WhereField,
    E.FieldTable AS WhereTable
FROM
    WS_Worksheet A  INNER JOIN
    WS_WorksheetType B ON A.WorksheetTypeID = B.WorksheetTypeID LEFT OUTER JOIN
    WS_FrameMax C ON A.WorksheetID = C.WorksheetID LEFT OUTER JOIN
    WS_FrameScroll D ON A.WorksheetID = D.WorksheetID LEFT OUTER JOIN
    WS_Field E ON B.WhereFieldID = E.FieldID
0
Comment
Question by:pkaleda
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 7

Assisted Solution

by:BobMc
BobMc earned 250 total points
ID: 11990669
I dont think Oracle 8 supports the ANSI syntax - this feature was only introduced with Oracle9.
I dont use the ANSI syntax, but I think you will want something like

CREATE OR REPLACE VIEW WS_Worksheet_Detail
AS
SELECT
    A.WorksheetID, A.ShortDesc, A.LongDesc, A.WorksheetTypeID, A.Enabled, A.BodySQL,
    A.TotalSQL, A.OutOfSync, A.HeaderURL, A.FooterURL, A.IncludeURL, A.DisplayTypeID, A.IsConsolidated,
    B.ShortDesc AS WorksheetTypeDesc, B.Enabled AS WorksheetTypeEnabled,
    decode(D.FrameID, NULL,C.FrameID, D.FrameID) AS FrameID, E.FieldName AS WhereField,
    E.FieldTable AS WhereTable
FROM
    WS_Worksheet A  
    WS_WorksheetType B
    WS_FrameMax C
    WS_FrameScroll D
    WS_Field E
WHERE
    A.WorksheetTypeID = B.WorksheetTypeID
AND
    A.WorksheetID(+) = C.WorksheetID
AND
    A.WorksheetID(+) = D.WorksheetID
AND
    B.WhereFieldID(+) = E.FieldID;
0
 
LVL 15

Accepted Solution

by:
andrewst earned 250 total points
ID: 11990697
That's correct, except you have the (+)s on the wrong side of the joins.  Should be:

AND
    A.WorksheetID = C.WorksheetID (+)
AND
    A.WorksheetID = D.WorksheetID (+)
AND
    B.WhereFieldID = E.FieldID (+);
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 11990754
What I can see wrong here is  ISNULL(D.FrameID, C.FrameID)
replace it with Oracle's NVL(D.FrameID, C.FrameID)

Good luck!
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Author Comment

by:pkaleda
ID: 11990790
BobMC and andrewst.

I used a combo of your querries that is below.  It tells me that the "sql command not properly ended" on this line "WS_WorksheetType b"

CREATE OR REPLACE VIEW WS_Worksheet_Detail
AS
SELECT
    A.WorksheetID, A.ShortDesc, A.LongDesc, A.WorksheetTypeID, A.Enabled, A.BodySQL,
    A.TotalSQL, A.OutOfSync, A.HeaderURL, A.FooterURL, A.IncludeURL, A.DisplayTypeID, A.IsConsolidated,
    B.ShortDesc AS WorksheetTypeDesc, B.Enabled AS WorksheetTypeEnabled,
    decode(D.FrameID, NULL,C.FrameID, D.FrameID) AS FrameID, E.FieldName AS WhereField,
    E.FieldTable AS WhereTable
FROM
    WS_Worksheet A  
    WS_WorksheetType B
    WS_FrameMax C
    WS_FrameScroll D
    WS_Field E
WHERE
    A.WorksheetTypeID = B.WorksheetTypeID
AND
    A.WorksheetID = C.WorksheetID (+)
AND
    A.WorksheetID = D.WorksheetID (+)
AND
    B.WhereFieldID = E.FieldID (+)
0
 
LVL 15

Expert Comment

by:andrewst
ID: 11990807
You'll need to put some commas between those table names in the FROM clause...
0
 
LVL 6

Author Comment

by:pkaleda
ID: 11990857
Thanks.  I have not used Oracle in 2 1/2 years and have been given this project that is due tomorrow.  
0
 
LVL 7

Expert Comment

by:BobMc
ID: 11990876
@andrewst,

I dont use ANSI syntax, cos I can't read it when theres more than one join condition!
Can you explain how you arrive at (for example)  
 A.WorksheetID = C.WorksheetID (+)  
and not
  A.WorksheetID(+) = C.WorksheetID

Surely that would be a right outer join, or am I missing something (its been a long day!)

Bob

0
 
LVL 15

Expert Comment

by:andrewst
ID: 11990986
Bob,

I'm with you there - after 15 years of writing SQL without the new ANSI join syntax, I find the old way much easier!

I also find the "left" part mysterious, but it is a fact that "FROM A LEFT OUTER JOIN B" is equivalent to our "FROM A, B WHERE A = B (+)".  It's just something you have to memorise.

I had a debate about the "meaning" of LEFT (and indeed OUTER) in "LEFT OUTER JOIN" with Rudy Limeback here:

http://www.dbforums.com/t976615.html

However, as you can see, nothing got resolved since he misunderstood my point - he thought I just didn't know what it DID!  
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle cursor lifecycle inside procedure. 2 27
format dd/mm/yyyy parameter 16 48
Help with Oracle IF statment 5 36
PL/SQL: ORA-00979: not a GROUP BY expression 3 53
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question