Alternative Sql Query [message #669962] |
Sat, 26 May 2018 04:23 |
nagaraju.ch
Messages: 103 Registered: July 2007 Location: bangalore
|
Senior Member |
|
|
Hi
Can some one help me on creating alternative query for OR Operator.
FROM
table1 a
FULL OUTER JOIN
table2 b
ON (a.key=b.key)
FULL OUTER JOIN
table3 c
ON (a.key=c.key)
LEFT OUTER JOIN
table4 d
ON (c.key1=d.key1)
LEFT OUTER JOIN
table5 e
ON (b.col=e.col OR d.col=e.col) AND (b.col1=e.col1 OR d.col1=e.col1)
in our hive code, there is no OR operator avalable so can someone help on writing this without OR in general sql.
Thanks
[Updated on: Sat, 26 May 2018 04:24] Report message to a moderator
|
|
|
Re: Alternative Sql Query [message #669963 is a reply to message #669962] |
Sat, 26 May 2018 05:33 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
nagaraju.ch wrote on Sat, 26 May 2018 05:23Hi
in our hive code, there is no OR operator avalable so can someone help on writing this without OR in general sql.
??? What product is it that it doesn't allow OR? Anyway:
ON 1 = case
when b.col=e.col then 1
when d.col=e.col then 1
end
AND 1 = case
when b.col1=e.col1 then 1
when d.col1=e.col1 then 1
end
SY.
|
|
|
|
Re: Alternative Sql Query [message #669965 is a reply to message #669963] |
Sat, 26 May 2018 19:07 |
nagaraju.ch
Messages: 103 Registered: July 2007 Location: bangalore
|
Senior Member |
|
|
thanks sy
hive is one of the bigdata product which APACHE HIVE.
hive dont allow non equality join which is limitation with the product. so i can't implement what you have suggested.
|
|
|
|
Re: Alternative Sql Query [message #669969 is a reply to message #669965] |
Sun, 27 May 2018 11:12 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
nagaraju.ch wrote on Sat, 26 May 2018 20:07
hive is one of the bigdata product which APACHE HIVE.
hive dont allow non equality join which is limitation with the product. so i can't implement what you have suggested.
I don't know APACHE HIVE, but according to Logical Operators OR is supported:
Operator Operand types Description
NOT A boolean TRUE if A is FALSE or NULL if A is NULL. Otherwise FALSE.
A OR B boolean TRUE if either A or B or both are TRUE, FALSE OR NULL is NULL, otherwise FALSE.
A NOT IN (val1, val2, ...) boolean TRUE if A is not equal to any of the values. As of Hive 0.13 subqueries are supported in NOT IN statements.
A IN (val1, val2, ...) boolean TRUE if A is equal to any of the values. As of Hive 0.13 subqueries are supported in IN statements.
A AND B boolean TRUE if both A and B are TRUE, otherwise FALSE. NULL if A or B is NULL.
[NOT] EXISTS (subquery) TRUE if the the subquery returns at least one row. Supported as of Hive 0.13.
! A boolean Same as NOT A.
Also, I didn't get "i can't implement what you have suggested". I suggested using CASE statement - are you saying CASE isn't supported either?
SY.
[Updated on: Sun, 27 May 2018 11:14] Report message to a moderator
|
|
|
|
Re: Alternative Sql Query [message #669973 is a reply to message #669966] |
Sun, 27 May 2018 15:10 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
nagaraju.ch wrote on Sun, 27 May 2018 01:08Hi John,
Thanks for your response, Can you please explain bit more on this. As i have two OR statments and And between them. Just have a go. Decompose your query into two (or more) queries, and UNION them together.
|
|
|