CODEWITHSUNDEEP
sqlhadoophive
Dee
Dee

Reputation: 411

SQL Hive subquery error

I have the query below

set hive.cli.print.header=true;
set hive.query.max.partition=1000;
set hive.mapred.mode=unstrict;

SELECT

dim_lookup("accounts",name,"account_id") = '28016' as company,
dim_lookup("campaigns",name,"campaign_id") in (117649,112311,112319,112313,107799,110743,112559,112557,105191,105231,107377,108675,106587,107325,110671,107329,107181,106565,105123,106569,106579,110835,105127,105243,107185,105211,105215) as campaign_name,
case when is_click_through=0 then "PV" else "PC" end as conv_type,
(SELECT COUNT(1) FROM impressions WHERE ad_info[2] in (117649,112311,112319,112313,107799,110743,112559,112557,105191,105231,107377,108675,106587,107325,110671,107329,107181,106565,105123,106569,106579,110835,105127,105243,107185,105211,105215)) AS impressions

FROM actions

WHERE
data_date>='20170101'
AND data_date<='20171231'
AND conversion_action_id in (20769223,20769214,20769219,20764929,20764932,20764935,20769215,20769216,20764919,20769218,20769217,20769220,20769222)
GROUP BY conv_type

When I execute it I get an error 

 ERROR ql.Driver: FAILED: ParseException line 8:1 cannot recognize input near 'SELECT' 'COUNT' '(' in expression specification

I am trying to fetch each count of impression for a specified conversion_action_id. What could be the error in my query? Thanks for the help.

FYI: ad_info[2] and campaign_id are the same.

Upvotes: 1

Views: 1242

Answers (1)

Dennis Jaheruddin
Dennis Jaheruddin

Reputation: 21561

The problem is quite clear, you have a subquery inside your SELECT.

That is not how this works.

Unfortunately the exact solution is not that clear, as it I am not completely sure what you want, but here is some general advice:

  1. Write your subquery, test it and make sure it is ok
  2. Rather than putting it in your SELECT part, put it in your FROM part, and (as always) SELECt from the FROM

Just think of your subquery output as an other table that can be used in the from statement, and which needs to be combined (JOIN, UNION?) with other tables in the from statement.

Upvotes: 1

Related Questions