db2 query returning 0 records

Question

I am trying to execute below query:

select * from trgdms.src_stm where trim(src_stm_code) in (select concat (concat('''', trim(replace(processed_src_sys_cd,',',''','''))),'''' ) from trgdms.batch_run_log where src_stm_cd_or_hub_cd='CTR' and file_process_dt='2015-06-19' and batch_id=42);

the src_stm_code column of table trgdms.src_stm have values like T19,T68,T73 etc. When I run the inner query alone i do get the correct result:

select concat (concat('''', trim(replace(processed_src_sys_cd,',',''','''))),'''' ) from trgdms.batch_run_log where src_stm_cd_or_hub_cd='CTR' and file_process_dt='2015-06-19' and batch_id=42

Result: 'T19','T68','T73'

Wondered if anyone has used something similar in db2?

Answer 1

Remove the concat part from the inner query. When you concatenate values, the list of values generated will be treated as a string and the query doesn't return any results.

select * from trgdms.src_stm where trim(src_stm_code)
in (select trim(replace(processed_src_sys_cd,',',''',''')) from trgdms.batch_run_log 
    where src_stm_cd_or_hub_cd='CTR' and file_process_dt='2015-06-19' and batch_id=42);