Using function inside CASE
I am trying to use the
NVL functions inside the case. The case is in the where clause of the select statement.
The code below gives the following error:
ORA-00905: missing keyword
AND ( CASE WHEN SUBSTR(upper(p_open_invoice),1,1) = 'Y' THEN NVL(P.AMOUNT_DUE_REMAINING,0) = 0 ELSE 1=1 END)
This looks like a syntax error around equal operator of
That is not how
case expressions work (in Oracle) -- there is no boolean type to return.
The simplest method is to remove the `case and express this as simple logic:
AND (SUBSTR(upper(p_open_invoice), 1, 1) <> 'Y' OR COALESCE(P.AMOUNT_DUE_REMAINING, 0) = 0 )
p_open_invoice can be
NULL, you need to take that into account as well.
SQL CASE, Using CASE inside of aggregate functions; Practice problems. For the next few lessons, you'll work� In this syntax, the CASE evaluates expressions specified in the WHEN clauses. If an expression evaluates to true. CASE returns the corresponding result in the THEN clause. Otherwise, it returns the result specified in the ELSE clause. In case the ELSE clause is not available, then the CASE expression returns NULL .
You cannot use a
collation as a result for
case..when statements, it's better converting the condition to
AND (( SUBSTR(upper(p_open_invoice),1,1) = 'Y' AND NVL(P.AMOUNT_DUE_REMAINING,0) = 0 ) OR SUBSTR(upper(p_open_invoice),1,1) != 'Y' )
SQL CASE works in practically all SQL-Databases, SQL offers two case abbreviations to cope with null : coalesce and nullif . Both are used like functions and do not use the keywords case , when , then , else and � The SQL CASE Statement. The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. If there is no ELSE part and no conditions are true, it returns NULL.
If you're accustomed to programming in PL/SQL you may have seen that there's a BOOLEAN type in PL/SQL. However, this is not true in the Oracle database itself. The way I usually work around this is to use character expressions which return 'Y' or 'N' instead of
Keeping this in mind - if you really want to use a CASE expression similar to what you had originally you can use the following:
AND CASE WHEN SUBSTR(upper(p_open_invoice),1,1) = 'Y' THEN CASE WHEN NVL(P.AMOUNT_DUE_REMAINING,0) = 0 THEN 'Y' ELSE 'N' END ELSE 'Y' END = 'Y'
Here the CASE expression returns either 'Y' or 'N', which is then compared with 'Y'.
Is is possible to use Aggregate functions inside CASE statement , Is is possible to use Aggregate functions inside CASE statement? I want to execute SUM of a field at different grouping levels is it possible to� In your query, the CASE expression has a non-aggregate expression, UPPER (attk.status), as well as an aggregate expression, COUNT (attk.status). Note that the non-aggregate expression is outside of the aggregate expression. Let's turn this inside out, and put the CASE expression inside the aggregate expression.
CASE Statement, The value of the CASE operand and WHEN operands in a simple CASE statement can you can code an assignment statement using a CASE expression instead. inside SQL queries, for example instead of a call to the DECODE function or� CREATE FUNCTION [FATMS].[fnReturnByPeriod] ( @Period INT ) RETURNS int AS BEGIN SELECT CASE @Period when 1 then 1 when @Period >1 and @Period <=7 then 1 when @Period >7 and @Period <=30 then 1 when @Period >30 and @Period<=90 then 1 when @Period >90 and @Period <=180 then 1 when @Period >180 and @Period <=360 then 1 else 0 END RETURN @Period END
MySQL CASE Function, Well organized and easy to understand Web building tutorials with lots of examples of how The CASE statement goes through conditions and return a value when the first If no conditions are true, it will return the value in the ELSE clause. In certain cases, you may need to use a function as one of the arguments of another function. For example, the following formula uses a nested AVERAGE function and compares the result with the value 50. 1. The AVERAGE and SUM functions are nested within the IF function.
If you want such a nested function to be available at the module level, you'd have to either return it from the function, or define a global variable to store it in: def f1(a): def f2(x): return a+x return 2*a, f2
- Can you show us the full
SQLstatement? Are you running this from a script file? What is the goal in this
SQL? Can you change the title to ask a question? Please refer to the guidelines of asking questions.
- what can be done if there are too many conditions to be checked like in the below scenario: -- -- AND ( CASE WHEN p_trx_date_low Is Null and p_trx_date_high Is Null Then a.trx_date = a.trx_date WHEN p_trx_date_low Is Not Null and p_trx_date_high Is Null Then a.trx_date >= p_trx_date_low WHEN p_trx_date_low Is Null and p_trx_date_high Is Not Null Then a.trx_date <= p_trx_date_high WHEN p_trx_date_low Is Not Null and p_trx_date_high Is Not Null Then a.trx_date between p_trx_date_low and p_trx_date_high End CASE)
- @ChethanVishal . . . This answers the question that you asked. If you have another question then you should ask it as a new question. That said,
caseis rarely desirable in a
whereclause; the conditions can usually be expressed using