crocklobster
asked on
Subquery as outer join condition fails in Oracle 9i
Hi. I have a query similar to this (It's been greatly simplified for this question, but the basics are intact.) :
SELECT *
FROM MESSAGE m1, MESSAGE m2, DOCUMENT d1, DOCUMENT d2,
MESSAGE m3, MESSAGE m4
WHERE
m1.MESSAGE_ID = m2.PARENT_MESSAGE_ID(+)
AND (SELECT nvl(m2.MESSAGE_ID, m1.MESSAGE_ID)
FROM dual) = d1.MESSAGE_ID(+)
AND d1.DOCUMENT_ID = d2.RECEIVED_DOCUMENT_ID(+)
AND d2.MESSAGE_ID = m3.MESSAGE_ID(+)
AND m3.PARENT_MESSAGE_ID = m4.MESSAGE_ID(+)
When I run this in Oracle 8, it works fine, and gives me exactly what I want. In Oracle 9, I get
AND d1.DOCUMENT_ID = d2.RECEIVED_DOCUMENT_ID(+)
*
ERROR at line 8:
ORA-00936: missing expression
I suspect it actually doen't like the join condition above. Does anyone know if there is something I can do to get this to work in Oracle 9?
Here's my scenario for background:
We have message objects which sometimes contain other message objects, but not always. The "latest-descendent" message should typically contain a document (it won't always though). That document will be linked to another document (a forwarding doc), which is linked to an outgoing message, which may or may not have a parent message created from it.
I'm trying to get rows that look like this:
inbound parent message data | inbound child message data or nulls | inbound doc data or nulls |
outbound doc data or nulls | outbound message data or nulls | outbound parent message or nulls
I need to link the document table to _either_ the inbound child message id, or the inbound parent message id, depending on whether a child message exists.
Like I said, this works fine in 8i. I know I could do this in a procedure, but I'm trying to get the statement to create a view.
Any help appreciated.
SELECT *
FROM MESSAGE m1, MESSAGE m2, DOCUMENT d1, DOCUMENT d2,
MESSAGE m3, MESSAGE m4
WHERE
m1.MESSAGE_ID = m2.PARENT_MESSAGE_ID(+)
AND (SELECT nvl(m2.MESSAGE_ID, m1.MESSAGE_ID)
FROM dual) = d1.MESSAGE_ID(+)
AND d1.DOCUMENT_ID = d2.RECEIVED_DOCUMENT_ID(+)
AND d2.MESSAGE_ID = m3.MESSAGE_ID(+)
AND m3.PARENT_MESSAGE_ID = m4.MESSAGE_ID(+)
When I run this in Oracle 8, it works fine, and gives me exactly what I want. In Oracle 9, I get
AND d1.DOCUMENT_ID = d2.RECEIVED_DOCUMENT_ID(+)
*
ERROR at line 8:
ORA-00936: missing expression
I suspect it actually doen't like the join condition above. Does anyone know if there is something I can do to get this to work in Oracle 9?
Here's my scenario for background:
We have message objects which sometimes contain other message objects, but not always. The "latest-descendent" message should typically contain a document (it won't always though). That document will be linked to another document (a forwarding doc), which is linked to an outgoing message, which may or may not have a parent message created from it.
I'm trying to get rows that look like this:
inbound parent message data | inbound child message data or nulls | inbound doc data or nulls |
outbound doc data or nulls | outbound message data or nulls | outbound parent message or nulls
I need to link the document table to _either_ the inbound child message id, or the inbound parent message id, depending on whether a child message exists.
Like I said, this works fine in 8i. I know I could do this in a procedure, but I'm trying to get the statement to create a view.
Any help appreciated.
What you trying to achieve in your query looks very close to the standard ORACLE feature (existing both in ORACLE 8 and 9) that allows effectively manage the hierarchical tasks. You can find it in ORACLE documentation by looking for the keywords
CONNECT BY and START WITH
that should be eventually added to your query.
It is always advisable to use ORACLE implementation instead of recreating it by itself unless you have significant reason believe that your case is (may be?) exception.
Sorry, I cannot be more specific without going in the very details of your implementation.
Try it, it is very powerful and even amazing feature.
COuld you post sample data ?
VC
VC
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It looks like that might work, but I'm all set anyway. I went ahead and used to LEFT OUTER JOIN syntax in 9, and the (+) syntax in 8. In the script we use to add the views, I put a pl/sql block in to figure out what the instance version is, and add the correct view.
No time to investigate the hierarchical query thing, but thanks for the info.
Regards,
Chris
No time to investigate the hierarchical query thing, but thanks for the info.
Regards,
Chris
ASKER
Unforunately, this doesn't work in Oracle 8, so I still don't have a unified solution.
If anyone can figure out a unified oracle 8 / 9 solution to this using a select statement (because it needs to create a view, and the output needs to look as I described), I'd like to hear about it.
Thanks
-c