Solved

Inner Join Problem

Posted on 2004-09-06
8
2,036 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now