Solved

Inner Join Problem

Posted on 2004-09-06
8
2,041 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
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 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query to identify changes between rows of two tables 8 55
use lov values 2 61
constraint check 2 48
Checking for column width 8 28
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

791 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