Case statement in SQL Server causing error

Question

I am trying to select some values from a database table and then convert them to human readable words. I am getting many errors in the third case statement. Can somebody tell me were am going wrong?

Errors

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'or'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'AS'.

Code:

SELECT 
   P.id AS Ref, 
   CASE 
      WHEN P.listed = 1 THEN 'Yes' 
      ELSE 'No' 
   END AS Listed, 
   CONVERT(DATE, CONVERT(VARCHAR, P.listeddate, 12)) AS DateListed, 
   CASE 
      WHEN P.premium = 1 THEN 'Premium' 
      ELSE 'Free' 
   END AS Type, 
   P.postcode AS London, 
   CONVERT(DATE, CONVERT(VARCHAR, (SELECT TOP 1 moveindate 
                                   FROM propertytenant 
                                   WHERE P.id = propertyid 
                                   ORDER BY id), 12)) AS MoveInDate, 
   (SELECT FirstName 
    FROM UserDetails 
    WHERE UserId = (SELECT UserId 
                FROM aspnet_Membership 
            WHERE LoweredEmail = P.CreatedBy)) AS FirstName,
   (SELECT LastName 
    FROM UserDetails 
    WHERE UserId = (SELECT UserId 
                    FROM aspnet_Membership 
                    WHERE LoweredEmail = P.CreatedBy)) AS LastName,
   (SELECT Mobile 
    FROM UserDetails 
    WHERE UserId = (SELECT UserId 
                    FROM aspnet_Membership 
                    WHERE LoweredEmail = P.CreatedBy)) AS Mobile,
   CASE 
       WHEN (SELECT TOP 1 DepositScheme 
             FROM [Rent].[dbo].[Contract] 
             WHERE PropertyID = P.Id) = 1 
          THEN 'DPS' or = 2 
          THEN 'DPS2' 
       ELSE 'Other' 
   AS DPSType,
   (SELECT DPSNumber 
    FROM UserDetails 
    WHERE UserId = (SELECT UserId 
                    FROM aspnet_Membership 
                    WHERE LoweredEmail = P.CreatedBy)) AS DPSNumber
   P.createdby AS Email 
FROM   
   [Rent].[dbo].[property] AS P; 

Answer 1

The query can be made easier to read and manage using some JOIN, and the last CASE can benefit the form CASE x WHEN Value ... END over the form CASE WHEN x = 1 ... END, probably even the two lasting subquery can be changed in JOIN with the knowledge of the DB schema.

SELECT P.id AS Ref
     , CASE WHEN P.listed = 1 THEN 'Yes' 
            ELSE 'No' 
       END AS Listed
     , CONVERT(DATE, CONVERT(VARCHAR, P.listeddate, 12)) AS DateListed
     , CASE WHEN P.premium = 1 THEN 'Premium' 
            ELSE 'Free' 
       END AS Type
     , P.postcode AS London
     , CONVERT(DATE, CONVERT(VARCHAR, (SELECT TOP 1 moveindate 
                                       FROM propertytenant 
                                       WHERE P.id = propertyid 
                                       ORDER BY id), 12)) AS MoveInDate
     , ud.FirstName 
     , ud.LastName 
     , ud.Mobile
     , CASE (SELECT TOP 1 DepositScheme 
             FROM   [Rent].[dbo].[Contract] 
             WHERE  PropertyID = P.Id) 
            WHEN = 1 THEN 'DPS' 
            WHEN = 2 THEN 'DPS2' 
            ELSE 'Other' 
       AS DPSType
     , ud.DPSNumber 
     , P.createdby AS Email 
FROM   Rent.property P
       INNER JOIN aspnet_Membership am ON P.CreatedBy = am.LoweredEmail
       INNER JOIN UserDetails ud ON am.UserId = ud.UserId

Answer 2

Try this if it works

SELECT P.id                                                  AS Ref, 
       CASE 
         WHEN P.listed = 1 THEN 'Yes' 
         ELSE 'No' 
       END                                                   AS Listed, 
       CONVERT(DATE, CONVERT(VARCHAR, P.listeddate, 12))     AS DateListed, 
       CASE 
         WHEN P.premium = 1 THEN 'Premium' 
         ELSE 'Free' 
       END                                                   AS Type, 
       P.postcode                                            AS London, 
       CONVERT(DATE, CONVERT(VARCHAR, (SELECT TOP 1 moveindate 
                                       FROM   propertytenant 
                                       WHERE  P.id = propertyid 
                                       ORDER  BY id), 12))   AS MoveInDate, 
       (SELECT firstname 
        FROM   userdetails 
        WHERE  userid = (SELECT userid 
                         FROM   aspnet_membership 
                         WHERE  loweredemail = P.createdby)) AS FirstName, 
       (SELECT lastname 
        FROM   userdetails 
        WHERE  userid = (SELECT userid 
                         FROM   aspnet_membership 
                         WHERE  loweredemail = P.createdby)) AS LastName, 
       (SELECT mobile 
        FROM   userdetails 
        WHERE  userid = (SELECT userid 
                         FROM   aspnet_membership 
                         WHERE  loweredemail = P.createdby)) AS Mobile, 
       CASE (SELECT TOP 1 depositscheme 
             FROM   [Rent].[dbo].[contract] 
             WHERE  propertyid = P.id) 
         WHEN 1 THEN 'DPS' 
         WHEN 2 THEN 'DPS2' 
         ELSE 'Other' 
       END                                                   AS DPSType, 
       (SELECT dpsnumber 
        FROM   userdetails 
        WHERE  userid = (SELECT userid 
                         FROM   aspnet_membership 
                         WHERE  loweredemail = P.createdby)) AS DPSNumber, 
       P.createdby                                           AS Email 
FROM   [Rent].[dbo].[property] AS P;

Answer 3

For Multiple options in one CASE statement use more WHEN conditions

So Try

SELECT P.id                                                  AS Ref, 
       CASE 
         WHEN P.listed = 1 THEN 'Yes' 
         ELSE 'No' 
       END                                                   AS Listed, 
       CONVERT(DATE, CONVERT(VARCHAR, P.listeddate, 12))     AS DateListed, 
       CASE 
         WHEN P.premium = 1 THEN 'Premium' 
         ELSE 'Free' 
       END                                                   AS Type, 
       P.postcode                                            AS London, 
       CONVERT(DATE, CONVERT(VARCHAR, (SELECT TOP 1 moveindate 
                                       FROM   propertytenant 
                                       WHERE  P.id = propertyid 
                                       ORDER  BY id), 12))   AS MoveInDate, 
       (SELECT firstname 
        FROM   userdetails 
        WHERE  userid = (SELECT userid 
                         FROM   aspnet_membership 
                         WHERE  loweredemail = P.createdby)) AS FirstName, 
       (SELECT lastname 
        FROM   userdetails 
        WHERE  userid = (SELECT userid 
                         FROM   aspnet_membership 
                         WHERE  loweredemail = P.createdby)) AS LastName, 
       (SELECT mobile 
        FROM   userdetails 
        WHERE  userid = (SELECT userid 
                         FROM   aspnet_membership 
                         WHERE  loweredemail = P.createdby)) AS Mobile, 
       CASE 
         WHEN (SELECT TOP 1 depositscheme 
               FROM   [Rent].[dbo].[contract] 
               WHERE  propertyid = P.id) = 1 THEN 'DPS' 
         WHEN (SELECT TOP 1 depositscheme 
               FROM   [Rent].[dbo].[contract] 
               WHERE  propertyid = P.id) = 2 THEN 'DPS2' 
         ELSE 'Other' 
       END                                                   AS DPSType, 
       (SELECT dpsnumber 
        FROM   userdetails 
        WHERE  userid = (SELECT userid 
                         FROM   aspnet_membership 
                         WHERE  loweredemail = P.createdby)) AS DPSNumber, 
       P.createdby                                           AS Email 
FROM   [Rent].[dbo].[property] AS P;

Answer 4

CASE WHEN (SELECT TOP 1 DepositScheme 
           FROM [Rent].[dbo].[Contract] 
           WHERE PropertyID = P.Id) = 1 
     THEN 'DPS' 
     WHEN (SELECT TOP 1 DepositScheme 
           FROM [Rent].[dbo].[Contract] 
           WHERE PropertyID = P.Id) = 2 
     THEN 'DPS2' 
     ELSE 'Other' END AS DPSType

Try this.