Need help in complex sql query

Question

Say you have 2 columns you are grouping by and getting a count:

select col1, col2, count(*) from yourtable group by col1, col2

Your col2 will be unique, but you will get repeated col1's. To avoid this, you can:

select decode(lag(col1,1,0) over (order by col1),col1,' ',col1) col1, col2, cnt from (select col1, col2, count(*) cnt from yourtable group by col1, col2)

This will compare the prior value -- lag(col1,1,0) over (order by col1) -- with the current value. If it is the same, it returns a blank instead.

---

My question

I am trying to write a SELECT as below to display data in the given format at the end of the question. Its not working,please help

SELECT DECODE (LAG (firstname, 1, 0) OVER (ORDER BY firstname),
               firstname, ' ',
               firstname
              ) firstname,
       DECODE (LAG (emplid, 1, 0) OVER (ORDER BY emplid),
               emplid, ' ',
               emplid
              ) emplid,
       DECODE (LAG (tplan_name, 1, 0) OVER (ORDER BY tplan_name),
               tplan_name, ' ',
               tplan_name
              ) tplan_name,
    completed_cdt_act,             ----->This is 1 more calculated field corresponding to each TPLAN_NAME

       DECODE (LAG (t_objective_id, 1, 0) OVER (ORDER BY t_objective_id),
               t_objective_id, ' ',
               t_objective_id
              ) t_objective_id,
       completed_activities
  FROM (SELECT test_cp.firstname, test_op.emplid,tp.tplan_name,
            fn_tp_get_cmpltd_act_cnt (tp.tplan_id,
                                 test_cp.person_id,
                                 test_o.org_id
                                ) completed_cdt_act, 
               test_tpo.t_objective_id,
               (  fn_tp_obj_comp_req_act_cdt (test_lp.lp_person_id,
                                              tp.tplan_id,
                                              test_tpo.t_objective_id,
                                              tp.is_credit_based
                                             )
                + fn_tp_obj_comp_opt_act_cdt (test_lp.lp_person_id,
                                              tp.tplan_id,
                                              test_tpo.t_objective_id,
                                              tp.is_credit_based
                                             )
               ) completed_activities,
                tpobjact.activity_id activity_id,  -----> Again ,each objective_id we select CAN have multiple activity_ids.The remaining fields gives the activity_name,their status

            lr.catalog_item_name,
       fn_tp_get_act_type (tpobjact.is_preferred,
                           tpobjact.is_required) status,
       lr.catalog_item_type activity_type, lr.delivery_method,  lr.status status1,
          FROM test_learning_plan test_lp,
               test_training_plan tp,
               test_person test_cp,
               test_org_person test_op,
               test_org test_o
               test_tp_objective test_tpo,
               test_train_obj_activity tpobjact,

       test_learning_record lr,
       test_training_objective obj,
         WHERE test_lp.lp_person_id = '1'
           AND test_cp.person_id = test_lp.lp_person_id
           AND tp.tplan_id = test_lp.lp_catalog_hist_id
           AND test_op.o_person_id = test_cp.person_id
           and test_o.org_id = test_op.O_ORG_ID
           AND test_tpo.tplan_id = tp.tplan_id
           and  lr.tp_ref_lp_id = test_lp.learning_plan_id
   AND lr.lr_catalog_history_id = tpobjact.activity_id


   AND tpobjact.t_objective_id = test_tpo.t_objective_id);





******************************************************************************************************
The display format is something like this.

firstname   emplid Tplan name completed(for TP) Objective Name Credits (for objective) Number of activities completed(for objective) activity                                                                                                                                                                                                                                         name activity completion status

U1  U1                TP1                                      5     
                                                                 OBJ1                    4                       3                                    C1       PASSED
                                                                                                                                             C2     PASSED
                                                                                                                                             C3 WAIVED
          T1 ENROLLED
          T2 ENROLLED
                                                                        OBJ2 3            2  
                                                                       S1 ENROLLED
          S2 PASSED
          T3 WAIVED
U1  U1 TP2     C4 INPROGRESS
      50 OBJ11 50 30 C11 PASSED
          C22 PASSED
          C33 WAIVED
          T11 ENROLLED
          T22 ENROLLED
       OBJ22 40 20  
          S11 ENROLLED
          S22 PASSED

Answer 1

I guess the easiest way to approach this is to simplify things.

So start like this.

1) What do you want to display - i.e. what is the list of

If you want a list of employees with "some stuff" against each employee, then write a script that just gets the list of employees.

2) Add a bit of complexity

If you need to know the "number of something" an employee has done, add that in. You have the option to a) Join the tables and use grouping b) performing a sub-select statement to just get the value. Your choice will depend on performance considerations.

3) Got that working? Add a bit more complexity - what's the next thing you need for each employee that you need to obtain... (i.e. iterate around to add each complex item, knowing that it works at each stage).