locked
crystal is wrong result for one of the fields. RRS feed

  • Question

  • User-1352958039 posted

    I've written SQL query in command line, where I'm counting the amount ($) for employees and spouses. I ran the result on sql plus and got $270 for employees and $210 for spouses. However, when I ran the query in crystal, I got $280 for employees and $210 for spouses. I'm doing all the work in my query. In crystal, I'm just displaying the fields.

    Please advise me as to what am I doing wrong here. Thanks in advance!

    Below is my query.

    With tb_main as

    (

    SELECT CLIENT, AMOUNT, ACTIVITY_TYPE, PERSON_ID, EMP_SPOUSE_ID, company

    FROM MAIN

    ),

    tb_emp as

    (SELECT e.EMP_SPOUSE_ID, COUNT(e.CLIENT) OVER (PARTITION BY e.company) emp_cl, 

    SUM(e.AMOUNT) OVER (PARTITION BY e.company) emp_inc, 

    COUNT(e.CLIENT) OVER (PARTITION BY e.CLIENT) emp_group,

    SUM(e.AMOUNT) OVER (PARTITION BY e.CLIENT) emp_inc_group,

    COUNT(e.ACTIVITY_TYPE) OVER (PARTITION BY e.CLIENT) emp_act_type_group

    FROM tb_main e

    WHERE person_id = 11

    ),

    tb_spouse as

    (SELECT s.EMP_SPOUSE_ID, COUNT(s.CLIENT) OVER (PARTITION BY s.company) sp_cl, 

    SUM(s.AMOUNT) OVER (PARTITION BY s.company) sp_inc, 

    COUNT(s.CLIENT) OVER (PARTITION BY s.CLIENT) sp_group,

    SUM(s.AMOUNT) OVER (PARTITION BY s.CLIENT) sp_inc_group,

    COUNT(s.ACTIVITY_TYPE) OVER (PARTITION BY s.CLIENT) sp_act_type_group

    FROM tb_main s

    WHERE person_id = 99

    )

    SELECT e.emp_cl, e.emp_inc, e.emp_group, e.emp_inc_group, e.emp_act_type_group,

           s.sp_cl, s.sp_inc, s.sp_group, s.sp_inc_group, s.sp_act_type_group

    FROM tb_emp e

    LEFT OUTER JOIN tb_spouse s ON (e.emp_spouse_id= s.emp_spouse_id);

    Saturday, December 16, 2017 9:45 PM

All replies

  • User-129908252 posted

    Hi mh9876,

    Once debug the code what is returning in code behind for you query result.

    Please better to create the store procedure and return it.

    Thanks,

    Jagan

    Monday, December 25, 2017 10:51 AM