1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Combining two tables into one to show on the report.

Discussion in 'Oracle Forms and Reports' started by js98, Dec 16, 2017.

  1. js98

    js98 Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    usa
    I obtaining information from one main table to retrieve employees and spouses, and display the fields in my report. I'm counting employees and spouses based on different scenarios or grouping. I'm having trouble coming tb_emp and tb_spouse. I'm getting cartesian product with the following join.

    Please advise me as to how can I combine two mini tables. Thanks in advance!

    With tb_main as
    (
    SELECT CLIENT_NO, AMOUNT, ACTIVITY_TYPE, PERSON_ID, EMP_SPOUSE_NUMBER
    FROM table_main_db
    WHERE EMPLOYER_NUMBER = 1234
    ),

    tb_emp as
    (SELECT e.CLIENT_NO, e.AMOUNT, e.EMP_SPOUSE_NUMBER,
    COUNT(e.CLIENT_NO) OVER (PARTITION BY e.CLIENT_NO) emp_group,
    SUM(e.AMOUNT) OVER (PARTITION BY e.CLIENT_NO) emp_amt_group,
    COUNT(DISTINCT e.ACTIVITY_TYPE) OVER (PARTITION BY e.CLIENT_NUMBER) emp_act_group
    FROM tb_main e
    WHERE PERSON_ID = 11 -- id for employees
    ),

    tb_spouse as
    (SELECT s.CLIENT_NO, s.AMOUNT, s.EMP_SPOUSE_NUMBER,
    COUNT(s.CLIENT_NO) OVER (PARTITION BY s.CLIENT_NO) spouse_group,
    SUM(s.AMOUNT) OVER (PARTITION BY s.CLIENT_NO) spouse_amt_group,
    COUNT(DISTINCT s.ACTIVITY_TYPE) OVER (PARTITION BY s.CLIENT_NO) spouse_act_group
    FROM tb_main s
    WHERE PERSON_ID = 99 -- id for spouse
    ),

    SELECT e.CLIENT_NO, e.AMOUNT, e.emp_group, e.emp_amt_group,
    s.CLIENT_NO, s.AMOUNT, s.spouse_group, s.spouse_amt_group
    FROM tb_emp e
    LEFT OUTER JOIN tb_spouse s ON (e.EMP_SPOUSE_NUMBER = s.EMP_SPOUSE_NUMBER)
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,564
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    We need create table statements and sample data to see what you're seeing. A Cartesian join means you have no common columns to join on; you need to run each query to see what your results look like. It appears that the emp_spouse_number columns don't provide data to join on between the two tables.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,564
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I don't see how you're getting any results since the query you posted has a comma on line 22 that needs to be removed. Once that's done I see no Cartesian product in the output:

    Code (SQL):
    SQL> WITH tb_main AS
      2  (
      3  SELECT CLIENT_NO, AMOUNT, ACTIVITY_TYPE, PERSON_ID, EMP_SPOUSE_NUMBER
      4  FROM table_main_db
      5  WHERE EMPLOYER_NUMBER = 1234
      6  ),
      7  tb_emp AS
      8  (SELECT me.CLIENT_NO, me.AMOUNT, me.EMP_SPOUSE_NUMBER,
      9  COUNT(me.CLIENT_NO) OVER (PARTITION BY me.CLIENT_NO) emp_group,
    10  SUM(me.AMOUNT) OVER (PARTITION BY me.CLIENT_NO) emp_amt_group,
    11  COUNT(DISTINCT me.ACTIVITY_TYPE) OVER (PARTITION BY me.CLIENT_NO) emp_act_group
    12  FROM tb_main me
    13  WHERE me.PERSON_ID = 11
    14  ),
    15  tb_spouse AS
    16  (SELECT ms.CLIENT_NO, ms.AMOUNT, ms.EMP_SPOUSE_NUMBER,
    17  COUNT(ms.CLIENT_NO) OVER (PARTITION BY ms.CLIENT_NO) spouse_group,
    18  SUM(ms.AMOUNT) OVER (PARTITION BY ms.CLIENT_NO) spouse_amt_group,
    19  COUNT(DISTINCT ms.ACTIVITY_TYPE) OVER (PARTITION BY ms.CLIENT_NO) spouse_act_group
    20  FROM tb_main ms
    21  WHERE ms.PERSON_ID = 99
    22  )
    23  SELECT e.CLIENT_NO, e.AMOUNT, e.emp_group, e.emp_amt_group,
    24  s.CLIENT_NO, s.AMOUNT, s.spouse_group, s.spouse_amt_group
    25  FROM tb_emp e
    26  LEFT OUTER JOIN tb_spouse s ON (e.EMP_SPOUSE_NUMBER = s.EMP_SPOUSE_NUMBER);

    CLIENT_NO     AMOUNT  EMP_GROUP EMP_AMT_GROUP  CLIENT_NO     AMOUNT SPOUSE_GROUP SPOUSE_AMT_GROUP
    ---------- ---------- ---------- ------------- ---------- ---------- ------------ ----------------
             1        100          5           500          1        100            2              200
             1        100          5           500          1        100            2              200
             1        100          5           500
             1        100          5           500
             1        100          5           500

    SQL>
    You need to post your create table statements, your data and your output.
     
  4. js98

    js98 Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    usa
    Yes, you are right. While editing, I must've accidentally placed the comma there. Spouse and employee are in the same table and are connected with the emp_spouse_number. When I ran this report on crystal report, I was getting very big results in my count. I'm thinking may be I've created a Cartesian product or correlated query. Right now I'm testing my query on a smaller database. If same issue then I'll get back. Thanks
     
  5. js98

    js98 Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    usa
    I have a table that contains employee's information and spouse's information. They both have their own client_number. Both employees and spouses participate in several activities due to which their associated client_number repeats in the table.
    I want to do two things;
    1) count the number of employees and spouses.
    2) count how many employees and spouses (separately) participate in specific number of activities. For this part the output should be as shown below.
    upload_2017-12-21_18-53-27.png
    I need to group by client number to get their activities. Then I'm using cross tab in crystal to display the result.
    The problem is that when I run my queries separately I get the right result, but when I join them I'm getting wrong result. I don't know how to join all the queries together. Any suggestion will be greatly appreciated. Following is my query.
    Code (SQL):
    WITH tb_main AS
    (
    SELECT CLIENT_NUMBER,  [COLOR=#ff0000]-->is different for employees and spouses[/COLOR]
    ACTIVITY_TYPE,     [COLOR=#ff4d4d]--> the activity they participate in[/COLOR]
    PERSON_ID,        [COLOR=#ff0000]--> 11 for employees and 99 for spouses.[/COLOR]
    EMPLOYEE_NUMBER,  [COLOR=#ff0000]--> this is same as spouse number[/COLOR]
    SPOUSE_NUMBER,    [COLOR=#ff0000]--> this is same as employee number[/COLOR]
    INCENTIVE_AMOUNT   [COLOR=#ff0000]--> money they receive for participating in activity.[/COLOR]
    FROM tb_main_db
    ),

    rpt_emp AS
    (SELECT
    COUNT(e.CLIENT_NUMBER) emp_cl,
    COUNT(DISTINCT e.CLIENT_NUMBER) dist_emp_cl,
    SUM(e.INCENTIVE_AMOUNT) emp_amt
    FROM rpt_main e
    WHERE PERSON_ID = 11
    ),

    tb_emp_group AS
    (SELECT
    COUNT(DISTINCT e.ACTIVITY_TYPE) emp_act_type_group,
    COUNT(e.CLIENT_NUMBER) emp_group,
    SUM(e.INCENTIVE_AMOUNT) emp_inc_group
    FROM rpt_main e
    WHERE PERSON_ID = 11
    GROUP BY CLIENT_NUMBER
    ),

    tb_spouse_group AS
    (SELECT
    COUNT(s.CLIENT_NUMBER) sal_cl,
    COUNT(DISTINCT s.CLIENT_NUMBER) dist_sal_cl,
    SUM(s.INCENTIVE_AMOUNT) sal_amt
    FROM rpt_main s
    WHERE MEMBER_TYPE_ID = 99
    ),

    tb_spouse_group AS
    (SELECT
    COUNT(DISTINCT s.INCENTIVE_ACTIVITY_TYPE_ID) sal_act_type_group,
    COUNT(s.CLIENT_NUMBER) sal_group,
    SUM(s.INCENTIVE_AMOUNT) sal_inc_group
    FROM rpt_main s
    WHERE MEMBER_TYPE_ID = 99
    GROUP BY CLIENT_NUMBER
    )

    SELECT r.*, g.*, s.*, sg.*
    FROM tb_emp r
    INNER JOIN tb_emp_group g ON (1=1)
    INNER JOIN tb_spouse s ON (1=1)
    INNER JOIN tb_spouse_group sg ON (1=1)    ->  IS giving Cartesian product
     
     

    Attached Files: