Beeline CLI fails compiling order by while Hue UI runs the query successfully

colorsoflife — Tue, 21 Apr 2026 13:55:12 GMT

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

Re: Beeline CLI fails compiling order by while Hue UI runs the query successfully

nsabharwal — Wed, 13 Jan 2016 02:34:02 GMT

@Mamta Chawla

There is very high probablity that it's syntax issue.

Re: Beeline CLI fails compiling order by while Hue UI runs the query successfully

paul_boal — Wed, 13 Jan 2016 03:09:52 GMT

Digging through your SQL for some syntactical things that might possibly throw off beeline differently than the Hive UI. No promises, but a couple of things to check.

Always use "AS" for column aliases. You're missing an "AS" here:

case
when LOSS_STATE=POLICY_STATE then '1'
else '0'
end PSEQLS,

If you're selecting from more than one table, always use the table alias in your select. You're missing one here:

  GeneralInjuryType       AS General_Injury_Type,

Let us know what you find. You might also try running your query through the hive CLI, too. Or running beeline in verbose mode.

Re: Beeline CLI fails compiling order by while Hue UI runs the query successfully

colorsoflife — Wed, 13 Jan 2016 03:20:48 GMT

If Syntax issue, Then how it is working in Hue??

Re: Beeline CLI fails compiling order by while Hue UI runs the query successfully

nsabharwal — Wed, 13 Jan 2016 03:23:32 GMT

@Mamta Chawla

Some kind of formatting while posting the query in beeline. Try with hive

hive -f /home/my/hive-script.sql

question Beeline CLI fails compiling order by while Hue UI runs the query successfully in Archives of Support Questions (Read Only)

Beeline CLI fails compiling order by while Hue UI runs the query successfully

Re: Beeline CLI fails compiling order by while Hue UI runs the query successfully

Re: Beeline CLI fails compiling order by while Hue UI runs the query successfully

Re: Beeline CLI fails compiling order by while Hue UI runs the query successfully

Re: Beeline CLI fails compiling order by while Hue UI runs the query successfully