Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Titre:
SQL grouping by a field defined by a UDF
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
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement