Oracle SQL – Outer join based on concat

Tags: , , ,



I need to cross join two tables but the joining conditions are:

Select FutureInventory.Item,
       To_Number(Concat(Location, Channel_Id)) Location,
  From V_CUST_FUTURE_INV_POSITION FutureInventory,
       xx_Item_Loc_Info_V         ItemLoc
 Where FutureInventory.Item(+) = ItemLoc.Item
   And To_Number(Concat(Location, Channel_Id))(+) = ItemLoc.Loc;

I want to outer join the column To_Number(Concat(Location, Channel_Id)) with ItemLoc.Loc where to put the (+) sign

Answer

First, use explicit join syntax – the old (+) is hard to read and easy to get wrong. Second, use the || operator instead of the CONCAT function – again, easier to read. So we end up with

Select FutureInventory.Item,
       To_Number(Location || Channel_Id) Location,
  From xx_Item_Loc_Info_V ItemLoc
  LEFT OUTER JOIN V_CUST_FUTURE_INV_POSITION FutureInventory,
    ON FutureInventory.Item = ItemLoc.Item And
       To_Number(Location || Channel_Id) = ItemLoc.Loc


Source: stackoverflow