Is there a way to concat two columns from a table and join three tables

Question

I am trying to join three tables, but in one of the tables I need to concat two columns for being able to do the left join.

select * from 
 order_dupe_check_cleaned dcc left join AAPEN_SORLIN asr
on dcc.INTERNAL_ORDER_ID = asr.VLCODE 
left join order_dupe_atributes oda on dcc.INTERNAL_ORDER_ID = oda.INTERNAL_ORDER_ID 
and oda.product_id = --** Here I want to insert the concat statement**(select concat_ws(VLDEPT,VLSTYL)as product_id2 pi from AAPEN_SORLIN) 
where dcc.order_id like '73901124'
;

I have used (+) instead (||) as this one was giving me duplicates, so I was trying to link the three tables by using left join however, one of the tables does not contain product_id but it has two columns (VLDEPT,VLSTYL) that together give the product_id that is why I wanted to use concat.

Answer 1

With strings you can concatenate simply by using ||.

Because you've already joined the fields via left join AAPEN_SORLIN asr you should be able to access them directly.

select 
    * 
from 
 order_dupe_check_cleaned dcc 
 left join AAPEN_SORLIN asr
on dcc.INTERNAL_ORDER_ID = asr.VLCODE 
left join order_dupe_atributes oda on dcc.INTERNAL_ORDER_ID = oda.INTERNAL_ORDER_ID 
and oda.product_id = VLDEPT||VLSTYL

--** Here I want to insert the concat statement**(select concat_ws(VLDEPT,VLSTYL)as product_id2 pi from AAPEN_SORLIN) 
where dcc.order_id like '73901124'