Hi. In this lesson, we talk about automatic type conversion. Conversion functions, which are used to convert one type of data to another and miscellaneous functions. Let's get started. Oracle supports automatic type conversion, which means if the given value is not of the required type, but can be converted to required type, a racquel automatically converts it. For example, any number or date can be converted to character.

So, if the context needs character, but what you're giving is a number or a date or I kill will automatically convert that to character. Similarly, if a character is given where a date is expected it will try to convert character to date provided the value is given in the required format. But date can never be converted to a number and number can never be converted to date. So, these are some guidelines related to automatic type conversion. Let us see how that works. So here if I say I want to multiply 10 with 20 Now, this is where I'm trying to multiply 20 and number with a string, but Oracle knows what it needs is a number so it will try to convert this to a number.

Let's go and you can see the conversion is done automatically. And similarly, if you try to use something like length of 1234 what we are giving is actually a number But what is expected is a character because length is a string function, it expects a character but still it works, because Oracle can convert this number to a string. And similarly, you can convert a string to date, for example, says date minus, and we are giving something like a date. So here if I say months between, this is where I need something like two dates, and I'm giving one date, then comma and the date I can give something like first January 2018, for example, it'll give me how many months are in between these two, because it knows that it can convert whatever is given there, though it is of type character, it's converted to date.

These are some of the examples where Oracle automatically converts. But there are cases where it cannot do that. For example, if I try to subtract the system date from days, I mean if I try to subtract a date from another date, but the date is not given in date data type, it's given as a string. So, according to us if you give a string, but if it is in the date format, Oracle can convert. So let's try and see whether it can do it. When you go ahead, it is not doing that.

It is not able to convert the given value to required type because it's a little confused. As you can see, it is saying it's an invalid number. It's treating it as a number. The reason is simple. We have a possibility, like we have a case where you can have a date subtracted from date. So you can have a number subtracted from the date, this is about number of days to be subtracted from date.

So, in between these two, when you give a string, Oracle is trying to treat it like this. So it assumes what you are giving is a number because it's not a date, it's a string, it will try to first convert that to number and that is not working. And that's exactly what the problem here is. So, how do we fix it? Well, the solution to this is our conversion functions by using conversion functions, we can explicitly convert the given value to the required type. So to date is what we need to use in this context.

So if I can use to date and then give the value then Oracle understands this To be converted to a date, then it will proceed with the operation. So, there are a few cases where you have to explicitly convert a given value to a required type in such a case, you have to use the function to date two character and to number those three are called as conversion functions. And if you look at the format's especially for to character and to date, you can give these characters to specify how the value should be treated. So going back, what if I'm giving something like 2018 one and one, this is not something that is understood by Oracle. So there it is throwing errors saying this is not something it understood. That's fine.

Now we can tell Oracle that this is to be treated as ear and then we talk about month And then then the date. So now it understands how to interpret those numbers. And again we get the result that we want. So two character and to date both can make use of some characters the format's. And here are those characters. They are used even into character.

So let's go to character and see how that can be used. So normally when you say CES date, you get only the date part. But I want to take not just the date but also the time part. So I can use h 24, colon EMI, and that will take the time portion from that. So there you see it's now 31st August 2019 and 11:50am talking about h 24 means 24 hours format, and if I gave a church and then give something like pm It's going to give me something like this it's 11:50am though you'll give me him, eight is going to give you either am or Pam depending on the time of your system. So this is how I can extract the time portion from the SIS date, you can do that for any date.

And of course, you can also display the date itself if you want like this and then you can get the time part of it. So in this case, I don't need to display that separately in one stroke I get both date as well as the time. So to character is to convert a date to a string but it can extract whatever you want. Of course if you want you can just take only a single part of it. Sometimes we are interested in getting only the ear nothing else. So this is what we can do.

If you go around this You will get only the year and nothing else. So how can we use it? Well, I want to find out employers who joined in the ER 2005. So I can say select first name, comma, hire date, comma salary, some details from employees. Were saying, if I say something like hire date, this gives me the completed date, but I'm only interested in one part of it. So one way to do that is to convert this to character and extract only here and then compare that with whatever you want.

So this is where we are looking for employees who joined in 2005. You can see these are the employees who joined in 2005. So that's the application of conversion function to character to date, in certain cases, even tonight. buffer will be of use. And then if you talk about the miscellaneous functions, miscellaneous functions are the ones which are not specific to any data type, they can be used with the different data types. And we have some examples here.

The first miscellaneous function is a decode, which can be used to compare the given expression with the given values. If department ID is 10 then we will be taking the value of point five zero. If department ID is 20, then we take point seven five, and if it is neither of them, then it'll go 4.25. This is a kind of if else if else structure. So, the code is pretty powerful with the help of days, we can compare one value with multiple value Use wherever it is matching the value, we take the corresponding value. So this is to calculate the bonus of the employee, which happens to be 50% if employee belongs to department 10 and 75% if employee belongs to department 20.

Otherwise the rest of them would get 25%. That's the way it goes. Now, that's the first example here, I want to find out what is the greatest of the given two values. So this is where I want to know which is greater. his salary by two is greater or 1000. Because I want to take the greatest of these two values and then use that maybe we are announcing some bonus and we want to make sure that everybody gets a place to 1000 so off of the salary are 1000, whichever is Higher.

So that's greatest, the opposite to that is least whichever is least. So, we want to make sure nobody gets more than 1000. So, this is the value if it is less than 1000 employees would get this otherwise he would get 1000. This is what we typically see in many online shopping scenarios, they say you get a discount of 50% subject to a maximum of 1000. That means, if you're 50% is less than 1000 fine, otherwise you get only 1000 and then we have a couple of functions related to null and they are extremely important. Let us see what is the purpose?

Let's try to find out the net salary of the employee. So how do we calculate net salary a salary plus the commission so salary Do commission percentage commission percentage is the percentage of commission employees entitled to get it is calculated as a percentage based on the salary. So salary plus commission is going to be the net salary. That's fine. Let's go and try this is what we are getting what is that? Now, for many employees, the total salary is null but for some it is giving valid value.

What's happening? Well, this is another very important part of any database especially Oracle. So what we do is salary let us say is 5000 and then we want to say salary into and for this person, let us say the commission percentage is null. When commission percentage is null then The expression is going to return again. Now, any expression involving null returns. Now, that's a point worth noting down.

Any expression involving a null value, again, returns null. So we get null from this. And that plus 5000 is again now that's exactly what you're saying for all these employees, because their commission percentage is null. So we are going to get only now as the final result. So how do we solve this problem? We solve this problem by treating this null as zero.

If I can treat null as zero, then this is going to be zero into salary zero, and zero plus salary is going to be the actual salary. And that's the way we solve the problem. But how do we do it? Well, here it is. You Go to a function called NVL NVL functions says gave me the expression and if this is null I will return this and if it is not now this is returned as these. So, the correct way to calculate net salary of the employee his salary plays salary into not directly commission person taste, but NVL of commission person test comma zero this is what we need to use.

So, this is a very very important function because it can help you to treat null as something else, because null values are not going to go well in expressions. So, here is an example and now we will fix this So, how do we fix it NVL of commission person taste come comma zero. So, if commission percentage is the Now we treat it as zero. Otherwise we take it as it is go ahead. Now, everything is fine. Now all employees get proper net salary.

So that's how we need to use those functions. And there are a few more versions of NVL like NVL to null if and coalesce is a function which returns the first not null value. So it is going to examine the given values, whichever is not null. It takes that and then it stops. So this is an example for coil as and I'm trying to find out from locations table, postal code, state province city, and in that order, so if postal code is null, we ignored if state provinces also now we ignored then if six is not null, we take it. But in a particular row, we do have a postal code straight away, it would pick that.

So that's the way it is going to work. It's called coil is very interesting function. And then we also have not a function actually, it's called case expression. And this case expression can be used just like a D code, but it's more flexible, more readable. So, I say, compare job ID with the it programmer, if job ID is it programmer, then I want this to be returned. And if it is not it programmer then check whether it is sales rep.

If that is the case, we want this to be returned. In all the remaining cases, I want to return this. So this is called the case expression and we want to take the value and We want to display that as new salary of the employee. So, this is case expression. There are some other flavors of it that we will see later. But for now, this is the way it goes.

So the whole expression will end up as one new column here. So you get first name, job ID salary and also the new salary of employed. So, that's about case expression. And that's all in this lesson. So we understood automatic type conversion that is available in Oracle. We understood how to use two character and to date functions to have the commonly used functions for type conversion.

We understood miscellaneous functions like a D code, greatest lays, but the most important of all is NVL, which is very widely used. And also we looked at case expression, but there are other flavors like case statement, which we will see later. Okay, so get used to them and we'll resume in the next lesson.