hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From yogesh dhari <yogeshh...@gmail.com>
Subject Re: Hive Query :: Implementing case statement
Date Thu, 20 Feb 2014 07:05:04 GMT
Hello Stephen ,

Yes, actully I have used Left Outer Join instead of Join, there were left
outer joins in RDBMS Query instead of join.

Thanks again :)



On Thu, Feb 20, 2014 at 10:45 AM, Stephen Sprague <spragues@gmail.com>wrote:

> Hi Yogesh,
>
> i overlooked one thing and for completeness we should make note of it here.
>
> change:
>
>
> -- non-intersected rows
>    select a.* from TABLE_SQL a join NEW_BALS b on (a.key=b.key) where
> b.NEW_BALANCE is null
>
>  to
>
> -- non-intersected rows
>    select a.* from TABLE_SQL a *LEFT OUTER* join NEW_BALS b on
> (a.key=b.key) where b.NEW_BALANCE is null
>
>
> This takes care of the rows where code != 1 and code != 2.
>
>
> But you knew that!
>
>
>
>
> On Wed, Feb 19, 2014 at 8:33 PM, yogesh dhari <yogeshhive@gmail.com>wrote:
>
>> Thanks a lot Stephen Sprague  :) :)
>>
>> It worked..  , just to remove the " ; "   from here, bcoz it was throuig
>> sub query systax error...
>>
>>
>>
>> create table NEW_BALS as
>> select * from (
>> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_2 b
>> on (a.key=b.key) where a.code='1';
>> UNION ALL
>> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_3 b
>> on (a.key=b.key) where a.code='2';
>> ) z
>> ;
>>
>>
>>
>> to
>>
>> create table NEW_BALS as
>> select * from (
>> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_2 b
>> on (a.key=b.key) where a.code='1'
>>       UNION ALL
>> select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_3 b
>> on (a.key=b.key) where a.code='2'
>>   ) z
>> ;
>>
>> Thanks & Regards
>> Yogesh Kumar
>>
>>
>> On Tue, Feb 18, 2014 at 9:18 PM, Stephen Sprague <spragues@gmail.com>wrote:
>>
>>> maybe consider something along these lines. nb. not tested.
>>>
>>> -- temp table holding new balances + key
>>>
>>> create table NEW_BALS as
>>>   select * from (
>>>       select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join
>>> TABLE_SQL_2 b on (a.key=b.key) where a.code='1';
>>>       UNION ALL
>>>       select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join
>>> TABLE_SQL_3 b on (a.key=b.key) where a.code='2';
>>>   ) z
>>>   ;
>>>
>>> -- i gave the table a new name but you could use TABLE_SQL instead of
>>> NEW_TABLE_SQL for the true update.
>>>
>>> insert overwrite table NEW_TABLE_SQL
>>>
>>>    select * from (
>>>
>>> -- intersected rows - you'll have to line-up the columns correctly
>>> substituting b.NEW_BALANCE into the right position
>>>    select  a.col1, b.NEW_BALANCE, a.col2, ... from TABLE_SQL a join
>>> NEW_BALS b on (a.key=b.key)
>>>
>>>    UNION ALL
>>>
>>> -- non-intersected rows
>>>    select a.* from TABLE_SQL a join NEW_BALS b on (a.key=b.key) where
>>> b.NEW_BALANCE is null
>>>
>>>    ) z
>>> ;
>>>
>>> there's probably some typos in there but hopefully you get the idea and
>>> can take it from here.
>>>
>>>
>>>
>>>
>>>
>>> On Tue, Feb 18, 2014 at 4:39 AM, yogesh dhari <yogeshhive@gmail.com>wrote:
>>>
>>>> Hello All,
>>>>
>>>> I have a use case where a table say TABLE_SQL is geting updated like.
>>>>
>>>>
>>>> 1st Update Command
>>>>
>>>> update TABLE_SQL a
>>>> set BALANCE = b.prev
>>>> from TABLE_SQL_2 b
>>>> where a.key = b.key and a.code = "1"
>>>>
>>>>
>>>> 2nd Update Command
>>>>
>>>> update TABLE_SQL a
>>>> set BALANCE = b.prev
>>>> from TABLE_SQL_3 b
>>>> where a.key = b.key and a.code = "2"
>>>>
>>>>
>>>> same column is getting update twice in sql table,
>>>>
>>>> I have a Table in Hive say TABLE_HIVE.
>>>>
>>>> How to perform this kind operatation in HIVE,
>>>>
>>>> Pls Help,
>>>>
>>>> Thanks in Advance
>>>> Yogesh Kumar
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>

Mime
View raw message