| Outer Join appears not to work in Sql Plus [message #21491] |
Wed, 07 August 2002 15:08  |
John Mount
Messages: 2
Registered: August 2002
|
Junior Member |
|
|
This is very strange. I created a Crystal Report which uses outer joins between tables. The report appears to return all the correct data. I copied and pasted the Sql from the Crystal Report directly into Sql Plus Worksheet and it excutes OK, but does not return all of the rows that the same query does in Crystal.
This is a basic Order with Parts, both in different tables, in a one-to-many relationship. Since there are some Orders without Parts(test data may not be real), I used an outer join between these 2 tables to return Order Numbers for Orders without Parts. There are 2 Orders among 48 without Parts. Pages for these 2 Orders are created in Crystal, but with no Detail (Part) sections. However, when running the Crystal Sql in Sql Plus Worksheet, rows for the 2 Orders without Parts do not appear in the result set, although all tables are outer joined.
Anyone know what may be happening in Sql Plus Worksheet?
Thanks
|
|
|
|
| Re: Outer Join appears not to work in Sql Plus [message #21517 is a reply to message #21491] |
Thu, 08 August 2002 19:33  |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
Since I am not sure if there is any difference between crystal reports and Oracle sql process, it is little difficult to say what the problem is, unless you provide your sql query to us. If possible, provide the sql code so that it would be easier to find the culprit. Hope you understand my point.
Good luck :)
|
|
|
|
| Re: Outer Join appears not to work in Sql Plus [message #21527 is a reply to message #21491] |
Fri, 09 August 2002 09:15 |
John Mount
Messages: 2
Registered: August 2002
|
Junior Member |
|
|
Here is the query as run in Sql Plus Worksheet:
SELECT
EPM_OPEN_ORD_WITH_ROUTING\.ORDER_NO, INVENTORY_PART.PART_NO
FROM
EPM_OPEN_ORD_WITH_ROUTING EPM_OPEN_ORD_WITH_ROUTING\,
PAYMENT_TERM PAYMENT_TERM,
CUSTOMER_ORDER_LINE CUSTOMER_ORDER_LINE,
CUST_ORD_CUSTOMER_ADDRESS_ENT CUST_ORD_CUSTOMER_ADDRESS_ENT,
INVENTORY_PART INVENTORY_PART
WHERE
EPM_OPEN_ORD_WITH_ROUTING\.PAY_TERM_ID = PAYMENT_TERM.PAY_TERM_ID(+) AND
EPM_OPEN_ORD_WITH_ROUTING\.ORDER_NO = CUSTOMER_ORDER_LINE.ORDER_NO(+) AND
EPM_OPEN_ORD_WITH_ROUTING\.CUSTOMER_NO = CUST_ORD_CUSTOMER_ADDRESS_ENT.CUSTOMER_ID(+) AND
EPM_OPEN_ORD_WITH_ROUTING\.SHIP_ADDR_NO = CUST_ORD_CUSTOMER_ADDRESS_ENT.ADDRESS_ID(+) AND
CUSTOMER_ORDER_LINE.CONTRACT = INVENTORY_PART.CONTRACT(+) AND
CUSTOMER_ORDER_LINE.PART_NO = INVENTORY_PART.PART_NO(+) AND
CUSTOMER_ORDER_LINE.CONTRACT = 'CLARK'
ORDER BY
EPM_OPEN_ORD_WITH_ROUTING\.ORDER_NO ASC
This differs from the query run by Crystal in that the Crystal query does not have the Where clause statement of CUSTOMER_ORDER_LINE.CONTRACT = 'CLARK' because this is done in the Record Selection part of Crystal. If I remove this line from the SqlPlus query, the two missing Order records appear, so it has something to do with this, apparently. But I don't know why because the two missing Order records both have a CONTRACT = 'CLARK' so adding this should not filter them out.
Any ideas why this makes a difference?
Many thanks.
JM
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
