Which statement is true regarding the COALESCE function?
A.
It can have a maximum of five expressions in a list.
B.
It returns the highest NOT NULL value in the list for all rows.
C.
It requires that all expressions in the list must be of the same data type.
D.
It requires that at least one of the expressions in the list must have a NOT NULL value.
is it ‘c’ b/c oracle guide explain like ++++the data type COALESCE returns if a not null value is found is the same as that
of the first not null parameter. To avoid an “ORA-00932: inconsistent data types”
error, all not null parameters must have data types compatible with the first not null
parameter. Consider the following three queries:
Query 1: select coalesce(null, null, null, ‘a string’) from dual;
Query 2: select coalesce(null, null, null) from dual;
Query 3: select coalesce(substr(‘abc’,4),’Not bc’,’No substring’) from dual;
Query 1 returns the fourth parameter: a string, since this is the first not null
parameter encountered. Query 2 returns null because all its parameters are null.
Query 3 evaluates its first parameter, which is a nested SUBSTR function, and finds
it to be null. The second parameter is not null so the string ‘Not bc’ is returned.
+++++
Answer is C. Does this mean COALESCE will not do implicit conversion like NVL does?
so:
COALESCE – will not do datatype implicit conversion
NVL – will do datatype implicit conversion
Right?
Option C (must be the same data type), is not correct
Take this example:
SELECT product_id, list_price, min_price,
COALESCE(0.9*list_price, min_price, 5) “Sale”
FROM product_information
WHERE supplier_id = 102050
ORDER BY product_id, list_price, min_price, “Sale”;
If there is no list price, then the sale price is the minimum price. If there is no minimum price, then the sale price is “5”
PRODUCT_ID LIST_PRICE MIN_PRICE Sale
———- ———- ———- ———-
1769 48 43.2
1770 73 73
2378 305 247 274.5
2382 850 731 765
3355 5
the example was from oracle documentation:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions023.htm
select coalesce(null,1,’one’) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
select coalesce(null,’one’,1) from dual
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
‘B’ OR ‘C’ ?
I HAVE TRIED THIS
SELECT COALESCE(NULL,NULL,NULL,NULL,NULL,NULL,NULL) FROM DUAL;
SO ‘A’ AND ‘D’ IS INCORRECT
I am sure this paragraph has touched all the internet visitors,
its really really nice paragraph on building up new website.
Best VIP cheap Flyers jerseys Store
Option C is COrrect.
Non-numeric expressions (such as strings or dates) must all be of the same data type, and return a value of that data type. Specifying expressions with incompatible data types results in an SQLCODE -378, and a %msg error message value.
Numeric expressions may be of different data types. If you specify numeric expressions with different data types, the data type returned is the expression data type most compatible with all of the possible result values, the data type with the highest data type precedence. The following data types are compatible and are specified in order of precedence (highest to lowest): DOUBLE, NUMERIC, BIGINT, INTEGER, SMALLINT, TINYINT.
The time to read or check out the content material or web-sites we have linked to below.