Use Case statement in Join resulting in error

April 18, 2023

I am trying to use a CASE statement inside a Join below. Essentially if the cce.LOCATOR_ID is NULL then I just want to say 1=1 (to evaluate true), otherwise (if cce.LOCATOR_ID is NOT Null) then I want to join DEF.LOCATOR_ID = cce.LOCATOR_ID. This is erroring with ORA-00905: missing keyword

SELECT cce.ITEM_NUMBER, DEF.SEGMENT1
FROM INV_CYCLE_COUNT_ENTRIES cce
LEFT OUTER JOIN inv_item_loc_defaults DEF ON FEF.INVENTORY_ITEM_ID = cce.INVENTORY_ITEM_ID   
   AND DEF.SUBINVENTORY_CODE = cce.SUBINVENTORY
   AND DEF.DEFAULT_TYPE = 2
   AND CASE WHEN cce.LOCATOR_ID IS NULL THEN 1=1 ELSE DEF.LOCATOR_ID = cce.LOCATOR_ID END

It seems as if the last line needs to be changed to something like: AND cce.LOCATOR_ID = CASE WHEN cce.LOCATOR_ID IS NULL THEN ???? I don’t know what to put here when the CASE statement is after the equal sign and just have it evaluate to true, like with 1=1 above.

>Solution :

Just use AND/OR logic

LEFT OUTER JOIN inv_item_loc_defaults DEF ON FEF.INVENTORY_ITEM_ID = cce.INVENTORY_ITEM_ID   
   AND DEF.SUBINVENTORY_CODE = cce.SUBINVENTORY
   AND DEF.DEFAULT_TYPE = 2
   AND (cce.LOCATOR_ID IS NULL or DEF.LOCATOR_ID = cce.LOCATOR_ID)