Keep error on MODIFIEDDATE that appears in two tables in incremental load

In-Depth Discussions
1

HI,

I’ve the following query:

{{
config(
materialized = ‘incremental’,
)
}}
WITH CTE_stg_SalesOrderHeader AS (
SELECT *
FROM SALESLT.stg_SalesOrderHeader
),
CTE_stg_SalesOrderDetail AS (
SELECT
LINETOTAL
,UNITPRICEDISCOUNT
,UNITPRICE
,SALESORDERDETAILID
,SALESORDERID
,PRODUCTID
FROM SALESLT.stg_SalesOrderdetail
)
SELECT
SOH.CUSTOMERID --FK dimension
,SOD.PRODUCTID --FK dimension
,SOH.SALESORDERID --DD
,SOH.PURCHASEORDERNUMBER
,SOH.ACCOUNTNUMBER
,SOH.REVISIONNUMBER
,SOH.COMMENT
,SOH.SHIPMETHOD
,SOH.ONLINEORDERFLAG
,TO_NUMBER(TO_CHAR(SOH.ORDERDATE,‘yyyymmdd’)) AS ORDERDATEID --FK Calendar dimension
,SOH.MODIFIEDDATE
,TO_NUMBER(TO_CHAR(SOH.SHIPDATE,‘yyyymmdd’)) AS SHIPDATEID
,TO_NUMBER(TO_CHAR(SOH.DUEDATE,‘yyyymmdd’)) AS DUEDATEID
,SOH.STATUS
,SOH.CREDITCARDAPPROVALCODE
,SOH.FREIGHT
,SOD.LINETOTAL
,SOD.UNITPRICEDISCOUNT
,SOD.UNITPRICE
,SOD.SALESORDERDETAILID
FROM CTE_stg_SalesOrderHeader SOH
INNER JOIN CTE_stg_SalesOrderDetail SOD ON SOH.SALESORDERID = SOD.SALESORDERID
{% if is_incremental() %}
WHERE SOH.MODIFIEDDATE > (SELECT MAX(MODIFIEDDATE) FROM {{this}})
{% endif %}

It gives an error :

Subquery containing correlated aggregate function [MAX(SYS_VW.MODIFIEDDATE_6)] can only appear in having or select clause

IF I have this query

{{
config(
materialized = ‘incremental’,
)
}}
WITH CTE_stg_SalesOrderHeader AS (
SELECT *
FROM SALESLT.stg_SalesOrderHeader
),
CTE_stg_SalesOrderDetail AS (
SELECT *
– LINETOTAL
– ,UNITPRICEDISCOUNT
– ,UNITPRICE
– ,SALESORDERDETAILID
– ,SALESORDERID
– ,PRODUCTID
FROM SALESLT.stg_SalesOrderdetail
)
SELECT
SOH.CUSTOMERID --FK dimension
,SOD.PRODUCTID --FK dimension
,SOH.SALESORDERID --DD
,SOH.PURCHASEORDERNUMBER
,SOH.ACCOUNTNUMBER
,SOH.REVISIONNUMBER
,SOH.COMMENT
,SOH.SHIPMETHOD
,SOH.ONLINEORDERFLAG
,TO_NUMBER(TO_CHAR(SOH.ORDERDATE,‘yyyymmdd’)) AS ORDERDATEID --FK Calendar dimension
,SOH.MODIFIEDDATE
,TO_NUMBER(TO_CHAR(SOH.SHIPDATE,‘yyyymmdd’)) AS SHIPDATEID
,TO_NUMBER(TO_CHAR(SOH.DUEDATE,‘yyyymmdd’)) AS DUEDATEID
,SOH.STATUS
,SOH.CREDITCARDAPPROVALCODE
,SOH.FREIGHT
,SOD.LINETOTAL
,SOD.UNITPRICEDISCOUNT
,SOD.UNITPRICE
,SOD.SALESORDERDETAILID
FROM CTE_stg_SalesOrderHeader SOH
INNER JOIN CTE_stg_SalesOrderDetail SOD ON SOH.SALESORDERID = SOD.SALESORDERID
{% if is_incremental() %}
WHERE MODIFIEDDATE > (SELECT MAX(MODIFIEDDATE) FROM {{this}})
{% endif %}

I get an error

ambiguous column name ‘MODIFIEDDATE’

The only time I can get this working is using a date column that is not in both tables: Like ORDERDateID

{{
config(
materialized = ‘incremental’,
)
}}
WITH CTE_stg_SalesOrderHeader AS (
SELECT *
FROM SALESLT.stg_SalesOrderHeader
),
CTE_stg_SalesOrderDetail AS (
SELECT *
– LINETOTAL
– ,UNITPRICEDISCOUNT
– ,UNITPRICE
– ,SALESORDERDETAILID
– ,SALESORDERID
– ,PRODUCTID
FROM SALESLT.stg_SalesOrderdetail
)
SELECT
SOH.CUSTOMERID --FK dimension
,SOD.PRODUCTID --FK dimension
,SOH.SALESORDERID --DD
,SOH.PURCHASEORDERNUMBER
,SOH.ACCOUNTNUMBER
,SOH.REVISIONNUMBER
,SOH.COMMENT
,SOH.SHIPMETHOD
,SOH.ONLINEORDERFLAG
,TO_NUMBER(TO_CHAR(SOH.ORDERDATE,‘yyyymmdd’)) AS ORDERDATEID --FK Calendar dimension
,SOH.MODIFIEDDATE
,TO_NUMBER(TO_CHAR(SOH.SHIPDATE,‘yyyymmdd’)) AS SHIPDATEID
,TO_NUMBER(TO_CHAR(SOH.DUEDATE,‘yyyymmdd’)) AS DUEDATEID
,SOH.STATUS
,SOH.CREDITCARDAPPROVALCODE
,SOH.FREIGHT
,SOD.LINETOTAL
,SOD.UNITPRICEDISCOUNT
,SOD.UNITPRICE
,SOD.SALESORDERDETAILID
FROM CTE_stg_SalesOrderHeader SOH
INNER JOIN CTE_stg_SalesOrderDetail SOD ON SOH.SALESORDERID = SOD.SALESORDERID
{% if is_incremental() %}
WHERE ORDERDATEID > (SELECT MAX(ORDERDATEID) FROM {{this}})
{% endif %}