How to use the results of a SELECT query in a WHERE clause

Question

Coming from Teradata, I would usually create a volatile table with some variables that I would use throughout my code.

E.g.,

create volatile table var as (
select 'filter_value' as var_field
) with data on commit preserve rows;

Then I would use that table in a SELECT WHERE clause:

select * from table
where some_field = (select var_field from var);

I am trying to do something similar in HUE (Impala editor) however getting an error:

create table var as
select 'filter_value' as var_field

select * from table
    where some_field = (select var_field from var)

AnalysisException: Syntax error in line 5:undefined: from table-name-hidden ^ Encountered: FROM Expected: CASE, CAST, DEFAULT, EXISTS, FALSE, IF, INTERVAL, NOT, NULL, REPLACE, TRUNCATE, TRUE, IDENTIFIER CAUSED BY: Exception: Syntax error

Does anyone know how to do this or replicate this feature in Hue?

It's convenient not having to define my variables throughout the whole code and keep them all at the top in one table.

Possible duplicate of [How to set variables in HIVE scripts](https://stackoverflow.com/questions/12464636/how-to-set-variables-in-hive-scripts) — mazaneicha, Jul 13 '19 at 16:14
In Hue specifically, http://gethue.com/sql-editor-variables/ — mazaneicha, Jul 13 '19 at 16:17

score 0 · Answer 1 · answered Jul 15 '19 at 13:35

I have a solution. I tried it in Hue and I was able to accomplish your goal.

Queries used in Teradata:

Query 1:

create volatile table var as (
select 'filter_value' as var_field
) with data on commit preserve rows;

Query 2:

select * from table
where some_field = (select var_field from var);

Queries that work in Impala for the same scenario as above:

Query 1:

create table var as
select 'filter_value' as var_field;

Query 2:

SELECT * FROM test
where test_field in (select var_field from var);

Steps performed to Test:

1. Volatile Table creation:

create table var as
select 'filter_value' as var_field;

+-------------------+
| summary           |
+-------------------+
| Inserted 1 row(s) |
+-------------------+

2. Volatile table data check:

SELECT * FROM var;

+--------------+
| var_field    |
+--------------+
| filter_value |
+--------------+

3. Sample table creation and data insertion:

CREATE TABLE test 
(test_field string);

Fetched 0 row(s) in 0.81s

----

INSERT INTO test
values ("filter_value");

Modified 1 row(s) in 5.64s

----

INSERT INTO test
values ("filter_value2");

Modified 1 row(s) in 0.32s
----

4. Sample table data check:

select * from test;

+---------------+
| test_field    |
+---------------+
| filter_value  |
| filter_value2 |
+---------------+

5. Goal:

SELECT * FROM test
where test_field in (select var_field from var);

+--------------+
| test_field   |
+--------------+
| filter_value |
+--------------+

How to use the results of a SELECT query in a WHERE clause

1 Answers1