I am having a syntax issue with the following dbt merge code. I am new to dbt. this is not a type 2 dimention. My error is
001003 (42000): SQL compilation error:
16:53:54 syntax error line 84 at position 28 unexpected ‘inserts’.
Some example code or error messages
{{
config(
materialized='incremental',
unique_key='test_id',
incremental_strategy='merge'
)
}}
with
cte_mgltestordermap as (
select
s.testid
,s.ordernumber
,s.reportmaildate
,s.originalreportmaildate
,s.preciseflag
, case
when production.originalreportmaildate is null then stage.reportmaildate
else production.originalreportmaildate
end as upd_originalreportmaildate
, case
when production.originalreportmaildate is null then stage.ordernumber
else production.ordernumber
end as upd_ordernumber
,case
when production.originalreportmaildate is null then coalesce(existingids.orderid, production.orderid)
else production.orderid
end as upd_orderid
,case
when production.preciseflag is null then stage.preciseflag
else production.preciseflag
end as upd_preciseflag
, case when coalesce(p.reportmaildate, '1970-01-01') <> coalesce(s.reportmaildate, '1970-01-01')
or coalesce(p.preciseflag, 0) <> coalesce(s.preciseflag, 0) then 'update' else null
end upd_status
from {{ ref('int_mgltestordermap') }} s
left outer join {{ this }} p on p.testid = s.testid
left outer join
(
select orderid, ordernumber
from {{ this }}
group by orderid, ordernumber
) existingids on stage.ordernumber = existingids.ordernumber
),
updates as (
select
reportmaildate
,upd_originalreportmaildate originalreportmaildate
,upd_ordernumber ordernumber
,upd_orderid orderid
,upd_preciseflag preciseflag
from cte_mgltestordermap
{% if is_incremental() %}
where testid in (select testid from {{ this }})
and upd_status = 'update'
{% endif %}
),
inserts as (
select
testid
,sysdate() recordlastupdated_utc
,hash(ordernumber) orderid
,ordernumber
,reportmaildate
,originalreportmaildate originalreportmaildate
,preciseflag
from cte_mgltestordermap
where testid not in (select testid from {{ this }})
)
select * from updates union inserts