3 Replies Latest reply: Dec 5, 2014 10:43 AM by Saradhi Balla RSS

    Group By on a text field

    Simon Brulotte

      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?