CODEWITHSUNDEEP
sqlnullcasewhere-clause
Dagmar J.
Dagmar J.

Reputation: 13

SQL condition CASE in WHERE statement 'THEN NULL'

I tried to replace value from 'operation_num' if 'column_task' has the particular value. But I cannot find suitable statement. Could you help me?

FYI: column 'operation_num' does not include any NULL value.

WHERE
  CASE operation_num WHEN column_task = 'EXERCISE' THEN operation_num is null
                     ELSE operation_num -- operation_num = operation_num
  END

Thanks

Upvotes: 0

Views: 760

Answers (6)

naimisya mohanty
naimisya mohanty

Reputation: 1

SELECT distinct ID,
       CASE
          WHEN COLUMN2 is not NULL then COLUM1||'#'|| COLUMN2
           
          ELSE COLUMN1
       END
FROM   table ;

this query shows result as column1# which is wrong ,when it's null then it should show column value

Upvotes: 0

sagi
sagi

Reputation: 40491

Convert it to AND/OR (will also have better performance) :

WHERE (column_task = 'EXERCISE' and operation IS NULL)
    OR operation_num

Though I don't understand the last condition .

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271091

I think you intend:

WHERE column_task <> 'EXERCISE' OR operation_num IS NULL)

Upvotes: 0

Dagmar J.
Dagmar J.

Reputation: 13

I am sorry for my wrong question. I solved finally my issue in 'SELECT' part in query:

select 
  (case when (to_char(operation_num) = '10' and column_task = 'EXERCISE')
  then REPLACE(to_char(operation_num),'10',' ')
  else to_char(operation__num)
  end) as "column"
  ,column_task
from  my_table

But thank you all for your efforts.

Upvotes: 0

Fahmi
Fahmi

Reputation: 37493

Try this

WHERE
  CASE WHEN column_task = 'EXERCISE' THEN null
                     ELSE operation_num
  END

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

I think it would be like below

WHERE
  (CASE column_task WHEN 'EXERCISE' THEN  null
                     ELSE operation_num end)= value

Upvotes: 1

Related Questions