Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Group by with desc column only

Status
Not open for further replies.

cinquetti

IS-IT--Management
Sep 20, 2002
2
BR
Dear all,

I have a table that contains:

cd_razao_tc(PK)
cd_so_razao_tc
desc_razao_tc

In the fact table, I use the cd_razao_tc column to make join and in the attribute, I display cd_so_razao_tc and desc_razao_tc columns.

The Microstrategy is executing the query like below:

select a11.CD_RAZAO_TC CD_TC,
a14.NM_RAZAO_TC NM_TC,
a14.CD_SO_RAZAO_TC CD_SO_RAZAO_TC,
a12.CD_MES CD_MES,
a13.NM_MES NM_MES,
sum(a11.QT_ICG_CGB) QUANTIDADEIN
from dw.TBDWF_ANALISE_CGB a11,
dw.TBDWD_PERIODO_DIA a12,
dw.TBDWD_PERIODO_MES a13,
dw.TBDWR_RAZAO_TC a14
where a11.CD_DIA_INCOMING = a12.CD_DIA and
a12.CD_MES = a13.CD_MES and
a11.CD_RAZAO_TC = a14.CD_RAZAO_TC
and a12.CD_MES in (200108, 200110)
group by a11.CD_RAZAO_TC,
a14.NM_RAZAO_TC,
a14.CD_SO_RAZAO_TC,
a12.CD_MES,
a13.NM_MES

and the results appears:
Metrics Quantity
Month Oct 2001 Aug 2001
Razão TC
60 COPIA RECEBIDA ILEGIVEL 0 202
72 SEM AUTORIZACAO 0 202
77 CARTAO INEXISTENTE 4 101
88 CARTAO VENCIDO 3 0
88 CARTAO VENCIDO 102 0

Note that cd_razao_tc is unique, but when i show the cd_so_razao_tc, display two rows with the same id and the same description. It occurs because in the GROUP BY CLAUSE, the Microstrategy puts cd_razao_tc too.

Someone knows how can i set to Microstrategy puts only cd_so_razao_tc and desc_razao_tc in the group by clause ?

Without cd_razao_tc in the group by clause (and select clause) the results appears like this:

Metrics Quantity
Month Oct 2001 Aug 2001
Razão TC
60 COPIA RECEBIDA ILEGIVEL 0 202
72 SEM AUTORIZACAO 0 202
77 CARTAO INEXISTENTE 4 101
88 CARTAO VENCIDO 105 0


Thanks,
 
try using cd_razao_tc to be id of attribute A1,
cd_so_razao_tc and desc_razao_tc to be id and desc of attribute A2

make A2 parent of A1.

then add A2 on your report instead of A1. that should work.

good luck
 
Since cd_razao_tc is your PK then cd_so_razao_tc is a parent of this attribute. Have you defined that relationship?
See if that will fix your problem.
 
thanks for help.
The first solution works very well.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top