Achal Shah
Achal Shah

Reputation:

dividing results of two PL/SQL select statements

The results of my two PL/SQL select statements are integers 27 & 50, I want their division (27/50) 0.54 at output...how to do that?

I have tried select * from ((select....)/(select ...)) but it does not work!!

Upvotes: 5

Views: 32755

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

In your FROM clause you can only join result sets together, you can't use other operators like that.

You can, however, use arithmetic operators in your SELECT clause, e.g. (as Alex has already said):

SELECT (SELECT thefirst ...)
       /
       (SELECT thesecond ...) AS result
FROM DUAL;

or, alternatively:

SELECT A.thefirst / B.thesecond AS result
FROM   (SELECT thefirst ...) A
      ,(SELECT thesecond ...) B;

The first approach will fail if the result sets do not have exactly one row each.

The second approach will work even if the result sets have more than one row - you may have to supply join criteria between A and B if you don't want a cartesian join between the two result sets.

Upvotes: 5

Alex Martelli
Alex Martelli

Reputation: 881497

SELECT 
  (SELECT thefirst FROM singlerowtable) / 
  (SELECT theother FROM othersinglerow) AS result

You can also use CAST(thefirst AS FLOAT) if you want to ensure a FLOAT division, &c.

Upvotes: 23

Related Questions