Hi Sergey
Thanks for your help, the following is the final creation from your suggestion and I have put it in the Stored Procedures for view to access it, but there is a problem leter on:
FUNCTION clubtestnames
LPARAMETERS tiPID, tiType
LOCAL laList[1], lcStr, liI
lcStr = ""
SELECT mTests.cTest ;
FROM sPatients, mTests, mTestTypes ;
WHERE sPatients.iPID = tiPID ;
AND mTests.iID = sPatients.iTest ;
AND mTestTypes.iID = mTests.iType ;
AND mTestTypes.iID = tiType ;
ORDER BY mTestTypes.cType, sPatients.iPID, sPatients.iSrNo ;
INTO ARRAY laList
FOR liI = 1 TO _TALLY
lcStr = lcStr + ALLTRIM(laList[liI]) + " "
ENDFOR
RETURN PADR(lcStr, 50)
ENDFUNC
The problem is that though the view works perfectly in VFP. The same view cannot be opened in Report Manager using ODBC/ADO.
I guess the UDF call creates the problem message: "LV_LKSPATIENTS1:[Microsoft][ODBC Visual FoxPro Driver]Function argument, value, type or count is invalid".
As soon as I rename this new view using the above function call to something else and the original view is renamed back to normal this same report starts working well.
Please advise if there is a work around. Is there a way the ODBC driver send the UDF call back to VFP for interpretation.
This UDF is in the Stored Procedures of the VFP DBC and is available at all times the DBC is open in VFP then why not for ODBC calls?
>>Hi all
>>
>>This is a typicaly requirement that I have. I have a table having values as below:
>>
>>HB Haematology
>>TC Haematology
>>URM UrineAnalysis
>>DC Haematology
>>
>>
>>I can group the above like follows:
>>
SELECT * FROM ... GROUP BY 2
>>
>>but I want the following result in the result:
>>
>>HB TC DC Haematology
>>URM UrineAnalysis
>>
>>
>>Is this possible to display in a view. This view will then be used externally by Report Manager for printing purposes. Thus I have to already have formatted the data beforehand as Report Manager won't be able to call VFP UDFs.
>>
>>Please help.
>
>I don't think you can do this w/o UDF's.
>SELECT Test0.ln, combinesn(ln) AS snlist;
> FROM ;
> test0;
> GROUP BY Test0.ln
>
>...
>FUNCTION combinesn
>LPARAMETERS lcLn
>LOCAL laList[1], lcStr, i
>SELECT sn FROM test0 ;
> WHERE ln = lcLn ;
> INTO ARRAY laList
>
>lcStr = ""
>FOR i=1 TO _TALLY
> lcStr = lcStr + " " + laList[i]
>ENDFOR
>RETURN PADR(lcStr,32)
>