I have a User Defined Function: MyFunction(parm1).
Within MyFunction(), I have an SQL SELECT statement:
SELECT * FROM myTable WHERE field1=parm1.
If I call MyFunction() from within another SQL Statement such as:
SELECT MyFunction(mfield) FROM myOtherTable,
where field is a field name in myOtherTable,
then the 1st record passed to MyFunction is the 1st record in myOtherTable, whether or not this record is a deleted record. Often times, the 1st record in myOtherTable is a deleted record. Even though I had my SET DELETED set to disregard deleted records, it still pass the first (deleted) record in myOtherTable. Since the first field passed to MyFunction() is a field that is no longer valid, the SQL statement in MyFunction() does not return anything, and the SQL statement that calls MyFunction() returns only 1 char for the 1st record, subsequently all records returned by the calling SQL statement only returns 1 char.
I cannot do a PADR(xRet) in MyFunction() because the length of the return string is not constant, depending on the parameter passed.
Any ideas?
Thank you all.