Skip to main content

I am trying to build a quick report that will pull all of our rework operations that we label with an “R” in our operation description. I cannot get this report to match shopfloor_clockings. It does not pull all of the same shop orders and operations. I started trying to test the database tables individually, but I need a left join in order to filter the content in the operations_history down to the operations with the operation_description found in the shop_order_operations so that I can see that they are working. I get the error that you can see in the screenshot and cannot find a way around it.

The problem is that you have mixed ANSI join and Oracle joins in a way so that the SQL is invalid.

If you want to do it only with ANSI join I guess it will look like this:

SELECT ifsapp.operation_history.part_no,
       ifsapp.operation_history.order_no
FROM ifsapp.operation_history
INNER JOIN ifsapp.inventory_part
ON ifsapp.inventory_part.part_no = ifsapp.operation_history.part_no
AND ifsapp.inventory_part.contract = ifsapp.operation_history.contract
LEFT OUTER JOIN ifsapp.shop_order_operation
ON ifsapp.operation_history.order_no = ifsapp.shop_order_operation.order_no
AND ifsapp.operation_history.release_no = ifsapp.shop_order_operation.release_no
AND ifsapp.operation_history.sequence_no = ifsapp.shop_order_operation.sequence_no
AND ifsapp.operation_history.operation_no = ifsapp.shop_order_operation.operation_no

Observe that I have used all keys between the views. Otherwise you might get duplicate records and worse performance.

Difference between ANSI joins and Oracle joins can you see here:

https://livesql.oracle.com/apex/livesql/file/tutorial_G00HFQ7L5MB5BJ2K9612EDNWB.html

 

One other thing, you mention that you need to do an LEFT OUTER JOIN because:

“I started trying to test the database tables individually, but I need a left join in order to filter the content in the operations_history down to the operations with the operation_description found in the shop_order_operations so that I can see that they are working.”

 

I really don’t understand the need, LEFT OUTER JOIN means in this SQL means that you get:

  1. Records from OPERATION_HISTORY which has a record in SHOP_ORDER_OPERATION.
  2. Records from OPERATION_HISTORY where you do not have a record in SHOP_ORDER_OPERATION.

Is that the intended behavior? If 2 is not needed do an INNER JOIN instead.

The goal is to get all of the order and operations from OPERATION_HISTORY and create a column in the quick report that will add the operation description from SHOP_ORDER_OPERATIONS. I do not believe I am getting all of my shop orders when doing a 1:1 connection between order_no, operation_no, release_no, sequence_no, and part_no. That is why I am looking at outer joins to do testing and see if i am missing something but I’m having trouble getting around the errors.

Reply


Terms & ConditionsCookie settings