You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Viral Parikh <vi...@gmail.com> on 2014/09/12 18:34:24 UTC
Re: Return value from previous record if value is -1 using HIVE
To Whomsoever It May Concern,
I've got a table that contains a LocationId field. In some cases, where a
record shares the same foreign key, the LocationId might come through as -1.
What I want to do is in my select query is in the case of this happening,
the previous location.
Example data:
Record FK StartTime EndTime Location1
110 2011/01/01 12.30 2011/01/01 6.10 4562 110
2011/01/01 3.40 2011/01/01 4.00 -13 110
2011/01/02 1.00 2011/01/02 8.00 8914 110
2011/01/02 5.00 2011/01/02 6.00 -15 110
2011/01/02 6.10 2011/01/02 6.30 -1
The -1 should come out as 456 for record 2, and 891 for record 4 and 5
Can someone help me do this wit Hive syntax?
I can do it using SQL syntax (as below) but since Hive doesnt support
subqueries in select clauses I am unable to get it.
SELECT T1.record,
T1.fk,
T1.start_time,
T1.end_time,
CASE WHEN T1.location != -1 THEN Location
ELSE
(
SELECT TOP (1)
T2.location
FROM #temp1 AS T2
WHERE T2.record < T1.record
AND T2.fk = T1.fk
AND T2.location != -1
ORDER BY T2.Record DESC
)
ENDFROM #temp1 AS T1
Thank you for your help in advance!
On Fri, Sep 12, 2014 at 11:33 AM, Viral Parikh <vi...@gmail.com>
wrote:
> To Whomsoever It May Concern,
>
> I've got a table that contains a LocationId field. In some cases, where a
> record shares the same foreign key, the LocationId might come through as -1.
>
> What I want to do is in my select query is in the case of this happening,
> the previous location.
>
> Example data:
>
> Record FK StartTime EndTime Location1 110 2011/01/01 12.30 2011/01/01 6.10 4562 110 2011/01/01 3.40 2011/01/01 4.00 -13 110 2011/01/02 1.00 2011/01/02 8.00 8914 110 2011/01/02 5.00 2011/01/02 6.00 -15 110 2011/01/02 6.10 2011/01/02 6.30 -1
>
> The -1 should come out as 456 for record 2, and 891 for record 4 and 5
>
> Can someone help me do this wit Hive syntax?
>
> I can do it using SQL syntax (as below) but since Hive doesnt support
> subqueries in select clauses I am unable to get it.
>
> SELECT T1.record,
> T1.fk,
> T1.start_time,
> T1.end_time,
> CASE WHEN T1.location != -1 THEN Location
> ELSE
> (
> SELECT TOP (1)
> T2.location
> FROM #temp1 AS T2
> WHERE T2.record < T1.record
> AND T2.fk = T1.fk
> AND T2.location != -1
> ORDER BY T2.Record DESC
> )
> ENDFROM #temp1 AS T1
>
> Thank you for your help in advance!
>