Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query that return selects a table and returns one column with a number, and I want to know if the result is > 0.

SELECT * FROM Table WHERE
    (SELECT amount from Table2) > 0

Something like this. But this way I get an error

ORA-01427: single-row subquery returns more than one row

How can I make it so it only grabs if amount is > 0?

share|improve this question

closed as off-topic by Paul White, RolandoMySQLDBA, Jack Douglas Feb 25 at 19:36

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, RolandoMySQLDBA, Jack Douglas
If this question can be reworded to fit the rules in the help center, please edit the question.

1  
This is a nonsensical query. How does table relate to table2? Can you give us the schema? –  Jacobm001 Feb 25 at 16:38

1 Answer 1

up vote 3 down vote accepted

Presumably, there is more than 1 row in Table2. Given that, how do you know which particular row(s) in Table2 you want to read the amount from for each row in Table. I'll assume that there is some sort of key in Table and Table2 that would allow you to determine which row to read.

Assuming that is the case

SELECT *
  FROM table t1
 WHERE EXISTS( SELECT 1
                 FROM table2 t2
                WHERE t1.some_key = t2.some_key
                  AND t2.amount > 0 )

If you want to return every row in Table if there are any rows in Table2 with an amount > 0, you can eliminate the correlation using the key. But that would be very odd

SELECT *
  FROM table t1
 WHERE EXISTS( SELECT 1
                 FROM table2 t2
                WHERE t2.amount > 0 )

Based on your comment, perhaps you could put the entire CASE statement in the subquery

SELECT *
  FROM table t1
 WHERE EXISTS( SELECT 1
                 FROM table2 t2
                WHERE t1.some_key = t2.some_key
                  AND (CASE WHEN YYY is NULL THEN 100 ELSE 0 END) > 0 )

but that would translate to the much simpler

SELECT *
  FROM table t1
 WHERE EXISTS( SELECT 1
                 FROM table2 t2
                WHERE t1.some_key = t2.some_key
                  AND t2.yyy IS NULL )
share|improve this answer
    
What if my amount is actually a CASE WHEN...? (SELECT CASE WHEN YYY is NULL THEN 100 ELSE 0 END amount from Table2) –  BrunoLM Feb 25 at 16:12
    
@BrunoLM - Edited my answer –  Justin Cave Feb 25 at 16:19
1  
The same might be written using ANY , for instance , ` WHERE 0<ANY(SELECT amount FROM table2). It should produce the same results and execution plan as EXISTS` ... –  a1ex07 Feb 25 at 16:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.