> > Why would TRANSFORM(VAL(a1.Client_No)) return '1' given a1.Client_No = '0000197652', instead of the ALLTRIM(STR(VAL(a1.Client_No))) below which correctly returns '197652'?
> >
> > I had also written a UDF to just trim off the leading zeros, and it returned '1'. Actually, the UDF had '197652' when it headed back to the select statement, but '1' wound up in Field1.
> >
> > Can none of the vfp functions be trusted in SQL code?
> >
> > What am I missing?
> >
> > Dave
> >
> >
> > SELECT ;
> > ALLTRIM(STR(VAL(a1.Client_No))) as Field1, ;
> > cInvoiceNo as Field2, ;
> > CopDate(DATE()) as Field3, ;
> > ALLTRIM(a1.Sap_Code) as Field7 ;
> > FROM alsCompany a1 ;
> > WHERE a1.Co_Number = nCo_Number ;
> > INTO CURSOR csrH1
> >
>
> Better to use less functions to make your query much faster.
That depends. Not to use UDF is a definitely a good thing. I had one UDF because it was easier to code and to reuse. The hand-coded no-reuse query took 26 seconds. Converting the UDF into a formula - no matter the number of functions in the formula - the query then took 6.1 seconds. Using the technique I wrote about here:
http://my.advisor.com/doc/17440?open&p=1&pid=ztdbms the query took 6.3 seconds, even with all the code to create the snippet and macrosubstitution to execute the SQL command.
Mike Yearwood