Hi Oracle fellas,
Some times in oracle developments, we have to compare various kinds of values, table columns and etc.. to give a comfortable out put for the users. In this process, some times we have to give them feedback by showing them some kind of status or a value putting to newly created column which is not in the related database table.
As an example, just imagine we have table name "Employees" and it include Emp_No, Emp_name, Previous_Year_Salary and Current_Year_Salary columns. We have to compare the previous salary with current salary and return a value as M if those two values are equal and NM if not equal. Apart from that we have to display above value in a separate column name "any name" which not include in Employees table. Just image that we are advised to not to add any new column to the related table.
All you have to do is write a simple SELECT statement and put two columns into decode function with two values you have to return accordingly. It's like,
SELECT Emp_No, Emp_name, DECODE(Previous_Year_Salary , Current_Year_Salary, 'M', 'NM')
FROM Employees;
Above statement will return all the fetched records with the relevant comparison and relevant newly added column values. You can put a name to new column like bellow.
SELECT Emp_No, Emp_name, DECODE(Previous_Year_Salary , Current_Year_Salary, 'M', 'NM') as 'any name'
FROM Employees;
Apart from this DECODE function includes lots of formulas we can use. Within DECODE function we can not only compare numbers, but also dates and characters as well.
Some times in oracle developments, we have to compare various kinds of values, table columns and etc.. to give a comfortable out put for the users. In this process, some times we have to give them feedback by showing them some kind of status or a value putting to newly created column which is not in the related database table.
As an example, just imagine we have table name "Employees" and it include Emp_No, Emp_name, Previous_Year_Salary and Current_Year_Salary columns. We have to compare the previous salary with current salary and return a value as M if those two values are equal and NM if not equal. Apart from that we have to display above value in a separate column name "any name" which not include in Employees table. Just image that we are advised to not to add any new column to the related table.
All you have to do is write a simple SELECT statement and put two columns into decode function with two values you have to return accordingly. It's like,
SELECT Emp_No, Emp_name, DECODE(Previous_Year_Salary , Current_Year_Salary, 'M', 'NM')
FROM Employees;
Above statement will return all the fetched records with the relevant comparison and relevant newly added column values. You can put a name to new column like bellow.
SELECT Emp_No, Emp_name, DECODE(Previous_Year_Salary , Current_Year_Salary, 'M', 'NM') as 'any name'
FROM Employees;
Apart from this DECODE function includes lots of formulas we can use. Within DECODE function we can not only compare numbers, but also dates and characters as well.
