Oracle SQL - Function SQRT function

Introduction

SQRT function Returns the square root of a number or value.

SQRT will not accept Negative values in its domain.

Demo

SQL>
SQL> drop table my_table;

Table dropped.-- from  w  w w. j a  va 2s.  co m

SQL> CREATE TABLE my_table (
  2      LINENO NUMBER(2) not null,
  3      VALUE NUMBER(6,2) not null
  4  );
SQL>
SQL> insert into my_table values(1,1.2);
SQL> insert into my_table values(2,123.34);
SQL>
SQL> SELECT lineno, value, SQRT(value)
  2  FROM my_table
  3  ORDER BY lineno;

   LINENO |     VALUE | SQRT(VALUE)
--------- | --------- | -----------
 00001.00 |  00001.20 |    00001.10
 00002.00 |  00123.34 |    00011.11

SQL>
SQL>
SQL> insert into my_table values(3,-12.2);
SQL> insert into my_table values(4,100);
SQL> insert into my_table values(5,48);
SQL> insert into my_table values(6,-90);
SQL> insert into my_table values(7,0.19);
SQL>
SQL> SELECT lineno, value, SQRT(value)
  2  FROM my_table
  3  ORDER BY lineno;
SELECT lineno, value, SQRT(value)
                           *
ERROR at line 1:
ORA-01428: argument '-12.2' is out of range


SQL>

Use nested function to ensure that the SQRT function sees only a positive domain.

Demo

SQL>
SQL> SELECT lineno, value, ABS(value), SQRT(ABS(value))
  2  FROM my_table
  3  ORDER BY lineno;-- w  w  w  . ja  v  a2s. c  o  m

   LINENO |     VALUE | ABS(VALUE) | SQRT(ABS(VALUE))
--------- | --------- | ---------- | ----------------
 00001.00 |  00001.20 |   00001.20 |         00001.10
 00002.00 |  00123.34 |   00123.34 |         00011.11
 00003.00 | -00012.20 |   00012.20 |         00003.49
 00004.00 |  00100.00 |   00100.00 |         00010.00
 00005.00 |  00048.00 |   00048.00 |         00006.93
 00006.00 | -00090.00 |   00090.00 |         00009.49
 00007.00 |  00000.19 |   00000.19 |         00000.44

7 rows selected.

SQL>