Subquery can return 1 column only-CodePudding

The purpose of this query would be to do a calculation based on if today's date is a weekday or a weekend and use it after with Union All, however I am getting

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

It looks like that I can return only 1 column with this:

My query:

    SELECT (SELECT CASE WHEN DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday')
        THEN
            (SELECT --WeekEND
                'X' AS Table_name
                ,CAST(MAX(date_1) as date) AS max_date
                ,DATEDIFF(DD,(CAST(MAX(date_1)as date)),GETDATE()) as NrOfDays

                ,CASE
                    WHEN DATEDIFF(DD,(CAST(MAX(date_1)as date)),GETDATE()) <= 3 THEN 'good'
                        ELSE 'bad'
                            END AS Status
                                    FROM [Table_1])
        ELSE
            (SELECT --WeekDAY
                'X' AS Table_name
                ,CAST(MAX(date_1) as date) AS max_date
                ,DATEDIFF(DD,(CAST(MAX(date_1)as date)),GETDATE()) as NrOfDays

                ,CASE
                    WHEN DATEDIFF(DD,(CAST(MAX(date_1)as date)),GETDATE()) <= 1 THEN 'good'
                        ELSE 'bad'
                            END AS Status
                                    FROM [Table_1])
        END
)

Same issue with the following just a simplified version (if I delete the 'good', 'bad' part it works):

SELECT (SELECT CASE WHEN DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday') THEN 
(SELECT 'Weekend', 'good')
ELSE
(SELECT 'Weekday', 'bad')
END
)

CodePudding user response：

You are trying to return two columns from your subquery, into a single column of your main query.

Instead, you need to make two (very similar) subqueries. One for each column in your main query.

SELECT( 
    SELECT(
        CASE WHEN DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday') THEN 
            (SELECT 'Weekend')
        ELSE
            (SELECT 'Weekday')
        END AS Column1,
        CASE WHEN DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday') THEN 
            (SELECT 'good')
        ELSE
            (SELECT 'bad')
        END AS Column2
    )
)

There are other (better) ways to do this, depending upon your expected output. But this is probably the easiest/simplest to understand.

Also, not that it really matters but you have a lot of SELECTs in your query that you don't really need. The above can be simplified to:

SELECT
    CASE WHEN DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday') THEN 
        'Weekend'
    ELSE
        'Weekday'
    END AS Column1,
    CASE WHEN DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday') THEN 
        'good'
    ELSE
        'bad'
    END AS Column1

I don't know what your "Status" column is really doing, so I can't optimise it safely for you. But I think this should do what you want:

SELECT
    'X' AS Table_name,
    CAST(MAX(date_1) as date) AS max_date,
    DATEDIFF(DD,(CAST(MAX(date_1)as date)), GETDATE()) as NrOfDays,
    CASE WHEN DATENAME(weekday, GETDATE()) IN (N'Saturday', N'Sunday') THEN
        CASE WHEN DATEDIFF(DD,(CAST(MAX(date_1)as date)), GETDATE()) <= 3 THEN 
            'good'
        ELSE 
            'bad'
        END
    ELSE
        CASE WHEN DATEDIFF(DD,(CAST(MAX(date_1)as date)), GETDATE()) <= 1 THEN 
            'good'
        ELSE 
            'bad'
        END
    END AS Status
FROM [Table_1]