Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2011
    Posts
    5

    Unanswered: Conditional Where clause

    Here is my query

    Code:
    SELECT ys2.[CLIENTENROLL].EnrollID AS ID
       FROM ys2.[CLIENTENROLL]
     WHERE @EnrollSearchTerminated IS NULL OR ys2.[CLIENTENROLL].EnrollID IN
        (CASE WHEN @EnrollSearchTerminated = 'True' 
              THEN (SELECT ys2.CLIENTENROLL.EnrollID 
                      FROM ys2.[CLIENTENROLL] 
                     WHERE ys2.[CLIENTENROLL].EnrollTermDate IS NULL)
              ELSE (SELECT ys2.CLIENTENROLL.EnrollID 
                      FROM ys2.[CLIENTENROLL] 
                     WHERE ys2.[CLIENTENROLL].EnrollTermDate IS NOT NULL)
         END)
    ORDER BY EnrollID DESC
    Can anyone tell me why I am getting the error:

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

    If this is not a valid query, does anyone have an alternate way to accomplish this less using procedures?

    Thanks in advance for your help.
    Last edited by sykiemikey; 07-14-11 at 15:46.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Case expects a single value to be returned as an output. Not a full recordset. Does this come close to what you need?
    Code:
    SELECT ys2.[CLIENTENROLL].EnrollID AS ID
    FROM ys2.[CLIENTENROLL]
    WHERE @EnrollSearchTerminated IS NULL 
       OR (@EnrollSearchTerminated = 'True' and ys2.[CLIENTENROLL].EnrollTermDate IS NULL)
       OR (@EnrollSearchTerminated != 'True' AND ys2.[CLIENTENROLL].EnrollTermDate IS NOT NULL)
    ORDER BY EnrollID DESC

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try:
    Code:
    SELECT C.EnrollID AS ID
    FROM ys2.[CLIENTENROLL] as C
    WHERE @EnrollSearchTerminated IS NULL
    	UNION ALL
    SELECT C.EnrollID AS ID
    FROM ys2.[CLIENTENROLL] as C
    WHERE @EnrollSearchTerminated = 'True' AND
    	EXISTS (SELECT 1 
    		FROM ys2.[CLIENTENROLL] as T
    		WHERE C.EnrollID = T.EnrollID AND
    			T.EnrollTermDate IS NULL
    		)
    	UNION ALL
    SELECT C.EnrollID AS ID
    FROM ys2.[CLIENTENROLL] as C
    WHERE @EnrollSearchTerminated = 'False' AND
    	EXISTS (SELECT 1
    		FROM ys2.[CLIENTENROLL] as T
    		WHERE C.EnrollID = T.EnrollID AND
    			T.EnrollTermDate IS NOT NULL
    		)
    ORDER BY EnrollID DESC
    Last edited by Wim; 07-14-11 at 15:01. Reason: Sorry MCrowley, just saw your reply. No-one had answered the moment I opened this post.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jul 2011
    Posts
    5
    Thanks SOOO MUCH to both of you. Both solutions give the the exact counts I'm looking for, but the first seems to be the much simpler solution.

    I think my brain has already checked out for the weekend. Appreciate it!

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    No apologies necessary, Wim. I had to think about the query for a few minutes, myself.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Thumbs up

    Not only is MCrowley's solution a little bit simpler, it will also run a tad faster than mine. Like a hundredfold faster or so. Or a thousandfold. Or ...

    But both solutions are not equal. They will behave different in case EnrollID is not the PK of ys2.CLIENTENROLL.

    MCrowley's solution only looks at the columns within one record, making it perform very fast.
    - it will return all EnrollID's from ys2.CLIENTENROLL in case @EnrollSearchTerminated IS NULL
    - it will return all EnrollID's with an empty EnrollTermDate in case ys2.CLIENTENROLL = True
    - it will return all EnrollID's with a filled in EnrollTermDate in case ys2.CLIENTENROLL != True

    My solution will check all records in the table with the same EnrollID over and over again, for each record.
    - it will return all EnrollID's from ys2.CLIENTENROLL in case @EnrollSearchTerminated IS NULL
    - it will return all EnrollID's where there exists at least one ys2.CLIENTENROLL record with the same value of EnrollID with an empty EnrollTermDate, in case ys2.CLIENTENROLL = True
    - it will return all EnrollID's where there exists at least one ys2.CLIENTENROLL record with the same value of EnrollID with a filled in EnrollTermDate, in case ys2.CLIENTENROLL = False

    I don't know what semantics you want.
    As the table is called CLIENTENROLL, I thought it would have a composite PK of CliendId and EnrollID. Your code seemed to point in the direction of "where there is at least..".

    The two solutions will now give the same result, particularly when adding a DISTINCT to both SELECT's. They will start do differ when you retrieve other columns besides EnrollID.

    Also, watch out for the NULL beast.

    WHERE @EnrollSearchTerminated IS NULL
    ...
    WHEN @EnrollSearchTerminated = 'True'
    THEN ...
    ELSE ...


    The ELSE part will also be executed when @EnrollSearchTerminated IS NULL. It won't make any difference in this case, but it is a common source of bugs. That's why I explicitly wrote WHERE @EnrollSearchTerminated = 'False'
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Jul 2011
    Posts
    5
    EnrollID is the single PK of ys2.CLIENTENROLL so they came up with the same counts.

    I'm creating the query for my datasource to fill an asp grid based on search form parameters. I prefer the null "beast" because it pulls all results making the variables optional. With the @EnrollSearchTerminated I want to pull all if null, pull only terminated if true and pull only active if false.

    Here's the completed query which might help you get an idea of where I was going with it...

    Code:
    SELECT ys2.[CLIENTENROLL].EnrollID AS ID,
    	   ISNULL(ys2.[CLIENT].ClientLastName,'') + ', ' +
             ISNULL(ys2.[CLIENT].ClientFirstName,'') AS Client,
           ys2.[PROGRAM].ProgramDesc AS Program,
           Convert(varchar,ys2.[CLIENTENROLL].EnrollDate,101) AS Enrolled,
           CASE WHEN ys2.[CLIENTENROLL].EnrollCompleted = 'True' THEN 'Y'
                ELSE 'N' END AS Completed,
           Convert(varchar,ys2.[CLIENTENROLL].EnrollTermDate,101) AS Terminated,
           ys2.[CLIENTENROLL].EnrollDate,
           ys2.[CLIENTENROLL].EnrollTermDate
      FROM ys2.[CLIENTENROLL] JOIN ys2.[CLIENT]
        ON ys2.[CLIENT].ClientID = ys2.[CLIENTENROLL].ClientID
      JOIN ys2.[PROGRAM]
        ON ys2.[PROGRAM].ProgramID = ys2.[CLIENTENROLL].ProgramID
     WHERE (ys2.[CLIENTENROLL].ProgramID = @ProgramID OR @ProgramID = -1)
       AND (ys2.[CLIENTENROLL].ClientID = @ClientID OR @ClientID = -1)
       AND (ys2.[CLIENT].ClientFirstName LIKE '%' + @ClientFirst + '%' OR @ClientFirst IS NULL)
       AND (ys2.[CLIENT].ClientLastName LIKE '%' + @ClientLast + '%' OR @ClientLast IS NULL)
       AND (@EnrollSearchTerminated IS NULL 
            OR (@EnrollSearchTerminated = 'True' AND ys2.[CLIENTENROLL].EnrollTermDate IS NOT NULL)
            OR (@EnrollSearchTerminated = 'False' AND ys2.[CLIENTENROLL].EnrollTermDate IS NULL))
       AND (ys2.[CLIENTENROLL].EnrollCompleted = @EnrollCompleted OR @EnrollCompleted IS NULL)
       AND (@IncludeEnrollDates = 'False' 
           OR ((ys2.[CLIENTENROLL].EnrollDate >= @EnrollDate1 
                AND ys2.[CLIENTENROLL].EnrollDate <= @EnrollDate2) 
                    OR (@EnrollDate1 IS NULL OR @EnrollDate2 IS NULL)))
       AND (@IncludeTermDates = 'False' 
           OR ((ys2.[CLIENTENROLL].EnrollTermDate >= @EnrollDate3 
                AND ys2.[CLIENTENROLL].EnrollTermDate <= @EnrollDate4) 
                    OR (@EnrollDate3 IS NULL OR @EnrollDate4 IS NULL)))          
    ORDER BY EnrollID DESC
    Thanks again for your help. I was making it a lot harder than it needed to be.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Actually, I should caution you on relying on the variable to switch behaviors of the search. If this is part of a stored procedure, the plan will be cached. The plan that is cached will be determined by the inputs of whoever runs this first. That plan that is generated for the first person's parameters may be a horrible plan for the next person who runs the procedure. I have not come up with a good solution to a general search procedure, as yet. My advice to you would be to watch this procedure, and be ready to run sp_recompile on the CLIENTENROLL table, if the cached plan is for an edge case that is very much on the edge.

  9. #9
    Join Date
    Jul 2011
    Posts
    5
    Quote Originally Posted by MCrowley View Post
    Actually, I should caution you on relying on the variable to switch behaviors of the search. If this is part of a stored procedure, the plan will be cached. The plan that is cached will be determined by the inputs of whoever runs this first. That plan that is generated for the first person's parameters may be a horrible plan for the next person who runs the procedure. I have not come up with a good solution to a general search procedure, as yet. My advice to you would be to watch this procedure, and be ready to run sp_recompile on the CLIENTENROLL table, if the cached plan is for an edge case that is very much on the edge.
    Very good advice indeed. I don't use stored procedures for my searches for that very reason. I have the advantage (or disadvantage) of being the DBA AND the programmer so I use ado.net for all the search forms and DML in my web applications.

    This is my first time on this forum and it definitely won't be my last. Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •