โ08-02-2021 08:53 AM
I am getting the below error when I am using the expression to run using the filter. If I remove them and run it, it works fine. The double quotes are causing issues. Any help would be greatly appreciated? I have changed the filed names because of confidentiality issues. Thank You
ERROR:
Expression parsing failed near โ amap,'" >> { << โ, โ{โ),โ (Reason: Mismatched input โ{โ at line 9:60. Expecting one of: {, โ||โ, โ&&โ, โ^โ, โ==โ, โ!=โ, โ>โ, โ<โ, โ>=โ, โ<=โ, โ+โ, โ-โ, โ*โ, โ/โ, โ%โ, โ?โ, โinstanceofโ, โinโ, โmatchesโ, โ[โ, โ(โ, โ.โ, PropertyRef}; Resolution: Please check expression syntax)
SQL:
โREPLACE INTO abc.stg_order
select DISTINCT 1 as orderno
, 1 as orderId , 1 as subOrderId, 1 as itemId,
case when pi.amap is null then
JSON_EXTRACT_STRING(REPLACE(REPLACE(REPLACE(pi.amap,'โ{โ, โ{โ),โ}โโ,โ}โ),โ\โโ,โโโ),โcountryโ)
else
REPLACE(pi.amap::country, โ"โ, โโ)
end as country
, case when pi.amap is null then JSON_EXTRACT_STRING(REPLACE(REPLACE(REPLACE(pi.att ,โโ{โ, โ{โ),โ}โโ, โ}โ),โ\โโ, โ"โ) ,โffโ)
else REPLACE(pi.amap::ff, โ"โ, โโ)
end as ff
, case when pi.amap is null then JSON_EXTRACT_STRING(REPLACE(REPLACE(REPLACE(pi.att ,โโ{โ, โ{โ),โ}โโ, โ}โ),โ\โ', 'โโ) ,โframeโ)
else REPLACE(pi.amap::frame, โ"โ, โโ)
end as frame
, case when pi.amap is null then JSON_EXTRACT_STRING(REPLACE(REPLACE(REPLACE(pi.att,โโ{โ, โ{โ),โ}โโ, โ}โ),โ\โ', 'โโ) ,โorientationโ)
else REPLACE(pi.amap::orientation, โ"โ, โโ)
end as orientation
, case when pi.amap is null then JSON_EXTRACT_STRING(REPLACE(REPLACE(REPLACE(pi.att,โ" {โ, โ{โ),โ}โโ, โ}โ),โ\โโ, โ"โ) ,โsideโ)
else REPLACE(pi.amap::side, โ"โ, โโ)
end as side
, case when pi.amap is null then REPLACE(REPLACE(REPLACE(pi.att ,โโ{โ, โ{โ),โ}โโ, โ}โ),โ\โโ,โโ')
else pi.amap
end as amap
,ci.componentId
, pi.ingest_ts
from
abc.product pi
JOIN abc.component ci on
pi.Id = ci.Id
WHERE pi.ingest_ts > โโ+_filter_1+โโ"
โ08-02-2021 12:15 PM
Hi @nosingh,
2 tips:
"REPLACE INTO abc.stg_order
select DISTINCT 1 as orderno
, 1 as orderId , 1 as subOrderId, 1 as itemId,
case when pi.amap is null then
JSON_EXTRACT_STRING(REPLACE(REPLACE(REPLACE(pi.amap,'\"{', '{'),'}\"','}'),'\\\"','\"'),'country')
else
REPLACE(pi.amap::country, '\"', '')
end as country
, case when pi.amap is null then JSON_EXTRACT_STRING(REPLACE(REPLACE(REPLACE(pi.att ,'\"{', '{'),'}\"', '}'),'\\\"', '\"') ,'ff')
else REPLACE(pi.amap::ff, '\"', '')
end as ff
, case when pi.amap is null then JSON_EXTRACT_STRING(REPLACE(REPLACE(REPLACE(pi.att ,'\"{', '{'),'}\"', '}'),'\\\"', '\"') ,'frame')
else REPLACE(pi.amap::frame, '\"', '')
end as frame
, case when pi.amap is null then JSON_EXTRACT_STRING(REPLACE(REPLACE(REPLACE(pi.att,'\"{', '{'),'}\"', '}'),'\\\"', '\"') ,'orientation')
else REPLACE(pi.amap::orientation, '\"', '')
end as orientation
, case when pi.amap is null then JSON_EXTRACT_STRING(REPLACE(REPLACE(REPLACE(pi.att,'\" {', '{'),'}\"', '}'),'\\\"', '\"') ,'side')
else REPLACE(pi.amap::side, '\"', '')
end as side
, case when pi.amap is null then REPLACE(REPLACE(REPLACE(pi.att ,'\"{', '{'),'}\"', '}'),'\\\"','\"')
else pi.amap
end as amap
,ci.componentId
, pi.ingest_ts
from
abc.product pi
JOIN abc.component ci on
pi.Id = ci.Id
WHERE pi.ingest_ts > '"+_filter_1+"'"