Create a free Team What is Teams? Learn more. Asked 9 years, 4 months ago. Active 5 years, 9 months ago. Viewed k times. Is there any better way to write the lines below in SQL Server ? Improve this question. Jon Seigel Tripz Tripz. Define "better". You should be able to accomplish this with the statement below. Bappy Why would it be better than Jay's answer? Email Address. All Sign in options. Enter a Email Address. Choose your interests Get the latest news, expert insights and market research, sent straight to your inbox.
Newsletter Topics Select minimum 1 topic. Dharmendra Mohapatra June 20, 0 Comments. Alun Davies Posted June 20, 0 Comments. Hi, You may need to re-write the sql as given below.
Please try the below one. Prachi Agarwal Posted June 20, 0 Comments. Dharmendra Mohapatra Posted June 20, 0 Comments. Why are you using the isnull function use is null. Also why do you have dates in nvarchar?
In your current test line 1 checks date 1 is greater than date 2 and date 3 Line 2 checks date 2 is greater than date 1 and date 3 Line 2 checks date 3 is greater than date 1 and date 2 Do you need to check date 1 in the second and third tests?
Charles Kuchlenz Posted June 20, 0 Comments. It is clear that one of your date columns is not a string. Being an expression—rather than a control structure—means that case varies the result of formulas expressions based on conditions. Its use is similar to the ternary operator?
The syntax of the case expression is very flexible and allows for some abbreviations. The following example shows the syntax in the most generic form—the so-called searched case. The other variants are abbreviations that could also be written as searched case. Furthermore searched case works in virtually all SQL databases.
From that point of view, one could say that searched case is the only case syntax you absolutely need to remember. Thus the name searched case. A case expression can contain several when - then pairs. This allows for multiple conditions even without nesting—unlike the? On the other hand, nesting provides an inherent precedence among the conditions.
A flat case expression does not have such an inherent precedence. To close that gap, SQL uses the order in which the conditions appear in the case expression to define their precedence. Before closing the case expression with end , the optional else clause can be used. The SQL standard does not specify how to process case expressions—it just defines the result.
Of course, these products eventually deliver the right result—they just discard the unnecessarily evaluated ones. Ultimately, this is a result of the declarative nature of SQL: the database—not the programmer—decides how to get the correct result.
Do not use non-deterministic functions or data-changing functions in case expressions. The undefined evaluation process can have unpredictable side effects.
To avoid repeating an operand that is used in all when clauses over and over again, the SQL standard offers the so-called simple case :. The other side of the comparison remains in the when clause. The standard defines the simple case as a transformation to a searched case —the rules described above remain valid. SQL offers two case abbreviations to cope with null : coalesce and nullif.
Both are used like functions and do not use the keywords case , when , then , else and end. Coalesce returns the first not- null parameter or null , if all parameters are null. The number of parameters is not limited. The standard defines coalesce as a transformation into a case expression.
0コメント