Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

impala alternative to oracle dual table

avatar
Explorer

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

1 ACCEPTED SOLUTION

avatar
Expert Contributor

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 

View solution in original post

5 REPLIES 5

avatar

I'm not sure I follow your question.

 

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

avatar
Explorer

"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

avatar
Expert Contributor

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 

avatar
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)

avatar

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