I'm having two problems with SQL and UDF's.

The first involves grouping. This code gives bad results:

SELECT ;
ArrChannel(FUND_TYPE, I_APPL_SYS, PPL, C_LN_TYP, C_PD_TYP, M_BASE_LN, 'SUBCHANNEL') 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