Quadratic equation in SQL

Example for versions Oracle 10g SQL, Oracle 11g SQL

This example was tested in SQL*Plus and TOAD.

Pure SQL allows to input values at runtime in the form of substitution variables. To define a substitution variable, use its name (in this case A, B and C) with an ampersand & before it each time you need to reference it. When the query needs to be executed, you will receive a prompt to enter the values of the variables. Each reference to such variable will be replaced with its value, and the resulting query will be executed.

There are several ways to input the values for substitution variables. In this example first reference to each variable has double ampersand && before its name. This way the value of the variable has to be entered only once, and all following references will be replaced with the same value (using single ampersand in SQL*Plus asks to enter the value for each reference to the same variable). PL/SQL Developer requires that all variables have single & before their names, otherwise ORA-01008 “Not all variables bound” exception will be raised.

Note that the references are substituted with the values “as is”, so negative values of coefficients have to be entered in brackets.

First line of the example sets the character for decimal separator which is used when the numbers are converted into strings.

The query itself is composed of four different queries. Each query returns a string containing the result of calculations for one case of quadratic equation, and returns nothing for three other cases. The results of each query are united to produce the final result. 

alter session set NLS_NUMERIC_CHARACTERS='. ';

select 'Not a quadratic equation.' ans
  from dual
 where &&A = 0
union
select 'x = ' || to_char(-&&B/2/&A)
  from dual
 where &A != 0 and &B*&B-4*&A*&&C = 0
union
select 'x1 = ' || to_char((-&B+sqrt(&B*&B-4*&A*&C))/2/&A) || ', x2 = ' || to_char(-&B-sqrt(&B*&B-4*&A*&C))/2/&A
  from dual
 where &A != 0 and &B*&B-4*&A*&C > 0
union
select 'x1 = (' || to_char(-&B/2/&A) || ',' || to_char(sqrt(-&B*&B+4*&A*&C)/2/&A) || '), ' || 
       'x2 = (' || to_char(-&B/2/&A) || ',' || to_char(-sqrt(-&B*&B+4*&A*&C)/2/&A) || ')' 
  from dual
 where &A != 0 and &B*&B-4*&A*&C < 0;