5 Replies Latest reply: Apr 10, 2012 1:06 PM by 929681 RSS

    Querying and adding rows

    929681
      Hello everyone!

      I am developing a query for a report, the thing is that while retrieving data from database this report should populate some rows, which do not exist. For illusrtrating purpose lets say i have these tables :
      Table 1 - Companies
      Table 2 - Transactions.
      Table 3 - Transaction types.

      Important detail that most of the companies do not have transactions of all transaction types. Although the report logic requires to dysplay a company with all of them : "real" ones with real money values and other, not existed ones with just $0. The problem starts here because transaction types are combined in logical groups, so lets say if a company has only 1 real transaction of type_1, the report should contain "$0" records of other types associated with type_1, like type_2, type_3 and type_4. If company has transactions of type_1 and type_2, report should be populated with some other tran types from different transaction type group etc.

      The problem here is that the environment where it should be executed must be a pure sql (being a java programmer i understand how easy is to query database, load data into array[][] and add missing transaction types) - but the query should be ran on unix inside plsql batch so it should be single (or joined) select.

      Thanks in advance. Any help or ideas would be very appreciated!
        • 1. Re: Querying and adding rows
          Frank Kulash
          Hi,

          Welcome to the forum!

          Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved.
          Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
          If you can illustrate your problem using tables in the scott or hr schemas, then you can skip the sample data; just post the results you want, and an explanation.
          Always say which version of Oracle you're using.
          See the forum FAQ {message:id=9360002}

          This sounds like a job for a pratitioned outer join.
          Say you want a list of all the departments from scott.dept, and all jobs from scott.emp, with the number of employees who have that job in that department, but you want to include combinations that don't acutally occur in the data. For example, the only PRESIDENT in scott.emp is in deptno=10, but we want to show PRESIDENT (with a count = 0) in all the other departments, too.
          Here's one way to do that:
          SELECT       d.deptno
          ,       d.dname
          ,       e.job
          ,       COUNT (e.deptno)     AS cnt
          FROM             scott.dept  d
          LEFT OUTER JOIN      scott.emp   e  PARTITION BY (job)
                                            ON   d.deptno     = e.deptno
          GROUP BY  d.deptno
          ,            d.dname
          ,       e.job
          ORDER BY  d.deptno
          ,            e.job
          ;
          Outut:
          `   DEPTNO DNAME          JOB              CNT
          ---------- -------------- --------- ----------
                  10 ACCOUNTING     ANALYST            0
                  10 ACCOUNTING     CLERK              1
                  10 ACCOUNTING     MANAGER            1
                  10 ACCOUNTING     PRESIDENT          1
                  10 ACCOUNTING     SALESMAN           0
                  20 RESEARCH       ANALYST            2
                  20 RESEARCH       CLERK              2
                  20 RESEARCH       MANAGER            1
                  20 RESEARCH       PRESIDENT          0
                  20 RESEARCH       SALESMAN           0
                  30 SALES          ANALYST            0
                  30 SALES          CLERK              1
                  30 SALES          MANAGER            1
                  30 SALES          PRESIDENT          0
                  30 SALES          SALESMAN           4
                  40 OPERATIONS     ANALYST            0
                  40 OPERATIONS     CLERK              0
                  40 OPERATIONS     MANAGER            0
                  40 OPERATIONS     PRESIDENT          0
                  40 OPERATIONS     SALESMAN           0
          Edited by: Frank Kulash on Apr 10, 2012 1:33 PM
          • 2. Re: Querying and adding rows
            Peter vd Zwan
            Hi,

            Maybe it woould be nice to show some table structures and sample data.
            Oracle version qexpected output, ....
            And all other data we need to answer your question.

            Just an idea.

            Regards,

            Peter
            • 3. Re: Querying and adding rows
              929681
              Frank,

              thank for your idea. I will definetelly try.

              Unfortunatelly i cannot (can but better not to) post any of tables/schema examples coz all of data here is under clearance and belongs to governmental projects.
              Also, the tables names and functionality is changed due to the same reason. (The initial query report involves about of 15 tables with 5 union all's). Thanks for your understanding.

              Sorry forgot to mention - its 11gr2.

              Edited by: 926678 on Apr 10, 2012 10:46 AM
              • 4. Re: Querying and adding rows
                Frank Kulash
                Hi,
                926678 wrote:
                ... Unfortunatelly i cannot (can but better not to) post any of tables/schema examples coz all of data here is under clearance and belongs to governmental projects.
                There's no need to post your real table names. In fact, you wouldn't want to even if there were no security issues. Your real tables almost certainly have columns that play no role in a given problem. You don't want to include those columns in your data or explanation.
                Also, the tables names and functionality is changed due to the same reason. (The initial query report involves about of 15 tables with 5 union all's). Thanks for your understanding.
                All the more reason to post tables that you created just for solving thsi problem. You can simplify things a great deal. For example, you can create a single CREATE TABLE statment that shows what the result set of your real 15-table 5-UNION query.
                • 5. Re: Querying and adding rows
                  929681
                  Frank,

                  sorry forgot to mention, its planned to be executed from read-only role.