Inner Join Problem

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
LVL 6
pkaledaAsked:
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.

 
BobMcCommented:
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
 
andrewstCommented:
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

Experts Exchange Solution brought to you by ConnectWise

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
 
paquicubaCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
pkaledaAuthor Commented:
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
 
andrewstCommented:
You'll need to put some commas between those table names in the FROM clause...
0
 
pkaledaAuthor Commented:
Thanks.  I have not used Oracle in 2 1/2 years and have been given this project that is due tomorrow.  
0
 
BobMcCommented:
@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
 
andrewstCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.