hi all:
     some one said hive support sql like  select if(1=2,100,200) from dual; however i always get Table not found 'dual' . my hive is 0.9.0 and what is the reason?
fellowtree

Do you even have a table name 'dual' or not?

On Thu, Oct 18, 2012 at 2:24 PM, fellowtree <[EMAIL PROTECTED]> wrote:
> hi all:
>      some one said hive support sql like  select if(1=2,100,200) from dual;
> however i always get Table not found 'dual' . my hive is 0.9.0 and what is
> the reason?
>
> ________________________________
> fellowtree

The 'dual' table is a feature in Oracle only I believe.

There's an open Jira for this.  You can try Edward Capriolo's project
(described in the jira) which allows you to use dual.
https://issues.apache.org/jira/browse/HIVE-1558

On Thu, Oct 18, 2012 at 2:29 AM, MiaoMiao <[EMAIL PROTECTED]> wrote:
> Do you even have a table name 'dual' or not?
>
> On Thu, Oct 18, 2012 at 2:24 PM, fellowtree <[EMAIL PROTECTED]> wrote:
>> hi all:
>>      some one said hive support sql like  select if(1=2,100,200) from dual;
>> however i always get Table not found 'dual' . my hive is 0.9.0 and what is
>> the reason?
>>
>> ________________________________
>> fellowtree

I creates a single column, single row table called 'dual'
1. Create a text file on HDFS, call it a.txt with just the letter 'a' (or any single character in it)
2. Create the single column table, dual: 'create table dual (x, string);'
3. Load the text file into dual: load data local inpath '... /a.txt' overwrite into table dual;  (replace '...' with the path to the HDFS directory you created a.txt in)
4. Enjoy using it just like you do in Oracle: select parse_url('http://autos.aol.com/gallery/best-trucks-fuel-economy/', 'HOST') from dual

A quick AOL search and I see this guy did pretty much the same:

http://bigdatadw.blogspot.com/2012/02/dual-table-in-hive.html

---
Mark E. Sunderlin
Solutions Architect |AOL Core Data Technologies
P: 703-265-6935     |C: 540-327-6222 | AIM: MESunderlin
22000 AOL Way | Dulles, VA | 20166

-----Original Message-----
From: Kaufman Ng [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 18, 2012 9:45 AM
To: [EMAIL PROTECTED]
Cc: fellowtree
Subject: Re: why i get Table not found 'dual'

The 'dual' table is a feature in Oracle only I believe.

There's an open Jira for this.  You can try Edward Capriolo's project (described in the jira) which allows you to use dual.
https://issues.apache.org/jira/browse/HIVE-1558

On Thu, Oct 18, 2012 at 2:29 AM, MiaoMiao <[EMAIL PROTECTED]> wrote:
> Do you even have a table name 'dual' or not?
>
> On Thu, Oct 18, 2012 at 2:24 PM, fellowtree <[EMAIL PROTECTED]> wrote:
>> hi all:
>>      some one said hive support sql like  select if(1=2,100,200) from
>> dual; however i always get Table not found 'dual' . my hive is 0.9.0
>> and what is the reason?
>>
>> ________________________________
>> fellowtree

That way works but if someone accidentally adds a second row or
deletes the first row dual stops working right.

This was why I wrote dual input format. It always works right
regardless if the table has data.

Edward

On Thu, Oct 18, 2012 at 12:09 PM, Sunderlin, Mark
<[EMAIL PROTECTED]> wrote:
> I creates a single column, single row table called 'dual'
>
>
> 1.      Create a text file on HDFS, call it a.txt with just the letter 'a' (or any single character in it)
> 2.      Create the single column table, dual: 'create table dual (x, string);'
> 3.      Load the text file into dual: load data local inpath '... /a.txt' overwrite into table dual;  (replace '...' with the path to the HDFS directory you created a.txt in)
> 4.      Enjoy using it just like you do in Oracle: select parse_url('http://autos.aol.com/gallery/best-trucks-fuel-economy/', 'HOST') from dual
>
> A quick AOL search and I see this guy did pretty much the same:
>
> http://bigdatadw.blogspot.com/2012/02/dual-table-in-hive.html
>
> ---
> Mark E. Sunderlin
> Solutions Architect |AOL Core Data Technologies
> P: 703-265-6935     |C: 540-327-6222 | AIM: MESunderlin
> 22000 AOL Way | Dulles, VA | 20166
>
> -----Original Message-----
> From: Kaufman Ng [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, October 18, 2012 9:45 AM
> To: [EMAIL PROTECTED]
> Cc: fellowtree
> Subject: Re: why i get Table not found 'dual'
>
> The 'dual' table is a feature in Oracle only I believe.
>
> There's an open Jira for this.  You can try Edward Capriolo's project (described in the jira) which allows you to use dual.
> https://issues.apache.org/jira/browse/HIVE-1558
>
> On Thu, Oct 18, 2012 at 2:29 AM, MiaoMiao <[EMAIL PROTECTED]> wrote:
>> Do you even have a table name 'dual' or not?
>>
>> On Thu, Oct 18, 2012 at 2:24 PM, fellowtree <[EMAIL PROTECTED]> wrote:
>>> hi all:
>>>      some one said hive support sql like  select if(1=2,100,200) from
>>> dual; however i always get Table not found 'dual' . my hive is 0.9.0
>>> and what is the reason?
>>>
>>> ________________________________
>>> fellowtree

Ed's solution is clearly superior(!), if, your admin team will install it.  In some cases, your admin may not wish to do so.  In such cases, the below 'self-service' method will work. :-)
---
Mark E. Sunderlin
Solutions Architect |AOL Core Data Technologies
P: 703-265-6935     |C: 540-327-6222 | AIM: MESunderlin
22000 AOL Way | Dulles, VA | 20166
-----Original Message-----
From: Edward Capriolo [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 18, 2012 12:13 PM
To: [EMAIL PROTECTED]
Subject: Re: why I get Table not found 'dual'

That way works but if someone accidentally adds a second row or deletes the first row dual stops working right.

This was why I wrote dual input format. It always works right regardless if the table has data.

Edward

On Thu, Oct 18, 2012 at 12:09 PM, Sunderlin, Mark <[EMAIL PROTECTED]> wrote:
> I creates a single column, single row table called 'dual'
>
>
> 1.      Create a text file on HDFS, call it a.txt with just the letter 'a' (or any single character in it)
> 2.      Create the single column table, dual: 'create table dual (x, string);'
> 3.      Load the text file into dual: load data local inpath '... /a.txt' overwrite into table dual;  (replace '...' with the path to the HDFS directory you created a.txt in)
> 4.      Enjoy using it just like you do in Oracle: select parse_url('http://autos.aol.com/gallery/best-trucks-fuel-economy/', 'HOST') from dual
>
> A quick AOL search and I see this guy did pretty much the same:
>
> http://bigdatadw.blogspot.com/2012/02/dual-table-in-hive.html
>
> ---
> Mark E. Sunderlin
> Solutions Architect |AOL Core Data Technologies
> P: 703-265-6935     |C: 540-327-6222 | AIM: MESunderlin
> 22000 AOL Way | Dulles, VA | 20166
>
> -----Original Message-----
> From: Kaufman Ng [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, October 18, 2012 9:45 AM
> To: [EMAIL PROTECTED]
> Cc: fellowtree
> Subject: Re: why i get Table not found 'dual'
>
> The 'dual' table is a feature in Oracle only I believe.
>
> There's an open Jira for this.  You can try Edward Capriolo's project (described in the jira) which allows you to use dual.
> https://issues.apache.org/jira/browse/HIVE-1558
>
> On Thu, Oct 18, 2012 at 2:29 AM, MiaoMiao <[EMAIL PROTECTED]> wrote:
>> Do you even have a table name 'dual' or not?
>>
>> On Thu, Oct 18, 2012 at 2:24 PM, fellowtree <[EMAIL PROTECTED]> wrote:
>>> hi all:
>>>      some one said hive support sql like  select if(1=2,100,200)
>>> from dual; however i always get Table not found 'dual' . my hive is
>>> 0.9.0 and what is the reason?
>>>
>>> ________________________________
>>> fellowtree