Which two are true about the NVL, NVL2, and COALESCE functions? (Choose two.)
Which two are true about the NVL, NVL2, and COALESCE functions? (Choose two.)
NVL requires the expressions to have the same data type because while Oracle allows implicit data type conversion, it is best practice to ensure that the expressions are of the same type to avoid unexpected behavior or errors. COALESCE stops evaluating the list of expressions when it finds the first non-null value, which makes it more efficient when dealing with multiple possible expressions where only the first non-null value is needed.
A: NVL, does implicit conversion. Could have expressions of different datatype
First expression of NVL2 is never returned, it's only used for evaluation.
DE is correct the data types of the nvl are possible to explicitly convert the data type
DE is correct: D. COALESCE stops evaluating the list of expressions when it finds the first non-null value. TRUE https://www.oracletutorial.com/oracle-comparison-functions/oracle-coalesce/ E. The first expression in NVL2 is never returned. TRUE If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3. (https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/NVL2.html)
It should be AD. A: it is true that oracle can make implicit conversions but still the NVL function requires that the two exps be of the same data type. Hence A is correct E is wrong because if we specified that we want the same exp to be returned when it's not null it would return its first exp. Tried this in Toad and got 'h1' select nvl2('h1', 'h1', 4) from dual;
nvl2(expression1,expression2,expression3) so even expression2 = expression1 we will not say expression1 is returned A: we can implicitly use different type of data with nvl select nvl(1,'1') from dual; it will work so A is incorrect D,E is correct.
A is not necessarily be true. DB applies implicit conversation which sometimes will not work. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/NVL.html
A: NVL must have expressions of same datatype my inputs- here in option used must have hence it is not right SELECT NVL(ENAME, 0) FROM EMP; -- Here Ename is Varchar2 and we are passing 0 Number data type as second parameter. In this scenario, NVL working for 2 different datatypes.
Implicit conversion is always successful from number to varchar, not the opposite.
D IS CORRECT COALESCE () BEACUSE Return first not null expression in the expression list.
E IS CORRECT The first expression in NVL2 is never returned, it is only used to determine whether expression2 must be returned, or expression3.
E is not right because NVL2 (expr1, expr2, expr3) expr1 is the source value or expression that may contain null expr2 is the value returned if expr1 is not null expr3 is the value returned if expr1 is null F is not right because The COALESCE() function returns the first non-null value in a list.
AD is correct, if not then please correct me.
A, D is correct
it's also true the A, select nvl(1,'pippo') from dual returns ora-01722
Yes, but some values can be impicit converted into anotherdata type like nvl(1,'1').