CODEWITHSUNDEEP
oracle-databaseplsqlnestedconditional-statementscase
Uthpala Dl
Uthpala Dl

Reputation: 45

Usage of CASE Statements in WHERE Clause - PL/SQL

Can someone tell me what is the use of last 'Y' in the " end = 'Y') "

( case
 when a = 'STAGE PAYMENT' then
 'Y'
 when b not IN ('To be Received', 'Received') then
 'N'
 when c != (d - NVL(e, 0) - NVL(f, 0) - NVL(g, 0)) then
 'Y'
 when NVL(h, 0) + NVL(i, 0) + NVL(j, 0) <> 0 then
    case 
    when c != k then
    'Y'
    when (-l != NVL(e, 0) + NVL(f, 0) + NVL(g, 0) + NVL(m, 0)) then
    'Y' 
    else 'N'
    end 
 else 'N'
 end = 'Y')

Also, is there any way of optimizing this?

Thanks!

Upvotes: 0

Views: 205

Answers (2)

Boneist
Boneist

Reputation: 23588

As your case statement is part of the where clause, that = 'Y' is there to form the predicate.

Predicates most often have the form of <some value> <comparison operator> <other value> (there are exceptions, most notably regexp_like), where the <some value> and <other value> can be columns, functions, literal values, expressions, variables, etc.

Your case expression is simply taking up the place of the <some value>, i.e.:

select ...
from   ...
where  <some_col> = 1
and    case .... end = 'Y'

Upvotes: 0

Popeye
Popeye

Reputation: 35910

Last end = 'Y') is nothing but the comparision.

Your case statement is generating one value based on condition and if it is Y then that condition will be statisfied and row will be considered in the result.

Lets say, If a = 'STAGE PAYMENT' is true for some record then your case statement will generate Y as output, which will be again compared with last end = 'Y') and returns true.

Cheers!!

Upvotes: 1

Related Questions