impala alternative to oracle dual table

[#1]

In oracle it is possibile to use a statement like this

select something from dual

where dual is a special table on which you could select any field you like to

It is a special purpose table that it is often used

Any alternative in impala?

At the moment what i need to do is a simple

select 1 as type from dual

thanks

[#2]

I'm not sure I follow your question.

What exactly is "dual"? Can you provide more information?

[#3]

"dual" is a keyword in oracle sql.

It represents a fake table from which you can select whatever you want.

It is extremely useful for a lot of etl tasks.

For example if you need to generate a list of one hundred numbers you can do something like this

SELECT ROWNUM rn FROM dual CONNECT BY 1=1 AND ROWNUM < 1001

(rownum is another special keyword in oracle to have an id of the row)

Another example is this 

select 1 id, 'a' desc from dual union select 2 id, 'b' desc from dual

creating on the fly a simple table with 2 rows

1,'a'

2,'b'

and so on.

This on the fly generated table data can then be used and joined with other table and so on.

At the moment with impala i've create a real table to do the job inserting all the needed data

Beleive me that this fake table became very handy in a lot of situations

bye

[#4 · Accepted solution]

Impala allows you to select without a table, e.g.:

select 1 id, 'a' d UNION select 2 id, 'b' d;

('desc' is a keyword in Impala's SQL dialect). So 'dual' is not necessary for these kinds of on-the-fly table patterns.

Henry 

[#5]

What will be equivalent of below Oracle Query in Impala.

select 1 id, 'a' d from dual where not exists (select 1 from employee where empid > 20000)

[#6]

You can create a 1-row dummy table like this:

select 1 id, 'a' d from (select 1) dual where 1 = 1

You also have to rewrite the query to avoid an uncorrelated not exists. You can do something like:

select 1 id, 'a' d from (select 1) dual where (select count(*) from employee where empid > 20000) = 0

Computing the count might be expensive so you could add a limit like

select 1 id, 'a' d from (select 1) dual where (select count(*) from (select id from employee where empid > 20000 limit 1) emp) = 0