Try to pad to 3 characters in the select.
>I'm having two problems with SQL and UDF's.
>
>The first involves grouping. This code gives bad results:
>SELECT ;
> <b>PADR(</b>ArrChannel(FUND_TYPE, I_APPL_SYS, PPL, C_LN_TYP, C_PD_TYP, M_BASE_LN, 'SUBCHANNEL')<b>,3)</b> AS SUBCH, ;
> COUNT(*) AS CT ;
> FROM ;
> Q13MTD ;
> GROUP BY 1 ;
> INTO TABLE TEST1
>
>And this succeeds:
>
>SELECT ;
> ArrChannel(FUND_TYPE, I_APPL_SYS, PPL, C_LN_TYP, C_PD_TYP, M_BASE_LN, 'SUBCHANNEL') AS SUBCH ;
> FROM ;
> Q13MTD ;
> INTO TABLE TMP
>
>SELECT SUBCH, COUNT(*) FROM TMP GROUP BY 1 INTO TABLE TEST2
>The only difference is the that the UDF and grouping are done in the same SQL in the first instance, and broken out into two SQL statements in the second.
>
>The code fails by grouping incorrectly, returning wrong or truncated subchannels.
>
>Which brings me to problem number 2. The UDF compares the passed values to an array. It doesnt open any tables. Then it returns a TRIMmed string of one, two, or three characters. This seems to confuse foxpro. It only allocates two characters to the field.
>
>I changed the UDF to always return three characters without trimming. This was net entirely helpful.
>
>I'm running VFP6 on NT 4 workstation, SP6a.
>
>TIA
>
>-hans
--sb--