CASE WHEN Error - An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'

Question

I am trying to create an "Error column" and putting conditions in 'Case when'. I am getting the error below:

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

My query is:

SELECT  ROWID, [Alternate_number], DELIVERY_DATE, DP1, [DP1__],[DP2],[DP2__],[Deliver_to_EDI_warehouse_number] as EDI_warehouse,[Cube], Null as [Quantity], Null as [Balance_DP],

CASE WHEN isnull([DP1__],0) THEN 'MISSING DP1' WHEN  [DP1__]='' THEN 'MISSING DP1' 
WHEN isnull([Alternate_number],0) then 'MISSING WIN' when [Alternate_number]= '' then 'MISSING WIN'  
WHEN LEN(Delivery_date) = 10 THEN '' WHEN left(right((VIEW_MRP_Splitting_Cube_checker.Delivery_date),11),1) <> '%-%' THEN "DATE 2 TOO LONG" 
WHEN right(Left((VIEW_MRP_Splitting_Cube_checker.Delivery_date},11),1) <> '%-%' THEN "DATE 1 TOO LONG" 
else 'No Error' end as [Error Type]

from VIEW_MRP_Splitting_Cube_checker

Answer 1

You have misused the apostrophes and parentheses

SELECT  ROWID, [Alternate_number], DELIVERY_DATE, DP1, [DP1__],[DP2],[DP2__],[Deliver_to_EDI_warehouse_number] as EDI_warehouse,[Cube], Null as [Quantity], 
    NULL as [Balance_DP],
    CASE 
        WHEN isnull([DP1__],0) THEN 'MISSING DP1' 
        WHEN  [DP1__]='' THEN 'MISSING DP1' 
        WHEN isnull([Alternate_number],0) then 'MISSING WIN' 
        WHEN [Alternate_number]= '' then 'MISSING WIN'  
        WHEN LEN(Delivery_date) = 10 THEN '' 
        WHEN left(right((VIEW_MRP_Splitting_Cube_checker.Delivery_date),11),1) <> '%-%' THEN 'DATE 2 TOO LONG'
        WHEN right(Left((VIEW_MRP_Splitting_Cube_checker.Delivery_date),11),1) <> '%-%' THEN 'DATE 1 TOO LONG'
        else 'No Error' 
    END as [Error Type]
from VIEW_MRP_Splitting_Cube_checker

Answer 2

Replace

WHEN right(Left((VIEW_MRP_Splitting_Cube_checker.Delivery_date},11),1) <> '%-%'
                                                              ^----here is the problem

with

WHEN right(Left((VIEW_MRP_Splitting_Cube_checker.Delivery_date),11),1) <> '%-%'

Answer 3

instead of misusing isnull() instead of using [col] is null you can combine both of your null or empty checks using nullif() like so:

select  
    rowid
  , [Alternate_number]
  , delivery_date
  , dp1
  , [dp1__]
  , [dp2]
  , [dp2__]
  , [Deliver_to_edi_warehouse_number] as edi_warehouse
  , [Cube]
  , null as [Quantity]
  , null as [Balance_dp]
  , case 
        when nullif([dp1__],'') is null then 'missing dp1' 
        when nullif([Alternate_number],'') is null then 'missing win'
        when len(Delivery_date) = 10 then '' 
        when left(right((view_mrp_Splitting_Cube_checker.Delivery_date),11),1) <> '%-%' 
          then 'date 2 too long'
        when right(Left((view_mrp_Splitting_Cube_checker.Delivery_date),11),1) <> '%-%' 
          then 'date 1 too long'
        else 'No Error' 
    end as [Error Type]
from view_mrp_Splitting_Cube_checker

Answer 4

I tried to tidy up case part, I hope it works.

CASE 
    WHEN [DP1__] IS NULL OR [DP1__]='' THEN 'MISSING DP1' 
    WHEN [Alternate_number] IS NULL  OR [Alternate_number] ='' then 'MISSING WIN' 
    WHEN LEN(Delivery_date) = 10 THEN '' 
    WHEN left(right((VIEW_MRP_Splitting_Cube_checker.Delivery_date),11),1) <> '%-%' THEN 'DATE 2 TOO LONG'
    WHEN right(Left((VIEW_MRP_Splitting_Cube_checker.Delivery_date),11),1) <> '%-%' THEN 'DATE 1 TOO LONG' 
    ELSE 'No Error' 
END AS [Error Type]