How to solve ERROR: operator is not unique: "unknown" - "unknown" in a dynamic SQL query?

Question

I have a execute block inside a stored procedure and I am executing a query dynamically:

execute 'create table raw_mine.financial_multicase_xwalk_' || target_date || '
as
select distinct
  a.cvr_mnth_dt
, a.filler_string_10
, a.alt_prsn_id
, a.member_id
from raw_mine.rstag_mine_TO_CGT_MBRSHP_' || target_date || ' a
left join
raw_mine.rstag_mine_TO_CGT_FIN_' || target_date || ' b
on a.filler_string_10 = b.filler_string_10
and a.member_id = b.member_id
and left ( a.cvr_mnth_dt, 6) = left ( b.cvr_mnth_dt, 6)
left join
raw_mine.rstag_mine_TO_CGT_FIN_' || target_date || ' cp
on a.filler_string_10 = cp.filler_string_10
and a.alt_prsn_id = cp.alt_prsn_id
and left ( a.cvr_mnth_dt, 6) = left ( cp.cvr_mnth_dt, 6)
left join
raw_mine.member_xwalk_dulality_' || target_date || ' c
on a.alt_prsn_id = c.alt_prsn_id
and left ( a.cvr_mnth_dt, 6) = left ( replace ( c.cvr_mnth_dt, '-', ''), 6)
where nullif ( c.alt_prsn_id, '') is null
and cp.alt_prsn_id is not null
and cp.member_id != a.member_id;';

all the columns are varchars and target_date is input param from the procedure and I am getting an error

SQL Error [42725]: ERROR: operator is not unique: "unknown" - "unknown"

Hint: Could not choose a best candidate operator. You may need to add explicit type casts.

Any idea how to solve this?

This query works while executing outside the procedure.

Answer 1

It looks like you've forgotten to escape the single quotes defining strings in these lines:

and left ( a.cvr_mnth_dt, 6) = left ( replace ( c.cvr_mnth_dt, '-', ''), 6)
where nullif ( c.alt_prsn_id, '') is null

You will probably want

and left ( a.cvr_mnth_dt, 6) = left ( replace ( c.cvr_mnth_dt, ''-'', ''''), 6)
where nullif ( c.alt_prsn_id, '''') is null