CODEWITHSUNDEEP
sqlsql-servert-sql
otesanek
otesanek

Reputation: 57

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

Upvotes: 1

Views: 112

Answers (4)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

The basically use of CASE EXPRESSION is - evaluate some expression, based on the result of expression return the Single Value

Now you almost did correct in WHERE clause. Case Expression returning some values but in WHERE clause that values are need to be compare with some other values and this is missing part in your query . You can try this way as well to get correct result.

...
where 
1 = case
      when R.subject2 is not null and R.remains_due > 0 
         then 1
      when R.subject2 is not null and R.remains_due = 0 
         then 1
      when R.subject2 is not null and R.due_date <= GETDATE() 
         then 1
      when R.subject2 is null and I.due_date <= GETDATE() 
        then 1
   end

Upvotes: 1

DRapp
DRapp

Reputation: 48139

I think you have a total misunderstanding of the WHERE clause. The where clause is a condition of WHAT RECORDS TO BE INCLUDED in the result set. Ex: Give me all records where an invoice is >= some date range, or an invoice where an amount = some value you are looking for. These need to return a logical TRUE or FALSE ONLY.

Using the case statement would be in your field list to identify which VALUE you want back based on the condition. In this case, under the specific conditions of the row you want something specific returned to show the user.

select 
      I.subject1, 
      R.subject2,
      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
           end  as AmountDue,
      case 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   DueDate
   from
      dbo.invoice I
         left join dbo.repayments R 
            on I.subject1 = R.subject2

Upvotes: 1

neer
neer

Reputation: 4082

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
      )

Upvotes: 1

outia
outia

Reputation: 11

try

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.subject2 is not null and R.remains_due = 0 
          AND
        R.subject2 is not null and R.due_date <= GETDATE() 
          AND
        R.subject2 is null and I.due_date <= GETDATE() ;

Upvotes: 1

Related Questions