CODEWITHSUNDEEP
sqloracle-database
Chethan Vishal
Chethan Vishal

Reputation: 21

Using function inside CASE

I am trying to use the SUBSTR and 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 NVL function.

Upvotes: 0

Views: 848

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

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 TRUE or FALSE.

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'.

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65288

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' )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

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 
    )

If p_open_invoice can be NULL, you need to take that into account as well.

Upvotes: 2

Related Questions