CODEWITHSUNDEEP
hivehiveql
Charitra kocheri
Charitra kocheri

Reputation: 127

Hive nested select statements

I am trying to execute nested select statements in Hive -

select col1, (select COUNT(*) as cnt from table2) , col2 from table1;

When I run above query I am getting below exception -

FAILED: ParseException line 1:8 cannot recognize input near 'select' 'COUNT' '(' in expression specification

I also tried assigning select count(*) to hivevar and using it in the query. But, Still I am getting same issue.

set hivevar:cnt=select COUNT(*) as cnt from table2;
select col1, ${hivevar:cnt} , col2 from table1;

Upvotes: 1

Views: 1515

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31716

You may use a WITH clause and CROSS JOIN with the main table.

WITH t AS (SELECT COUNT (*) AS ct FROM table2)
SELECT s.col1, t.ct, s.col2
  FROM table1 s CROSS JOIN t

Upvotes: 1

Related Questions