Results 1 to 2 of 2
  1. #1
    Join Date
    May 2005
    Posts
    7

    Question Unanswered: how to create "almost" cartesian produkt?

    I have to solve following task. Assume an entity has 3 relationships to other entities, for example an ETL job 1) reads from some tables 2) writes to some tables and 3) runs some shell commandos:

    Code:
    Job  Input
    -----------
    Job1 In1
    Job1 In2
    Code:
    Job  Output
    -----------
    Job1 Out3
    Code:
    Job  CMD
    -----------
    Job1  mail
    Job1  cp
    Job1  del
    The key column is Job, in all 3 tables.

    So, I have to create following report:

    Code:
    Job   Input    Output     CMD
    ----------------------------
    Job1  In1        Out3       mail
    Job1  In2        NULL       cp
    Job1  NULL       NULL       del
    Job2  NULL      NULL       NULL <- Empty jobs should be also in result


    To get more information about jobs I have also to join some other tables, maybe left/right outerwise. It's not important for this exercise, but you should keep it in mind, because of some oracle restrictions with too many outer joins.

    Any idea?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This isn't really a query that joins data from 3/4 tables, it is a report made up of 4 separate queries:
    1) Jobs
    2) Inputs for Job
    3) Outputs for Job
    4) CMDs for Job

    Queries 2-4 are run for each Job row found by query 1, and the report is laid out horizontally.

    Most report-writing tools (such as Oracle Reports) can do this very easily, but it would be a nightmare to try to write a single SELECT statement to do it all.

Posting Permissions

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