Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

impala alternative to oracle dual table

Solved Go to solution
Highlighted

impala alternative to oracle dual table

New Contributor

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

Accepted Solutions

Re: impala alternative to oracle dual table

Rising Star

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
Highlighted

Re: impala alternative to oracle dual table

Master Collaborator

I'm not sure I follow your question.

 

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

Highlighted

Re: impala alternative to oracle dual table

New Contributor

"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

Rising Star

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

Highlighted

Re: impala alternative to oracle dual table

New Contributor
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)
Highlighted

Re: impala alternative to oracle dual table

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

Don't have an account?
Coming from Hortonworks? Activate your account here