How to select tuple from rows in oracle

user_2908457 0
Viewed 293

I have temp table that has following values:

╔═══════════╤═══════════╗
║ LATITUDE  │ LONGITUDE ║
╠═══════════╪═══════════╣
║ 69.122112 │ 39.122112 ║
╟───────────┼───────────╢
║ 69.123450 │ 39.123450 ║
╚═══════════╧═══════════╝

I want to do something like this

with temp as (select LATITUDE,LONGITUDE from SOME_TABLE_B)
select * from temp
union
select LATITUDE,LONGITUDE from SOME_TABLE where (LATITUDE, LONGITUDE) in (select (LATITUDE, LONGITUDE) from temp)

So as to select all other data that has similar latitude and longitude pair. But I get the following result:

ORA-00920 invalid relational operator

How can I select tuple from table, to use IN CLAUSE with tuple?

Thanks is advance

oracle12c oracle sql
3 Answers

I think the issue is the additional parentheses in the subquery. Try this:

select LATITUDE,LONGITUDE
from SOME_TABLE
where (LATITUDE, LONGITUDE) in (select LATITUDE, LONGITUDE from temp)
user_11440350

Not exactly sure about your requirement. If you want to check the data availability in temp table you can use below query,

select LATITUDE,LONGITUDE from SOME_TABLE where (LATITUDE, LONGITUDE) in (select 
LATITUDE, LONGITUDE from temp)

If you want to check avaliablity as well as union the data from temp, you can use below,

select LATITUDE,LONGITUDE from SOME_TABLE where (LATITUDE, LONGITUDE) in (select 
LATITUDE, LONGITUDE from temp)
UNION
select 
LATITUDE, LONGITUDE from temp
user_44736150

In your case you could also try using a join with a subquery:

select LATITUDE,LONGITUDE 
from SOME_TABLE  s 
INNER JOIN  ( 
    select  LATITUDE, LONGITUDE 
 from temp
 ) t on t.LATITUDE = s.LATITUDE 
    AND t.LONGITUDE  = s.LONGITUDE
user_35223120
Related