Problem in WHERE clause (don't know how I can address results from DECODE() function in WHERE clause).

Question

I have a rather simple PL/SQL query

(...)
DECODE(upper(Table.Column), 'SOMETHING', '---', TABLE2.COLUMN2) as VALUE
(...)

How can I apply the WHERE clause using above statement, because when I use something like:

WHERE upper(VALUE) like 'SOMETHING'

it gives me an empty recordset (and I am absolutely positive that there are records which fulfill those requirements).

I don't know if that matters, but I am calling this query from within Excel workbook, using VBA and CreateObject("OracleInProcServer.XOraSession").

Answer 1

The easiest solution is to copy and paste.

WHERE upper(DECODE(upper(Table.Column), 'SOMETHING', '---', TABLE2.COLUMN2)) like 'SOMETHING'

SOMETHING is not a good search pattern for LIKE though.

Answer 2

The output of the DECODE function would be either '---' or table2.column2.

This decode is equivalent to:

CASE upper(Table.Column) = 'SOMETHING' THEN '---' ELSE TABLE2.COLUMN2 END

I'm guessing you want to filter the rows that have upper(TABLE.Column) = 'SOMETHING', in that case you would use :

WHERE upper(VALUE) = '---'

Answer 3

The problem with the accepted solution is that you have to write out the DECODE expression twice. This can be a maintenance issue, because it opens up the possibility that the two instances of the expression will diverge at some point.

Another way to do it is to nest queries so you can use the result of the DECODE directly in your predicate.

SELECT * FROM (
  SELECT DECODE(upper(Table.Column), 'SOMETHING', '---', TABLE2.COLUMN2) as VALUE
    FROM ...
  )
  WHERE UPPER(value) LIKE 'X%'