Thanks for your reply. I found out that this was because of an additional round brace near line. I have corrected it . But now am getting a similar error again. I figured out that this is because of a missing brace for 'CONNECTION TO HADOOP' statement but cannot figure out where to place it as it is giving me error every time. Error: ERROR: Prepare error: Method not supportedError while compiling statement: FAILED: ParseException line 1:760 cannot recognize input near ';' '<EOF>' '<EOF>' in lateral view SQL statement: with ecs as ( select xml_data, application_number,interface_id, get_json_object(xml_data,'$.application.applicationid')as applicationid, regexp_replace(regexp_replace(get_json_object(xml_data,'$.application.applicants[0].internalSystem.ecs[]'), '\\[|\\]', ''), '\\}\\,\\{', '\\}\\;\\{') AS ecs_JSOn from gcgdlkmxusg_ecrm_db.ci_rpdm_t_d where application_number='20240829000002810' AND INTERFACE_ID=102 ) select application_number, INTERFACE_ID, applicationid, get_json_object(ecs_item,'$.productLine') as productLine, get_json_object(ecs_item,'$.productCode') as productCode, get_json_object(ecs_item,'$.creditLineAmtCards') as creditLineAmtCards, get_json_object(ecs_item,'$.cin') as cin from ecs LATERAL VIEW OUTER EXPLODE(split(ecs_json, ';')) p AS ecs_item; PROC SQL;
CONNECT TO HADOOP(
host = "bigdataplatform-gcg-&ab..lac.nsroot.net"
schema = &ESQUEMA.
HDFS_TEMPDIR = "/data/&HDFS./work/hive/&HDFS._work/"
DBMAX_TEXT = 32768
uri = "jdbc:hive2://bigdataplatform-gcg-&amb..lac.nsroot.net:10000/&ESQUEMA.;principal=hive/bigdataplatform-gcg-&amb..lac.nsroot.net@LAC.NSROOT.NET;ssl=true"
scratch_db = "&HDFS._work"
subchar=questionmark
);
CREATE TABLE Applicant AS
SELECT *
FROM CONNECTION TO HADOOP(
with ecs as (
select
xml_data, application_number,interface_id,
get_json_object(xml_data,'$.application.applicationid')as applicationid,
regexp_replace(regexp_replace(get_json_object(xml_data,'$.application.applicants[0].internalSystem.ecs[]'), '\\[|\\]', ''), '\\}\\,\\{', '\\}\\;\\{') AS ecs_JSOn
from gcgdlkmxsg_ecrm_db.ci_rpdm_t_d
where application_number='202410' AND INTERFACE_ID=1089
)
select
application_number,
INTERFACE_ID,
applicationid,
get_json_object(ecs_item,'$.productLine') as productLine,
get_json_object(ecs_item,'$.productCode') as productCode,
get_json_object(ecs_item,'$.creditLineAmtCards') as creditLineAmtCards,
get_json_object(ecs_item,'$.cin') as cin
from ecs
LATERAL VIEW OUTER EXPLODE(split(ecs_json, ';')) p AS ecs_item;)
/* disconnect from hadoop;*/
quit;
... View more
