I am creating a query that will be used for a cross-tab report. As you can see, in the first field I am putting several items into one string, later in VFP for my report I use the strextract to pull out the items I need to print. I need to include the unit cost of the inventory item in that that same string, but it is not a string type. The Total cost is stored in the coInv.CostBeforeCF field (Decimal 9,2). The CF field is an int. The unit cost is calculated by CostBeforeCf/CF. This seemed like a good place for the SQL UDF. But I got myself all confused reading the BOL.

Here is the current Code:

lcCmd=[SELECT  '|'+RTRIM(dbo.coPrefGrp.prefGroupDesc)+'|'+RTRIM(dbo.coinv.primedesc)+'|'+RTRIM(dbo.coinv.catalogno)+'|',rtrim(dbo.coper.lastname)+', '+substring(dbo.coper.firstname,1,1) as docname,  avg(dbo.cocasedata.actualqty) as avgQty, ]
lcCmd=lcCmd+[ dbo.coinv.catalogno, ]
lcCmd=lcCmd+[ dbo.coInv.CostBeforeCF, dbo.coInv.CF ]
lcCmd=lcCmd+[FROM    dbo.meetings INNER JOIN ]
lcCmd=lcCmd+[           dbo.meetproc ON dbo.meetings.meetingnumber = dbo.meetproc.meetingnumber INNER JOIN ]
lcCmd=lcCmd+[           dbo.coper ON dbo.meetproc.coperid = dbo.coper.coperid INNER JOIN ]
lcCmd=lcCmd+[           dbo.cocasedata ON dbo.meetings.meetingnumber = dbo.cocasedata.meetingnumber INNER JOIN ]
lcCmd=lcCmd+[           dbo.coinv ON dbo.cocasedata.linkedid = dbo.coinv.invid  INNER JOIN ]
lcCmd=lcCMd+[           dbo.coprefgrp ON dbo.coinv.prefgroupid = dbo.coprefgrp.prefgroupid ]
lcCmd=lcCmd+[WHERE     (dbo.meetings.iscompleted = 1)  ]
lcCmd=lcCmd+[and      dbo.meetproc.procid=?lnProcID ]
lcCmd=lcCmd+[and      dbo.meetings.begintime between ?ltQueryFrom and ?ltQueryTo ]
lcCmd=lcCmd+[AND meetings.deptid=?lnDeptID ]
lcCmd=lcCmd+[group by lastname,firstname,PrefGroupDesc,primedesc,catalogno,CostBeforeCf,CF ]
lcCmd=lcCmd+[order by PrefGroupDesc,primedesc,docname]

I was thinking if I could figure out how to do the UDF for the unit cost, the first line could be something like:

SELECT  '|'+RTRIM(dbo.coPrefGrp.prefGroupDesc)+'|'+RTRIM(dbo.coinv.primedesc)+'|'
           +RTRIM(dbo.coinv.catalogno)+'|'+udItemCost(dbo.coinv.invid)+'|'

Thanks for any help