Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2055
  • Last Modified:

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
0
pkaleda
Asked:
pkaleda
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
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
 
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
Technology Partners: 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!

 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now