Hi all, Im having a DB2 query which is below. I need the equivalent of it in ORACLE.
I came to know that there is no direct equivalent for DIGITS function in Oracle. I have wasted a lot of time in finding out this.
Immediate help will be highly helpful
WHEN MAX(i) IS NULL THEN
ELSE SUBSTR(DIGITS(MAX(CAST( j AS INTEGER ))+1), 8,3)
raj WHERE k = 2;
The DIGITS function returns a character-string representation of a number.
The argument must be an expression that returns a value of type SMALLINT, INTEGER, BIGINT or DECIMAL.
If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result of the function is a fixed-length character string representing the absolute value of the argument without regard to its scale. The result does not include a sign or a decimal character. Instead, it consists exclusively of digits, including, if necessary, leading zeros to fill out the string. The length of the string is:
* 5 if the argument is a small integer
* 10 if the argument is a large integer
* 19 if the argument is a big integer
* p if the argument is a decimal number with a precision of p.
* Assume that a table called TABLEX contains an INTEGER column called INTCOL containing 10-digit numbers. List all distinct four digit combinations of the first four digits contained in column INTCOL.
SELECT DISTINCT SUBSTR(DIGITS(INTCOL),1,4)
* Assume that COLUMNX has the DECIMAL(6,2) data type, and that one of its values is -6.28. Then, for this value:
But DIGITS function will trail some ZERO's to the given value based on the type(INteger,longint etc....), where as to_char() doesn't have such property.
For ex im having
select DIGITS(CAST( 2 as INTEGER )) from SYSIBM.SYSDUMMY1 in DB2 will return 0000000003
but SELECT to_char(CAST(2 as INTEGER)) FROM SYS.DUAL will return 2