>>>>>>>>>>I have this SQL:
>>>>>>>>>>
>>>>>>>>>>

>>>>>>>>>>SELECT cname, tdatetime, ;
>>>>>>>>>>       IIF(EMPTY(cPickup), cPaddr, cPickup) AS cPickup, ;
>>>>>>>>>>       IIF(EMPTY(cDropoff), cDaddr, cDropoff) AS cDropoff, ;
>>>>>>>>>>       IIIF(cStatus, "CXL", "NR", "NS", "Canceled", "Not Ready", "No Show") AS cstatus ;
>>>>>>>>>>    FROM Trip_History ;
>>>>>>>>>>    INTO CURSOR vtrips ;
>>>>>>>>>>    WHERE (INLIST(cStatus, "CXL", "NR", "NS")) AND BETWEEN(TTOD(tdatetime), pdBegin, pdEnd) ;
>>>>>>>>>>    ORDER BY cStatus, tdatetime
>>>>>>>>>>

>>>>>>>>>>
>>>>>>>>>>"IIIF()" is a function I made that returns one of the last three options. VFP is giving me a "data type mismatch" error on the IIIF line. I've used UDFs in SQL before without a problem, and I think I might have used this IIIF() before. I don't understand why it's giving me an error. IIIF.prg is in the path, so I know it can find it. And the function works on its own.
>>>>>>>>>>
>>>>>>>>>>Any ideas?
>>>>>>>>>>
>>>>>>>>>>Thanks,
>>>>>>>>>>
>>>>>>>>>>-Michelle
>>>>>>>>>
>>>>>>>>>Add WAIT WINDOW cParm1 NOWAIT to IIIF code, run and bomb it again, and look what you have in wait window.
>>>>>>>>
>>>>>>>>It's "NR" which is what it should be.
>>>>>>>>
>>>>>>>>Is that good or bad? :)
>>>>>>>>
>>>>>>>>Thanks,
>>>>>>>>
>>>>>>>>-Michelle
>>>>>>>
>>>>>>>I don't know. If it bombs, so it's not very good. There are two places which can fire type mismatch: either function code itself (I hope you have very simple code inside) or SQL, when let say IIIF returns Integer (or maybe .NULL. ?) for the first record and tries to return Character for the next one.
>>>>>>
>>>>>>IIIF() is a simple case statement, and it works if I run it by itself. As far as I know, it's returning a character. The wait window only showed up once, so I'm guessing it's bombing on the first one. I don't know what it doesn't like...
>>>>>>
>>>>>>Thanks,
>>>>>>
>>>>>>-Michelle
>>>>>
>>>>>Just a curiosity: could you post the function code? Also, how big the volume of data you process. Basically, UDF in SQL is not the best way to proceed: you significantly delay query speed.
>>>>
>>>>
>>>>Sure...
>>>>
>>>>

>>>>LPARAMETERS lcToMatch, lcChoice1, lcChoice2, lcChoice3, lcAnswer1, lcAnswer2, lcAnswer3
>>>>
>>>>DO CASE
>>>>   CASE lcToMatch = lcChoice1
>>>>      RETURN lcAnswer1
>>>>   CASE lcToMatch = lcChoice2
>>>>      RETURN lcAnswer2
>>>>   CASE lcToMatch = lcChoice3
>>>>      RETURN lcAnswer3
>>>>ENDCASE
>>>>

>>>>
>>>>Right now the table is small, but it has the potential to have thousands of records. Do you know a better way of replacing the abbreviations with the full words?
>>>>
>>>
>>>The problem is in your CASE statement - what gets returned if none of the conditions are met? Answer - a LOGICAL, not a string. Add an OTHERWISE clause to your CASE statement to return a consistent value, or a RETURN after the ENDCASE. Something like:
>>>
>>>

>>>DO CASE
>>>CASE lcToMatch = lcChoice1
>>>   RETURN lcAnswer1
>>>CASE lcToMatch = lcChoice2
>>>   RETURN lcAnswer2
>>>CASE lcToMatch = lcChoice3
>>>   RETURN lcAnswer3
>>>OTHERWISE
>>>   RETURN 'None of the Above'
>>>ENDCASE
>>>

>>>
>>>There are other problems, like what happens if non-character values are passed, or parameters are omitted. In the long run, rethinking the SQL SELECT to perhaps use a translation table would make the SQL statement more portable and easier to extend.
>>
>>
>>The "WHERE" clause makes sure that the values are the same ones in the IIIF(). So it would never not match or be a non-character value. And I don't see how parameters could be omitted since they're hard-coded in. I can try putting an otherwise in and see if that helps, but I don't see how it will.
>
>Is there possibly a memory variable with the name 'cStatus'? Without an explicit alias reference, it could easily be getting confused in the UDF call.
>
>BTW, using a translation table would simplify this considerably, since an inner join on the translation table would filter out any non-matching records AND provide the long strings (fixed length!) at the same time.
>
>>
>>Thanks,
>>
>>-Michelle


No memory variable. The other Ed came up with what was probably the problem. (See his post). I already had a table of stat_codes. It was just one of things where the brain completely misses the obvious... :)

Thanks,

-Michelle