CODEWITHSUNDEEP
hiveleft-joinhiveql
cheers519
cheers519

Reputation: 455

Unable to use nonequi join(<=) in hive

I am new to hive and was trying to execute an query which contains nonequi join. But when I try to execute in hive 0.7 I am getting error due to nonequi join condition.

Query:

SELECT * INTO  dbo.tbl_B
 FROM ( select 
    Id,
    Name,
    Age,
    SellingPrice,
    row_number() OVER ( PARTITION BY Id,Name
ORDER BY Age asc) AS row_no
from tbl_A
LEFT JOIN dbo.tbl_C
ON A.[Id]=C.[Id]
and A.SellingPrice  <= C.BuyingPrice
) AA
WHERE row_no = 1 ;

Please provide me idea to rewrite this query without using nonequi join.

Upvotes: 2

Views: 760

Answers (2)

Akram ELSharkawy
Akram ELSharkawy

Reputation: 1

Solution not working for me

query used:

CREATE TABLE  IMART_PREP.TST (STRT_DT DATE , END_DT DATE, ID INT , SALARY INT );

INSERT INTO IMART_PREP.TST VALUES (   '2018-01-01' ,   '2018-01-31' ,1, 1000);
INSERT INTO IMART_PREP.TST VALUES (  '2018-02-01' , '2018-02-28' , 1, 2000);
INSERT INTO IMART_PREP.TST VALUES (  '2018-03-01' ,  '2018-03-30' , 1, 3000);
INSERT INTO IMART_PREP.TST VALUES (  '2018-04-01' ,  '2018-04-30' , 1, 4000);
SELECT * FROM IMART_PREP.TST;

CREATE TABLE  IMART_PREP.TST_1 (STRT_DT DATE , END_DT DATE, ID INT , SALARY INT );
INSERT INTO IMART_PREP.TST_1 VALUES (   '2018-01-01' ,   '2018-01-31', 1, 1000);


SELECT *
FROM IMART_PREP.TST A ,  IMART_PREP.TST_1 B
WHERE  ( (A.ID = B.ID ) AND (A.STRT_DT BETWEEN B.STRT_DT AND B.END_DT ) ) OR (B.STRT_DT IS NULL );

Upvotes: 0

leftjoin
leftjoin

Reputation: 38325

Move non-equi join condition to the where clause:

SELECT * 
 FROM ( select 
        Id,
        Name,
        Age,
        SellingPrice,
        row_number() OVER ( PARTITION BY Id,Name ORDER BY Age asc) AS row_no
from tbl_A
LEFT JOIN dbo.tbl_C
ON A.[Id]=C.[Id]
where (A.SellingPrice  <= C.BuyingPrice) OR (C.BuyingPrice is null)
) AA
WHERE row_no = 1 ;

OR (C.BuyingPrice is null) is necessary to allow left join, without this condition, left join will be transformed to inner join because non-equality condition alone as is will filter out nulls, see here.

Upvotes: 1

Related Questions