Handling Null values in Oracle | Oracle interview question difference between null functions |Tamil
Вставка
- Опубліковано 3 гру 2020
- #Oracle#NVL#NVL2#NULLIF#COALESCE
========================================================
🌕Want to join WhatsApp Group? (Only technical discussion, no spam messages, no promotion msg)
📌Informatica PowerCenter
github.com/nicitacademy/Infor...
📌Oracle-SQL
github.com/nicitacademy/Oracl...
📌IICS
github.com/nicitacademy/IICS
========================================================
Handling Null values in Oracle:
================================
NVL - 2 arg
NVL2 - 3 arg
Nullif - 2 arg
Coalesce - n arg
NVL function will check whether the first input parameter is null, if first input parameter is null then the function returns the second parameter value as output.
NVL2 function will check the first parameter, and return second parameter if the first parameter is not null, otherwise returns third parameter.
NULLIF will compare the input parameters ( first and second parameter ), and returns NULL of both are same, otherwise returns the first parameter value.
COALESCE, returns the first not null expression in the given input parameters
nvl function in sql
what is nvl function in oracle
what is the use of nvl2 function in oracle
what is the use of nvl function in oracle
Oracle NVL() Function By Practical Examples - Oracle Tutorial
Oracle / PLSQL: NVL Function
How to use the nvl function in Oracle
Oracle NVL Function Explained with Examples
NVL(arg1,arg2)
if arg1 is null ==arg2
if arg1 is not null == arg1
select NVL(5,6) from dual; -- 5
select NVL(null,6) from dual; --6
select * from employees;
select employee_id,salary,commission_pct,salary+(salary*commission_pct) total_salary from employees;
select employee_id,salary,commission_pct,salary+(salary*nvL(commission_pct,0)) total_salary from employees;
NVL2(arg1,arg2,arg3)
if arg1 is null --- arg3
if arg1 is not null -- arg2
select nvl2(4,8,12) from dual; --8
select nvl2(null,8,12) from dual; -- 12
create table employee
(
employee_id number,
emp_name varchar2(30),
allocation_id number(10),
location varchar2(30)
);
SELECT employee_id,emp_name,
NVL2(allocation_id, 'Allocated', 'Waiting for project') allocation_status
FROM EMPLOYEE;
-----------------------------------------------------------
nullif(arg1,arg2)
if arg1=arg2 --- null
if arg1 != arg2 --- arg1
select nullif(5,8) from dual; --5
select nullif(8,8) from dual; -- null
CREATE OR REPLACE FUNCTION FUNCTION_DIV(A NUMBER, B NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN 8/0;
END;
select FUNCTION_DIV(4,0) from dual;
FUNCTION_DIV(5,0) will raise ORA-01476:divide by zero exception.
CREATE OR REPLACE FUNCTION F_DIV(A NUMBER, B NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN (A/NULLIF(B,0));
END;
select FUNCTION_DIV(4,2) from dual;
FUNCTION_DIV(5,0) would return NULL, instead of raising exception.
-----------------------------------------------------------
coalesce(arg1,arg2,arg3.. ..arg_n)
-- It will return first not null value
COALESCE -- It will always return first not null value
select commission_pct,manager_id,department_id from employees;
select commission_pct,manager_id,department_id,
COALESCE(commission_pct,manager_id,department_id,0) from employees;
select coalesce(mobile_no,office_no,resi_no,'no_phone_number') from dual;
*Complete 48 hours of Self-Paced Courses and Interview Questions Links:*
courses.nicitacademy.com/
Are you interested in joining my Live training? or Self-Paced course?
You can visit our website for more details.
www.nicitacademy.com/
Contact me for more details @ 🌕 wa.link/m4lat0
*WhatsApp group:*
chat.whatsapp.com/KMN5pKoLGSu8PuFLxSouB0
Thanks,
NiC IT Academy
=======================================================================
🔵Oracle SQL Tutorial - English
ua-cam.com/play/PLsphD3EpR7F9mmtY2jBt_O8Q9XmvrhQEF.html
📌*IICS demo session:*
ua-cam.com/video/C1zGVRFM3-A/v-deo.html
📌*Informatica Full Course:*
ua-cam.com/video/V2FtZ_INmjg/v-deo.html
📌*SQL Full Course:*
ua-cam.com/video/iuvzjf7nU1I/v-deo.html
📌*Informatica, IICS, and SQL playlist*
🌕 IICS Tutorial ENGLISH - Playlist
ua-cam.com/play/PLsphD3EpR7F9sqnL3R0jeETHP08Touklj.html
🌕 Informatica training in ENGLISH - Playlist
ua-cam.com/play/PLsphD3EpR7F9HtxQHfoRAStSWAuF7zBfd.html
🌕 Informatica Interview Preparation - ENGLISH
ua-cam.com/play/PLsphD3EpR7F-z_PFoL-uFc9_4DEnYsR3d.html
🌕Oracle SQL - தமிழில்
ua-cam.com/play/PLsphD3EpR7F-u4Jjp_3fYgLSsKwPPTEH4.html
🔵 Informatica training in தமிழில் - Playlist
ua-cam.com/play/PLsphD3EpR7F8dOIX6bhpv3fnZ8Io_WrH3.html
🔵 Informatica Interview Preparation - TAMIL
ua-cam.com/play/PLsphD3EpR7F8MoCdaGFY_xO7mMfT3fvnD.html
🔵Unix for Informatica Developer
ua-cam.com/play/PLsphD3EpR7F8jeo31oVNJ0yG9AaD-5_jw.html
🌕Informatica Project videos
ua-cam.com/play/PLsphD3EpR7F98r1Mp4QGT9iYmTNwyzPa6.html
Please subscribe, Like, and comment. I will post more videos.
Thanks
Good clarification. Simultaneously please add PLSQL topics and performance tuning
Hi Prabhu, Sure. I will add PLSQL part as well soon. Thanks for your feedback. Keep watching.
Please reupload in better quality 1080p it is available now 420p ......please consider sir....it was. Already posted in day session but I need subract video because whenever it come to doub especially in this part that can go to watch as well important topic thats why please consider one topic ....thank you.
It is uploaded with better quality only. It may be due to your network speed and your system setting. Pleas change it. I could see better quality of this video in my system..