Home » SQL & PL/SQL » SQL & PL/SQL » Alternative Sql Query
Alternative Sql Query [message #669962] Sat, 26 May 2018 04:23 Go to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
nagaraju.ch wrote on Sat, 26 May 2018 05:23
Hi
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 #669964 is a reply to message #669962] Sat, 26 May 2018 11:57 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
How about UNION? A compound query: one query with the first part of the OR unioned to a query with the second part?
Re: Alternative Sql Query [message #669965 is a reply to message #669963] Sat, 26 May 2018 19:07 Go to previous messageGo to next message
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 #669966 is a reply to message #669964] Sat, 26 May 2018 19:08 Go to previous messageGo to next message
nagaraju.ch
Messages: 103
Registered: July 2007
Location: bangalore
Senior Member
Hi John,

Thanks for your response, Can you please explain bit more on this. As i have two OR statments and And between them.
Re: Alternative Sql Query [message #669969 is a reply to message #669965] Sun, 27 May 2018 11:12 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 #669972 is a reply to message #669969] Sun, 27 May 2018 14:58 Go to previous messageGo to next message
nagaraju.ch
Messages: 103
Registered: July 2007
Location: bangalore
Senior Member
CASE is not supported within ON clause
Re: Alternative Sql Query [message #669973 is a reply to message #669966] Sun, 27 May 2018 15:10 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
nagaraju.ch wrote on Sun, 27 May 2018 01:08
Hi 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.
Previous Topic: Adding up graphs
Next Topic: duplicate part of text message
Goto Forum:
  


Current Time: Thu Mar 28 04:56:26 CDT 2024