Error in syntax for nested Select Case
Can anyone please correct my syntax for nested Select Case When.
Update T set P= a.Quantity from ( select case when D.P> T.Open then D.P from DOP D inner join T on D.PON = T.PON )a
I am new to sql server, so not sure how to write proper select case.
Select Case without End Select, This error has the following cause and solution: If you have nested Select Case statements, each must have a matching End Select. If the When conditions are correct, then I want it to be labeled as 'Suspended for Audit.' if they are not correct, then have it be either blank or filled in by the t.fstrTaskSource + 'TYP' + t.fstrType statement (this part works already) SELECT t.flngKey AS flngTaskKey, t.fstrAccountType, t.fstrTaskSource, CASE t.fstrCategory WHEN '' THEN
CASE statement is like a
switch statement in languages like C#. Basically it's a compound structure for doing a bunch of
if/else stuff in line. As was mentioned in one of the comments, usually it takes the form of
case when <first condition> then <result on first condition> when <nth condition> then <result of nth condition> <optional else clause> end. Note the optional
else clause. If omitted, any values which didn't meet one of the criteria will be set null.
From your example, I'm not quite sure what you're trying to get at here; both paths you've laid out end up mapping to
D.POQty, and a case statement is really only useful if it maps to different values. If all you want is the null behavior, so be it. But you probably don't need a case statement, or at least not one of this complexity.
That said, in an effort to show how you'd refactor your code to make the statement run, here's what I came up with. I removed your subquery because you can update a column directly from a case statement. I also combined the compound case statement you had because I didn't see what it added that simply
ANDing the two conditions together wouldn't. If I didn't get at what you were asking, let me know.
update t2s -- References which aliased table you intend to update set POQty = case when D.POQty > [Tab-2 sourcing].OpenQuantity then D.POQty when D.POQty < [Tab-2 sourcing].OpenQuantity and D.POQty > [Tab-2 sourcing].AlreadyAlloted then D.POQty else null -- This is implied if you leave it off. Just adding it in so that its behavior is clear end from DemandPortal D inner join [Tab-2 sourcing] t2s on D.PONumber = t2s.PONumber
CASE statement documentation: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql
Select Case statement (VBA), Select Case statements can be nested. Each nested Select Case statement must have a matching End Select statement. Example. This example We can nest CASE statements similar to nested ifs that we find in most programming languages. Let us see an example. Limit of nesting a CASE function is up to 10 levels only. In the following example, the limit of 10 is completely utilized. Declare @x int set @x = 1 Select case when @x <= 100 then -- Level 1
I think that the case statement is not necessary. You can do all the checks within a where clause:
UPDATE T SET POQty = D.POQty FROM [Tab-2 sourcing] T INNER JOIN DemandPortal D ON T.PONumber = D.PONumber WHERE D.POQty > T.OpenQuantity OR (D.POQty < T.OpenQuantity AND D.POQty > T.AlreadyAlloted)
[Solved] CASE WHEN nesting syntax (View topic) • Apache , Your code produced syntax errors unfortunately, but I played with it in by trying to analyze the query with the nested case when operators:. Best way to do nested case statement logic in SQL Server. SELECT COALESCE( CASE WHEN condition1 THEN calculation1 ELSE NULL END, CASE WHEN condition2 THEN
Dirty Secrets of the CASE Expression, For example, the following query produces a divide by zero error in the subquery and not introduce it to the searched CASE expression, but Select Case constructions can be nested. Each nested Select Case construction must have a matching End Select statement and must be completely contained within a single Case or Case Else statement block of the outer Select Case construction within which it is nested. Example. The following example uses a Select Case construction to write a line corresponding to the value of the variable number.
Nested case statements vs multiple criteria case statements , As an example, say we had a table with 2 integer fields, column a and column b. Which of the two queries would be more processor friendly? CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING. Transact-SQL Syntax Conventions. Syntax
Again about SELECT CASE nested in a loop – Stack overflow, I came across a case of Segmentation fault occurring when running a SELECT clean the stack when exiting from the SELECT CASE construct in a “rude way”, For example, using your non-standard-compliant solution and I've made a quick example of what i'm trying to do but it's still throwing a syntax error: Select field3, field 4, Case When field in (1, 3, 7) then 1 When field in (2, 4, 6) then 2 When field in (5, 9) then 3 When field is null or ' ' then 4 Else Case When field2 = x then 1 When field2 = y then 2 Else End End as fieldname