19

I have the following SQL:

select <misc things>
from pluspbillline 
left outer join workorder 
    on workorder.siteid=pluspbillline.siteid 
    and workorder.wonum = pluspbillline.refwo
    and workorder.orgid = pluspbillline.orgid
left outer join ticket
    on ticket.ticketid = pluspbillline.ticketid
    and ticket.class=pluspbillline.ticketclass
left outer join pluspsalesorder
    on pluspsalesorder.salesordernum=pluspbillline.salesordernum
    and pluspsalesorder.siteid=pluspbillline.siteid

In Oracle SQL Developer 4.0.0.13 (connected to a DB2 database), I get a squiggly line underneath the following italics: "from pluspbillline" and "left outer join workorder".

The warning says "pluspbillline is disconnected from the rest of the join graph". What does this mean?

ESP
  • 979
  • 2
  • 9
  • 21
  • 1
    I'm not familiar with the Oracle tool, but your SQL looks fine for DB2. You might consider trying IBM Data Studio (free) and seeing if you have any issues there. – WarrenT Dec 01 '13 at 22:47
  • 1
    It's not an issue in terms of functionality, I just like to know what these warnings mean. Do you know what a "join graph" is? Cheers – ESP Dec 03 '13 at 13:40
  • I'm getting a similar problem with a much simpler join with an Oracle Database and Cognos 10 with SQL Dev. I assumed it was related to how the tables were related in Framework Manager (which I cannot access) but if you're getting the same error then it is part of SQL Dev's "helping" indicate where there could be problems. – Rick Henderson Jun 27 '17 at 14:01
  • In my experience SqlDeveloper reports this error when there is nothing wrong with the query. I think it's trying to indicate you forgot to join a table to the other tables in your query. – WW. Jan 23 '18 at 05:53

4 Answers4

33

I got this as well. I'm not exactly sure how to articulate it but the error seems to be based on the logical flow of the code.

Essentially because you mention the table pluspbillline before workorder I think it expects the join to be on pluspbillline.siteid=workorder.siteid etc.

It seems that the order of the conditions for joins should flow from the first identified tables to the latest ones. So the following should make it happy:

plusbillline to workorder       on pluspbillline.siteid=workorder.siteid...
    ""       to ticket          on pluspbillline.ticketid = ticket.ticketid...
    ""       to pluspsalesorder on pluspbillline.salesordernum = pluspsalesorder.salesordernum...

I don't believe this would change the work oracle does (assuming you don't use optimizer hints) so I'd only bother to change if you hate the squiggly lines.

Ganesh Jadhav
  • 2,830
  • 1
  • 20
  • 32
Ewanw
  • 728
  • 11
  • 30
  • 4
    I also saw this in SQL Developer 4.x for the first time. I was able to appease the warning by re-arranging the order of my joins. I had it in a situation where I had to use join tables to get the correct set of records from 2 tables related through the third, e.g. a + b + c where b has only 2 columns b.A-ID and b.C-ID - i had to have: ...from b inner join a on b.A-ID = a.ID inner join c on b.C-ID = c.ID – qxotk May 19 '15 at 17:28
0

I'm not sure what's causing Oracle SQL Developer to give the error. But I'm putting this comment here to format it properly.

A join graph might look something like this

pluspbillline  ------+----<  workorder
                     |
                     +----<  ticket
                     |
                     +----<  pluspsalesorder

The lines on the graph might be labeled with the join fields. But this gives you a basic idea.

I don't see any reason why you are getting this warning. A column name typo in your SQL perhaps? Or some quirk in Oracle's interface that it doesn't understand the DB2 metadata properly? I suggested trying IBM's tool to see if it's merely their program.

WarrenT
  • 4,502
  • 19
  • 27
  • I'm pretty sure there's nothing wrong with the query, everything works fine. Thanks for the explanation of the graph! It will most likely be some incompatibility between SQL Developer and DB2. @a_horse_with_no_name : DB2 stores metadata to describe the structure of the database and other things, much like metadata for a media library it is not required for a relational DB. – ESP Dec 05 '13 at 13:11
  • @a_horse_with_no_name I was referring to the data that a tool will find in the [catalog views](http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Frbafzcatalog.htm). – WarrenT Dec 05 '13 at 22:33
0

The issue is caused by the Oracle Procedure having the same named input parameter as the column on the table you are joining to.
i.e input parm named bank_nbr and a table BankDept.bank_nbr will cause the error if you have WHERE bank_nbr = BankDept.bank_nbr I solved the issue by renaming the input parameter to in_bank_nbr and updating my where to read WHERE in_bank_nbr = BankDept.bank_nbr

Mark
  • 1
0

I had the same message when hovering over the "LEFT" word, but the whole query ran without a problem. On the other hand, when I hovered over "WITH", I got a piece of advice about restructuring the whole query. So, that message about disconnection could be not a sign of an error, but a warning about a too complex sentence. SQLDeveloper's editor does not mention the level of the problem.

Gangnus
  • 24,044
  • 16
  • 90
  • 149