I working on a completely new setup of Cognos 10 and encounter some issues when I want to create the metadata layer.
What I would like to do is start pretty basic and create something similar as this query:
select pst_pst_id, id_receiver, count(tra_id) cnt
select tra.tra_id, tra.pst_pst_id, tra.nop_nop_id, tra.bedrag, pst.rtl_rtl_id, pst.id_receiver
from hand_trans.tra, hand_trans.tst, hand_trans.pst
where pst.pst_id = tra.pst_pst_id
and tra.tra_id = tst.tra_tra_id
group by tra.tra_id, tra.pst_pst_id, tra.nop_nop_id, tra.bedrag, pst.rtl_rtl_id, pst.id_receiver
having max(status) = 21
group by pst_pst_id, id_receiver
As you can see i’m only using three tables so to import those is easy.
I did also manage to get the having max(status) = 21 in place using a filter on the tst table.
My problem lays in using the group by and sub query in a correct way.
If have been stuck for a few days now and start to wonder if I should be solving these ‘issues’ in framework manager
or on a higher level.
I would really appreciate it if someone could help me out, so I can continue building the metadata layer