invalid identifier while parsing json-CodePudding

I am compiling a dbt base model. Currently I get this error below. Line 6 looks the same as other lines above. Might a small syntax error that I could not spot.

15:40:22  Database Error in model base_datacenter_handling_unit (models/l10_staging_datacenter/base_unit.sql)
15:40:22    000904 (42000): SQL compilation error: error line 6 at position 3
15:40:22    invalid identifier 'VALUE'
15:40:22    compiled SQL at target/run/dbt/models/l10_staging_datacenter/base_unit.sql

This is how my file looks like:

SELECT 
    JSON_DATA:"key"::text AS KEY 
    , value:"description"::text AS DESCRIPTION 
  , value:"globalHandlingUnitId"::text AS GLOBAL_HANDLING_UNIT_ID
  , value:"tareWeight"::NUMBER(38,0) AS TARTE_WEIGHT
  , value:"tareWeight_unit"::text AS TARTE_WEIGHT_UNIT
  , value:"width"::NUMBER(38,0) AS WIDTH
  , value:"width_unit"::text AS WIDTH_UNIT
  , value:"length"::NUMBER(38,0) AS LENGTH
    , value:"validFrom"::TIMESTAMP_NTZ AS VALID_FROM_TS_UTC
    , value:"validTo"::TIMESTAMP_NTZ AS VALID_TO_TS_UTC
    , value:"lastModified"::TIMESTAMP_NTZ AS LAST_MODIFIED_TS_UTC
  , value:"status"::text AS STATUS
    , md5(KEY::STRING || MASTERCLIENT_ID) AS HANDLING_UNIT_KEY --different logic than in POSTGRESDWH! 
    ,MASTERCLIENT_ID
    ,{{ extract_masterclientname_clause('META_FILENAME') }} AS MASTERCLIENT_NAME
    ,META_ROW_NUM
  ,META_FILENAME
  ,META_LOAD_TS_UTC
  ,META_FILE_TS_UTC
  ,CASE WHEN {{table_dedup_clause('HANDLING_UNIT_KEY')}} 
    THEN True
    ELSE False
  END AS IS_RECORD_CURRENT
FROM {{ source('INGEST_DATACENTER', 'HANDLING_UNIT') }} src
QUALIFY {{table_dedup_clause('HANDLING_UNIT_KEY')}}

It could also be because of the STRING type md5(KEY::STRING || MASTERCLIENT_ID) I am using with md5 but I have another file, which is based on the same pattern but it does not throw an error tho:

SELECT 
    JSON_DATA:"issueId"::NUMBER(38,0) AS ISSUE_ID 
    , value:"slaName"::text AS SLA_NAME
    , value:"slaTimeLeft"::NUMBER(38,0) AS SLA_TIME_USED_SECONDS
    , md5(ISSUE_ID::STRING || SLA_NAME) AS ISSUE_SLA_ID  
    ,MASTERCLIENT_ID
    ,{{ extract_masterclientname_clause('META_FILENAME') }} AS MASTERCLIENT_NAME
    ,META_ROW_NUM
  ,META_FILENAME
  ,META_LOAD_TS_UTC
  ,META_FILE_TS_UTC
,CASE WHEN {{table_dedup_clause('ISSUE_SLA_ID')}}
    THEN True
    ELSE False
  END AS IS_RECORD_CURRENT
FROM {{ source('INGEST_EMS', 'ISSUES') }} src
    , lateral flatten ( input => JSON_DATA:slas)
QUALIFY {{table_dedup_clause('ISSUE_SLA_ID')}}

I don't see any significant difference between the two

CodePudding user response：

value is the output columns of a FLATTEN which you have in your second SQL. But not your first.

This is where putting an alias of every table, and using it on EVERY usage, you would see something like

SELECT t.json_data:"key",
   f.value:"json_prop_name"
FROM table AS t;

and be like, where does f come from...

CodePudding user response：

The most likely reason is the column is not named "tareWeight_unit". Snowflake creates column names in upper case regardless of how they are written unless the original create statement puts the columns names in double quotes (e.g. "MyColumn") in which case it will create the column names with the exact case specified. Use SHOW COLUMNS IN TABLE and check the actual column name.