Getting exception while executing the query to compare two values

user_10839935 0
Viewed 51

While executing a query to compare the rates of two columns and display if one is grater than other I am getting ORA-01427 exception

Here is the data set. This is mocked up data . Data size is huge in databse Table1

col1 col2   col3 col4 col5 col6
c1   c1test 85   85   I    5
c2   c2test 85   85   I    3
c3   c3test 85   85   E    6
c4   c4test G1   G1   E    7
c5   c5test G1   G1   E    5
c6   c6test G1   G1   E    8
c7   c7test G1   G1   I    3
c8   c8test G1   G1   G    7

Table2

col1  col2  col3  col4  col5
85    85    D     I      3
85    85    D     E      5
G1    G1    D     E      5
G1    G1    D     I      3
G1    G1    D     G      5
G1    G1    E     I      2
G1    G1    E     E      2
85    85    E     I      3

Expected result We need to compare the value of col5 of table2 with the col6 of table1 to find out the values greater in col6 of table1 and display the records. Comparison needs to be done only for col3 equals D values.

col1 col2   col3 col4 col5 col6   
c1   c1test 81   81   I    5
c3   c3test 81   81   E    6
c4   c4test G1   G1   E    7
c6   c6test G1   G1   E    8
c8   c8test G1   G1   G    7

I am using the below query

Select * from table1 where 
col6 > (select col5 from 
table2 where col3='D'
and col1=table1.col3
and col2=table1.col4
and col4=table1.col5

This throws an ora-01427 exception. Can you pls hel to get the expected output.

oracle sql
2 Answers

Your subquery is returning multiple rows. You can use min() or max() to get around this. I'm not sure which logic you really want:

Select t1.*
from table1 t1
where t1.col6 > (select max(t2.col5)
                 from table2 t2
                 where t2.col3 = 'D' and
                       t2.col1 = t1..col3 and
                       t2.col2 = t1.col4 and
                       t2.col4 = t1.col5
                );
user_11440350

If you are looking for value grater than any value of table2.col5, try this

SELECT t1.*
  FROM table1 t1
 WHERE t1.col6 > ANY (SELECT t2.col5
                        FROM table2 t2
                       WHERE t2.col3 = 'D' 
                         AND t2.col1 = t1.col3 
                         AND t2.col2 = t1.col4 
                         AND t2.col4 = t1.col5
                     );

If you need table1.col6 should be greater than all values found in table2.col5 use ALL instead of ANY

user_138780230
Related