CODEWITHSUNDEEP
sqlhive
Billy
Billy

Reputation: 23

Error while compiling statement: FAILED: ParseException line 4:0 cannot recognize input near '(' 'select' 'applicationprofileid' in select clause

create table sandbox_p_measurable_security.temp_calc_myaccess_privilegeddata1
stored as parquet as (
    select 
        applicationprofileid, 
        'OneBridge' as 'IsOnebridge' 
    from bigdata_normalized.myaccess_tblapplicationprofile apro
    where apro.applicationprofileid in (
        select apd.applicationprofileid 
        from bigdata_normalized.myaccess_tblapplicationprofiledependencies apd
        inner join bigdata_normalized.myaccess_tblsecuritygroup sg 
            on sg.securitygroupid = apd.securitygroupid
        where sg.businessrefcde = 'CDSOneBridge'
    )
);

Upvotes: 1

Views: 11720

Answers (1)

Gomz
Gomz

Reputation: 858

I was able to ge the same error as you got and it got resolved after removing the round bracket ( you have used after stored as parquet as

Test Results:

Failure:

Create table customers1
as (select * from customers)

Error as:

Error while compiling statement: FAILED: ParseException line 2:3 cannot recognize input near '(' 'select' '*' in select clause

Success:

Create table customers1
as select * from customers

 Success.

Try running the query as below (Removed the round brackets)

create table sandbox_p_measurable_security.temp_calc_myaccess_privilegeddata1
as
    select 
        applicationprofileid, 
        'OneBridge' as 'IsOnebridge' 
    from bigdata_normalized.myaccess_tblapplicationprofile apro
    where apro.applicationprofileid in (
        select apd.applicationprofileid 
        from bigdata_normalized.myaccess_tblapplicationprofiledependencies apd
        inner join bigdata_normalized.myaccess_tblsecuritygroup sg 
            on sg.securitygroupid = apd.securitygroupid
        where sg.businessrefcde = 'CDSOneBridge')
stored as parquet;

Upvotes: 1

Related Questions