Alter View gives error 'Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.'

Question

I need to alter a view where I need to run certain 'WHERE' clause conditions based on a column value in a different table.

I want to return record (SourceStayID,PropertyCode) based on InsertDate when SettingValue is 'SubscriptionBased' else return record (SourceStayID,PropertyCode) on the basis of DepartureDate. I need to create a view for this.

I get an error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I see that I can have only one column after select, but the requirement is to have more than one column, Is there any other way to do this or overcome this?

Alter View v_CheckView

SELECT 
  CASE 
    WHEN ((select SettingValue from PropertySetting where SettingKey='AutoEnrollmentBasis' )='SubscriptionBased'  )
    THEN (select SourceStayID,PropertyCode from d_customer_stay where SourceStayID=151905 and InsertDate=GETDATE() 
) 
    ELSE (select SourceStayID,PropertyCode from d_customer_stay where SourceStayID=151906 and DepartureDate > dateadd(day,datediff(day,1,GETDATE()),0) )

  END

Answer 1

CASE is an expression, not a statement so you cannot use it as you are attempting to do.

However you do not need a CASE expression, you just need simple AND/OR logic in your WHERE clause to return the rows that match your logic.

The following is a direct translation of your logic, however it still won't work because InsertDate = GETDATE() will never be true.

ALTER VIEW v_CheckView
AS
SELECT
    SourceStayID
    , PropertyCode
FROM d_customer_stay
WHERE ((SELECT SettingValue FROM PropertySetting WHERE SettingKey = 'AutoEnrollmentBasis') = 'SubscriptionBased' AND SourceStayID = 151905 AND InsertDate = GETDATE())
OR ((SELECT SettingValue FROM PropertySetting WHERE SettingKey = 'AutoEnrollmentBasis') <> 'SubscriptionBased' AND SourceStayID = 151906 AND DepartureDate > dateadd(day,datediff(day,1,GETDATE()),0));