>>>>>>>>>>>>>>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?
>>>>>>>>
>>>>>>>>Thanks,
>>>>>>>>
>>>>>>>>-Michelle
>>>>>>>
>>>>>>>Firstly, note that you have to provide return value of the same length for all cases. It cannot give an error, but can give you funny looking results. The better way when it's going to high-volume data (you can still be fine for thousands of record: it's not hogh volume) is to move 'abbreviation' code to front-end (e.g. to customized grid.column.controlsource).
>>>>>>
>>>>>>
>>>>>>I forgot about keeping it the same length. I'll have to add that. This SQL is going into a report and the status is the group name. How would I put that into a report?
>>>>>>
>>>>>>Thanks,
>>>>>>
>>>>>>-Michelle
>>>>>
>>>>>You can fire UDF from report field expression, e.g. you can type oApp.GetFullStatusDescription(mycursor.cstatus) there.
>>>>
>>>>I didn't realize I could do that. I'll put it there, then.
>>>>
>>>>Thanks,
>>>>
>>>>-Michelle
>>>
>>>Ok. BTW, Ed gave you right answer (very good catch). You never know what fires first in Select-SQL.
>>
>>And you know, it worked, too. I don't understand it. The IIIF() _always_ gets one of the things in the CASE, so why would it need the otherwise? I don't get it? My apologies to the other Ed for being a doubting Thomas. :)
>>
>>In all this wrestling, I've missed the obvious solution. I went back to change it to not using the IIIF() and I suddenly wondered why I'm not just joining it to the statuses table. That's what I do when I have a numeric key. I don't see any reason why I can't do it with an alpha key. I'm going to try that.
>>
>>This wasn't a waste of time, though. There's other places where the abbreviations aren't in a table, so I would have to know how to do this eventually anyway. :)
>>
>>Thanks,
>>
>>-Michelle
>
>The reason of SQL-error, you received, was probably following. Actually, 'Rushmorized' Select-SQL grabs according some WHERE criteria bunch of records from source tables and move them to new location (in a common NOFILTER case). So, the first thing VFP is inclined to do is to prepare this new location, i.e. parse fields with datatypes. It might be assumed, that it takes one sample record form source table(s), applies simplified SQL syntax (field list only) and build output data structure, and if this 'sample' returns 'Logical' then you really get an error.


Ah, so I can't assume that the UDF will only be applied to records that match the WHERE criteria? That would explain it...

Thanks,

-Michelle