Group By on a text field

[sibrulotte]

Hi,

I have a interim table where I calculate the sum of market values and accruals. I'm currently grouping by valuation date and CLASS_CODE4, which is a sector code. I want to do the same but instead of putting the code, use the description of the code. It's a one to one for all my records, and I have absolute certitude of this.

Using the code field works:

load
VAL_RPT.CLASS_CODE4&'-'&date(VAL_RPT.VAL_DATE,'YYYY-MM-DD') as %CLE_SECT_DATE,
VAL_RPT.VAL_DATE as R_PIC_00200_CALCUL.VAL_DATE,
sum(VAL_RPT.MKT_VALUE)  + sum(VAL_RPT.ACCRUAL_AMMOUNT) as  R_PIC_00200_CALCUL,
VAL_RPT.CLASS_CODE4 as R_PIC_00200_CALCUL.CLASS_CODE4
resident VAL_RPT_R230A
where
Match (VAL_RPT.PORT_NO, 'P0000000421')
group by VAL_RPT.VAL_DATE,VAL_RPT.CLASS_CODE4 ;

But using the description field gives an invalid expression:

load
VAL_RPT.CLASS_CODE4&'-'&date(VAL_RPT.VAL_DATE,'YYYY-MM-DD') as %CLE_SECT_DATE,
VAL_RPT.VAL_DATE as R_PIC_00200_CALCUL.VAL_DATE,
sum(VAL_RPT.MKT_VALUE)  + sum(VAL_RPT.ACCRUAL_AMMOUNT) as  R_PIC_00200_CALCUL,
VAL_RPT.CLASS_DESCRIPTION4 as R_PIC_00200_CALCUL.CLASS_DESCRIPTION4
resident VAL_RPT_R230A
where
Match (VAL_RPT.PORT_NO, 'P0000000421')
group by VAL_RPT.VAL_DATE,VAL_RPT.CLASS_DESCRIPTION4 ;

The field exists in my resident table if you're wondering...

The only difference between bothis that one is numeric the of text. Does that change anything?

[fernando_tonial]

Hi Simon, you need put all fields without agregation on Group By.

Try this.

load
VAL_RPT.CLASS_CODE4&'-'&date(VAL_RPT.VAL_DATE,'YYYY-MM-DD') as %CLE_SECT_DATE,
VAL_RPT.VAL_DATE as R_PIC_00200_CALCUL.VAL_DATE,
sum(VAL_RPT.MKT_VALUE)  + sum(VAL_RPT.ACCRUAL_AMMOUNT) as  R_PIC_00200_CALCUL,
VAL_RPT.CLASS_DESCRIPTION4 as R_PIC_00200_CALCUL.CLASS_DESCRIPTION4
resident VAL_RPT_R230A
where
Match (VAL_RPT.PORT_NO, 'P0000000421')
group by VAL_RPT.VAL_DATE,VAL_RPT.CLASS_CODE4,VAL_RPT.CLASS_DESCRIPTION4 ;

Best regards.

Tonial.

[sibrulotte]

Good job!!

Can't believe I skipped that.

Thanks,

I want to burry this thread now though, haha

[Anonymous]

haha...i know what you mean....but we have all been there before. this gets me from time to time lol