All my blogs with #SQLSERVERLearning is intended to cover all stuff identified as difference between Oracle and SQL Server.
Hopefully it might be helpful for Oracle guy also working in SQL Server world.
All finding or Observations with SQL Server for any Oracle guy can be access here.
#TGIF, had decided to publish at least one blog weekly and preferably on Friday!
Today will post about CASE statement in SQL server and how it differ with comparison in Oracle.
CASE is widely used for expression comparison with varying WHEN conditions and desired THEN for respective conditions.
IF nothing match we can use a default ELSE expression.
CASE help to compare different type of condition other then equality as compared to DECODE and get us desire output.
Whenever we compare any expression, it’s key factor to manage data type of operator for comparision.
Today will explore CASE expression with different data type and understand how it’s behave in Oracle and SQL server.
Lets start with below CASE statement,
SELECT CASE WHEN 1=1 THEN 1
WHEN '2' = 2 THEN '2'
WHEN GETDATE() = GETDATE() THEN GETDATE()
ELSE NULL
END
In above case statement, you will observe below thing
1. Data type of each expression in WHEN is different. (NUMBER – VARCHAR – DATETIME)
2. Return type for each expression is different.
3. It will match only for first WHEN expression, remaining WHEN expression and not reachable.
Now let see what the output for same.
Output :: 1900-01-02 00:00:00.000
Ideally at first thought, we would think output would be 1, as it match for first WHEN expression or some exception for data type mismatch due to different result expression.
But output is of DATE datatype and value is somewhat as of now irrelevant.
Now let route to Oracle Database and see how it behave for such different data type expression.
SELECT CASE WHEN 1=1 THEN 1
WHEN '2' = '2' THEN '2'
WHEN SYSDATE = SYSDATE THEN SYSDATE
ELSE NULL
END FROM DUAL;
Output :ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
In Oracle it is mandate for each result expression to be for same data type, implicit conversion is not impose.
Let understand, how SQL server behave with varying data type in CASE Expression.
1. Data type for Return expression is decided based on “Data Type Precedence”.
When we have expression that combine different data type, as in our case NUMBER , VARCHAR and DATE.It convert data type of lower precedence to higher precedence IFF valid.
For CASE example mentioned, expected return data type will be “DATE”
So in CASE statement examples, final output base on match was after implicit conversion of first match result expression (1) to DATE.
i.e. SELECT CAST(1 as DATETIME)
Output :: 1900-01-02 00:00:00.000
2.
Now let see what would be happen when second expression is match.
SELECT CASE WHEN 1!=1 THEN 1
WHEN '2' = 2 THEN '2'
WHEN GETDATE() = GETDATE() THEN GETDATE()
ELSE NULL
END
Output :: Conversion failed when converting date and/or time from character string.
Now we got an exception as we are trying to convert match result expression character data type to Date, which is not valid and hence not permitted.
Ideally it is always good practice to have similar datatype for each condition within any expression.
It will avoid any conversion failure at run-time.
Please share any feedback if any.!
Database and Migration Insights 