Hi, If I run below query in Hue UI, it runs successful, but in beeline CLI it fails saying missing 'EOF' use DB_xxx;
SET hive.execution.engine=tez;
SET hive.vectorized.execution.enabled=true;
SET hive.vectorized.execution.reduce.enabled= true;
SET hive.cbo.enable=true;
SET hive.stats.fetch.partition.stats=true;
SET hive.auto.convert.join=true;
SET hive.auto.convert.join.noconditionaltask.size=25000000;
SET hive.compute.query.using.stats=true;
SET hive.stats.fetch.column.stats =true;
insert into table db_xgl77sar.orc_claiminfo
select
CONCAT(CLAIMNUMBER,'-',EXP_NUMBER) AS EXP_ID,
Claimant_Age,
Case
when Claimant_Age >=0 and Claimant_Age < 3 then 'Infants'
when Claimant_Age >=3 and Claimant_Age < 8 then 'Preschoolers'
when Claimant_Age >=8 and Claimant_Age < 15 then 'General Schoolchildren'
when Claimant_Age >=15 and Claimant_Age < 25 then 'Teens'
when Claimant_Age >=25 and Claimant_Age < 40 then 'Young People'
when Claimant_Age >=40 and Claimant_Age < 60 then 'Mature Age People'
when Claimant_Age >=60 and Claimant_Age < 80 then 'Older People'
when Claimant_Age >=80 then 'Long-lived'
else 'N/A' END AS Claimant_Age_Group,
DATEDIFF(to_date(EXP_ORIGINAL_CLOSE_DATE),to_date(CLAIM_REPORT_DATE)) AS Rpt2Clsd,
case
when TOTAL_LOSS_ID is null then '0'
else '1'
end AS TOTAL_LOSS,
Claimnumber,
INCIDENT,
EXP_NUMBER,
Claim_Report_Date,
to_date(Date_of_Loss) As Date_of_Loss,
Claim_Close_Date,
EXP_ORIGINAL_CLOSE_DATE,
EXP_CLOSEDATE,
EXP_REOPENDATE,
EXP_REOPENREASON,
Description,
Insured_Lability,
ACCIDENT_TYPE,
FAULT_RATING,
LOSS_CAUSE,
LOSS_TYPE,
LINE_OF_BUSINESS,
JurisdictionState,
LOSS_STATE,
POLICY_STATE,
SIU_STATUS,
CLAIMANT_NAME,
TO_DATE(CLAIMANT_DOB) as CLAIMANT_DOB,
CLAIMANT_GENDER,
CLAIMANT_MARITAL_STATUS,
CLAIMANT_OCCUPATION,
EXP_STATUS,
EXP_TYPE,
EXP_Coverage,
COMPENSABILITYCOMMENTS,
HitandRun,
TOTAL_LOSS_ID,
VEHICLE_STYLE,
Ambulance_Used,
Death_Date,
DisabledDueToAccident,
General_Injury_Type,
Impairment,
InjuredCheck_AAA,
Detailed_Body_Part,
Detailed_Injury_Type,
Pre_Exist_Cond,
case
when LOSS_STATE=POLICY_STATE then '1'
else '0'
end PSEQLS,
Date_of_Loss AS DOL_DAY,
case
when EXP_REOPENDATE is null then '0'
else '1'
end AS Reopened,
case
when Death_Date is null then '0'
else '1'
end AS DEATH,
from_unixtime(unix_timestamp()) AS RUN_DATE
from (SELECT C.Claimnumber,
C.IncidentReport AS INCIDENT,
E.claimorder AS EXP_NUMBER,
to_date(C.createtime) AS Claim_Report_Date,
to_date(C.lossdate) AS Date_of_Loss,
to_date(C.closedate) AS Claim_Close_Date,
to_date(E.INITIALCLOSEDATE_AAA) AS EXP_ORIGINAL_CLOSE_DATE,
to_date(E.CLOSEDATE) AS EXP_CLOSEDATE,
to_date(E.REOPENDATE) AS EXP_REOPENDATE,
E.ReopenedReason AS EXP_REOPENREASON,
C.Description,
C.fault AS Insured_Lability,
C.LOSSCAUSE AS ACCIDENT_TYPE,
C.FAULTRATING AS FAULT_RATING,
C.LOSSCAUSE AS LOSS_CAUSE,
C.LOSSTYPE AS LOSS_TYPE,
C.LOBCODE AS LINE_OF_BUSINESS,
E.JURISDICTIONSTATE AS JurisdictionState,
ADR.STATE AS LOSS_STATE,
P.POLICYSTATE_AAA AS POLICY_STATE,
C.SIUSTATUS AS SIU_STATUS,
CONCAT(CT.FIRSTNAME, ' ', CT.LASTNAME ) AS CLAIMANT_NAME,
to_date(CT.DateOfBirth) AS CLAIMANT_DOB,
DATEDIFF (to_date(FROM_UNIXTIME( UNIX_TIMESTAMP())),to_date(CT.DateOfBirth))/365 AS Claimant_Age,
CT.Gender AS CLAIMANT_GENDER,
CT.MaritalStatus AS CLAIMANT_MARITAL_STATUS,
CT.OCCUPATION AS CLAIMANT_OCCUPATION,
E.STATE AS EXP_STATUS,
E.EXPOSURETYPE AS EXP_TYPE,
E.COVERAGESUBTYPE AS EXP_Coverage,
BP.COMPENSABILITYCOMMENTS,
I.HitandRun,
I.ID AS TOTAL_LOSS_ID,
V.STYLE AS VEHICLE_STYLE,
I.AmbulanceUsed AS Ambulance_Used,
I.DeathDate_AAA AS Death_Date,
I.DisabledDueToAccident AS DisabledDueToAccident,
GeneralInjuryType AS General_Injury_Type,
I.Impairment,
I.InjuredCheck_AAA,
BP.DetailedBodyPart AS Detailed_Body_Part,
BP.InjuryType_AAA AS Detailed_Injury_Type,
BP.Pre_ExistCond_AAA AS Pre_Exist_Cond
FROM
(SELECT id,Claimnumber,IncidentReport,createtime,lossdate,closedate,Description,fault,LOSSCAUSE,FAULTRATING,LOSSTYPE,LOBCODE,SIUSTATUS,LOSSLOCATIONID,POLICYID FROM CC_Claim WHERE RETIRED = 0) C
INNER JOIN (SELECT claimorder,INITIALCLOSEDATE_AAA,CLOSEDATE,REOPENDATE,ReopenedReason,JURISDICTIONSTATE,STATE,EXPOSURETYPE,COVERAGESUBTYPE,CLAIMID,INCIDENTID,ClaimantDenormID FROM CC_Exposure WHERE RETIRED = 0) E
ON C.ID = E.CLAIMID
LEFT JOIN CC_Incident I
ON E.INCIDENTID = I.ID
LEFT JOIN cc_bodypart BP
ON I.ID = BP.INCIDENTID
LEFT JOIN cc_contact CT
ON E.ClaimantDenormID = CT.ID
LEFT JOIN CC_ADDRESS ADR
ON C.LOSSLOCATIONID = ADR.ID
LEFT JOIN CC_VEHICLE V
ON I.VEHICLEID = V.ID
LEFT JOIN (SELECT id,POLICYSTATE_AAA FROM CC_POLICY WHERE RETIRED = 0 ) P
ON C.POLICYID = P.ID
WHERE C.claimnumber LIKE '1000-%'
AND C.incidentreport <> 1
AND C.lobcode = '10001'
AND C.losscause NOT IN ('10010', '10049')
AND upper(C.Description) not like'%TEST%'
ORDER BY C.CLAIMNUMBER ASC
) clminfo; What could be the possible issue?? Thanks Mamta
... View more
