CODEWITHSUNDEEP
sqlsql-servert-sql
JKMK
JKMK

Reputation: 1

SQL error Subquery returned more than 1 value

I got the following error when I change the PlanningDate period. Because of one PlanningTime has more than one "ProgrammeTitle, Title". I tried to change it as join, but can't get my expect result. Please help

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

select pt.PlanningTime,
       (SELECT (LTRIM(RTRIM(ProgrammeTitle+':'+Title))) 
        FROM Planning 
        where ChannelID = '34'
         and CONVERT(char(8),PlanningDate,112) between '20130101' and '20130107'
         and pt.PlanningTime = PlanningTime
         and DATEPART(dw,PlanningDate)=1) AS Title1, 
       (SELECT (LTRIM(RTRIM(ProgrammeTitle+':'+Title))) 
        FROM Planning 
        where ChannelID = '34'
          and CONVERT(char(8),PlanningDate,112) between '20130101' and '20130107'
          and pt.PlanningTime = PlanningTime
          and DATEPART(dw,PlanningDate)=2) AS Title2 
FROM PlanningTime pt 
where pt.ChannelID = '34'
  and CONVERT(char(8),pt.PlanningDate,112) between '20130101' and '20130107'

Upvotes: 0

Views: 144

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

In SQL Server, you can get all the rows using outer apply:

SELECT pt.PlanningTime, p1.Title1, p2.Title2
FROM PlanningTime pt OUTER APPLY
     (SELECT (LTRIM(RTRIM(ProgrammeTitle+':'+Title))) 
      FROM Planning 
      WHERE ChannelID = '34' and
            CONVERT(char(8),PlanningDate,112) between '20130101' and '20130107' and
            pt.PlanningTime = PlanningTime and
           DATEPART(dw, PlanningDate) = 1
     ) p1 OUTER APPLY
     (SELECT (LTRIM(RTRIM(ProgrammeTitle+':'+Title))) 
      FROM Planning 
      WHERE ChannelID = '34' AND
            CONVERT(char(8),PlanningDate,112) between '20130101' and '20130107'
            pt.PlanningTime = PlanningTime and
           DATEPART(dw,PlanningDate)=2
     ) p2
where pt.ChannelID = '34' and
      CONVERT(char(8),pt.PlanningDate,112) between '20130101' and '20130107';

This will get rid of your error. However, I'm guessing that the results are not exactly what you want. Your question doesn't specify what you want, so this should set you on a better path.

Note: You should use built-in date/time functions when working with date/times. You are converting them to strings, which is simply a bad idea. You don't need to do that and it affects performance.

Upvotes: 0

rinesh
rinesh

Reputation: 545

It is clear that your sub query returns more than one value and so you cant query the result this way. You must find some other way to query your results as per your table structure. Just think that any one of the sub-query in your query returns more than one result, how your select would work? That's it.

Upvotes: 0

Related Questions