CODEWITHSUNDEEP
sqloracle-databaseconditional-statements
AegisX303
AegisX303

Reputation: 103

Display query results based on condition

More of a conceptual question. I have a query that calculates a sum of some values and checks it against a template value X, something like:

SELECT
    SUM(...),
    X,
    SUM(...) - X AS delta
FROM
...

Now the query by itself works fine. The problem is that I need it to only display some results if the delta variable is non-zero, meaning there is a difference between the calculated sum and template X. If delta is zero, I need it to display nothing.

Is this possible to achieve in SQL? If yes, how would I go about it?

Upvotes: 0

Views: 162

Answers (2)

Popeye
Popeye

Reputation: 35900

Oracle does not support column alias in the HAVING clause. so in oracle, You must have to repeat the aggregation in the HAVING clause.

See this:

SQL> SELECT MAX(COL1) AS RES,
  2         COL2
  3    FROM (select 1 as col1, 1 as col2 from dual
  4          union all
  5          select 10 as col1, 2 as col2 from dual)
  6   GROUP BY COL2
  7  HAVING RES > 5;
HAVING RES > 5
       *
ERROR at line 7:
ORA-00904: "RES": invalid identifier


SQL> SELECT MAX(COL1) AS RES,
  2         COL2
  3    FROM (select 1 as col1, 1 as col2 from dual
  4          union all
  5          select 10 as col1, 2 as col2 from dual)
  6   GROUP BY COL2
  7  HAVING MAX(COL1) > 5;

       RES       COL2
---------- ----------
        10          2

SQL>

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You have an aggregation query. Many, if not most, databases support column aliases in the having clause:

select . . .
from . . .
group by . . .
having delta <> 0;

For those that don't, it is probably simplest to repeat the expressions:

having sum( . . . ) <> X

You can also put the query into a CTE or subquery, and then use where on the subquery.

Upvotes: 2

Related Questions