Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004

    Cool Unanswered: Oracle Sql*plus add two columns

    Hello There
    I am using sql*plus to write reports.

    I want to simplify one of my reports that has multiple sub queries that calculate some of the column values. I need to add the results of two columns (queries) as well as display the results in their normal column, say columns 3 and 4 with the addition of column 3 and 4 values displayed in column 5.

    I am at present duplicating the sql of both columns for the third with a + sign joining them. This works fine but I am sure that there is a better way of doing this.

    Possibly by storing the original two sub query results into two individual variables and then adding these variables to give the value required for column 5.

    I cannot find any examples of how to do this in my reference books and would be grateful for any assistance.
    Thank you for your time. Regards Allan Godney

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1
    The answer is to embed queries within queries, like Russian dolls, as in this (rather contrived) example:
    SQL> select dname, minsal, maxsal, minsal+maxsal from
      2  ( select dname
      3    ,      (select min(sal) from emp where emp.deptno = dept.deptno) minsal
      4    ,      (select max(sal) from emp where emp.deptno = dept.deptno) maxsal
      5    from   dept
      6  );
    -------------- ---------- ---------- -------------
    ACCOUNTING           1300       5000          6300
    RESEARCH              800       3000          3800
    SALES                 950       2850          3800

  3. #3
    Join Date
    Jun 2004
    Hello Tony
    Once I worked out how to change the SQL for the report it was much smaller and ran nearly twice as fast.
    Thank you this query can be closed.
    Allan Godney

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts