The DECODE has converted sysdate into a VARCHAR2. Once again, using DUMP on this query the CASE statements return data type 12, a DATE.
, decode(sysdate, trunc(sysdate), null, sysdate) as decode , case when sysdate = trunc(sysdate) then null If we take the previous query and convert it into one that uses a date instead: select case sysdate when trunc(sysdate) then null That's not a pretty list but the penultimate point brings me nicely on to dates. Behavior of explicit conversions is more predictable.
#Sql decode software
For example, implicit conversion from a datetime value to a VARCHAR2 value may return an unexpected year depending on the value of the NLS_DATE_FORMATĪlgorithms for implicit conversion are subject to change across software releases and among Oracle products. Implicit conversion depends on the context in which it occurs and may not work the same way in every case. Implicit data type conversion can have a negative impact on performance, especially if the data type of a column value is converted to that of a constant rather than the other way around. SQL statements are easier to understand when you use explicit data type conversion functions. Oracle recommends that you specify explicit conversions, rather than rely on implicit or automatic conversions, for these reasons: Secondly, here's what Oracle says about implicit conversion:
If you need it to be a number Oracle will implicitly convert the character to a number under the implicit conversion rules, right? This isn't true either, it won't work in a UNION as the data types have to be identical Oracle won't do any implicit conversion to make things easy for you. You might think that this doesn't really affect anything. I assume this occurs because, as the names suggest, DECODE is a function and CASE isn't, which implies they have been implemented differently internally. Using Oracle's Data Type Summary, DECODE is returning a VARCHAR2 (data type 1) whereas the CASE statements are "returning" numbers (data type 2). You can see that the data type of the DECODE is 1, whereas the two CASE statements "return" a data type of 2. If we use the DUMP function to evaluate the data types returned you'll see what I mean: SQL> select dump(case 1 when 2 then null else 0 end) as simple_caseĢ, dump(case when 1 = 2 then null else 0 end) as searched_caseģ, dump(decode(1, 2, null, 0)) as decode It only appears that the result obtained from each statement is identical. , case when 1 = 2 then null else 0 end as searched_caseĭo the simple CASE expression and the DECODE function (and in specific circumstances the searched CASE expression) always return the same result? select case 1 when 2 then null else 0 end as simple_case These three statements all seem to return the same result, 0. If default is omitted,Īs the searched CASE expression can be equivalent to the simple, this could be construed to be the same as well. No match is found, then Oracle returns default. To a search, then Oracle Database returns the corresponding result. Returns else_result if it exists and NULL otherwise.Ĭomparing this to the DECODE function, the descriptions seem to be identical.ĭECODE compares expr to each search value one by one. If no selector_value matches selector, the CASE expression The simple CASE expression returns the first result for which The documentation has the following to say about the simple CASE expression: It seems like the simple CASE expression and the DECODE function are equivalent and that the results returned by them should be identical.
0 kommentar(er)
