question Re: impala alternative to oracle dual table in Archives of Support Questions (Read Only)

impala alternative to oracle dual table

steghe — Fri, 16 Sep 2022 09:16:28 GMT

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

Re: impala alternative to oracle dual table

alex.behm — Mon, 29 Dec 2014 21:26:41 GMT

I'm not sure I follow your question.

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

Re: impala alternative to oracle dual table

steghe — Thu, 01 Jan 2015 10:52:08 GMT

"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

Re: impala alternative to oracle dual table

HenryR — Sun, 04 Jan 2015 23:30:04 GMT

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

Re: impala alternative to oracle dual table

manubatham20 — Wed, 20 Jan 2016 17:51:05 GMT

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)

Re: impala alternative to oracle dual table

Tim Armstrong — Wed, 20 Jan 2016 19:50:38 GMT

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