j. doe j. doe - 1 month ago 6
SQL Question

SQL Server : case when in where clause

Can you teach me what am I doing wrong in my case? :)

I am selecting invoices and repayments.

Error:


Msg 4145, Level 15, State 1, Line 10

An expression of non-boolean type specified in a context where a condition is expected, near 'end'.


Code:

select
I.subject1, R.subject2
from
dbo.invoice I
left join
dbo.repayments R on I.subject1 = R.subject2
where
case
when R.subject2 is not null and R.remains_due > 0
then R.remains_due
when R.subject2 is not null and R.remains_due = 0
then I.remains_due
when R.subject2 is not null and R.due_date <= GETDATE()
then R.due_date
when R.subject2 is null and I.due_date <= GETDATE()
then I.due_date
end

Answer

The following show how to use search conditions in the WHERE clause.

SELECT
  ColumnB,
  ColumnA
FROM
  TableA
WHERE
  ColumnA =  CASE WHEN ColumnB IS NULL THEN 'Test AA'
                  WHEN ColumnB IS NOT NULL THEN 'Test BB'
                  ELSE 'Test CC' END

Your query is not correct. Query looks like:

SELECT
  ColumnB,
  ColumnA
FROM
  TableA
WHERE
  -- Where is ColumnA???
  -- Return types must be same type!
  CASE WHEN ColumnB IS NULL THEN 'Test AA' -- VARCHAR?
       WHEN ColumnB IS NOT NULL THEN 1 -- INT ?
       ELSE '2016.10.20' END -- DATETIME?

Updated

select 
    I.subject1, 
    R.subject2
from
    dbo.invoice I LEFT join 
    dbo.repayments R on I.subject1 = R.subject2
WHERE
     (
        R.subject2 is not NULL AND
        R.remains_due >= 0  AND
        R.due_date <= GETDATE() 
     ) OR
     (
        R.subject2 is NULL AND
        I.due_date <= GETDATE() AND
        I.remains_due >= 0
      )
Comments