>>>>>>I could use some help thinking through how to create a fairly complex report. There are many tanles involved but it basically boild down to a budget and expense report for a collection of projects. Each project has an ID that looks like this. RTA-12, RTA-23, RTA-22a, RTA-22b, etc. The RTA- prefix is uniform with all the projects. The numeric portion of the ID represents a group that the project belongs to. Group 12, Group 22, etc. The parent table in this case identifys the project and there are child tables for the budget and expenses. The budget table is a one to one, and the expense table is a one to many. The report needs to produce a single line that summarizes the total budget and total expenses like this:
>>>>>>
>>>>>>Group Budget Expenses
>>>>>>1 400 250
>>>>>>22 800 650
>>>>>>
>>>>>>I have been able to deal with the expense portion by creating a cursor with a record for each expense and grouping the report on the Project Group. Then using the group footer to subtotal the expenses. But I am not at all clear on how to add the budget information.
>>>>>>
>>>>>>Any thoughts would be appreciated.
>>>>>
>>>>>If you are "driving" the report from the Expense cursor, you could set relations to the budget table/cursor based on Group. Then in the Group Footer you can reference BudgetTable.BudgetField.
>>>>>
>>>>>Another option is to build one cursor that joins the data and just print the data in the detail band.
>>>>>
>>>>>

>>>>>SELECT Group.GroupID, NVL(Budget.BudAmt, 0) AS BudAmt, NVL(ExpSumData.ExpAmt, 0) AS ExpAmt ;
>>>>>  FROM Group ;
>>>>>    LEFT JOIN Budget ON Budget.GroupID = Group.GroupID ;
>>>>>    LEFT JOIN (SELECT GroupID, SUM(ExpAmt) AS ExpAmt FROM Expenses GROUP BY GroupID) ExpSumData ;
>>>>>        ON ExpSumData.GroupID = Group.GroupID ;
>>>>>   ORDER BY GroupID
>>>>>

>>>>
>>>>Hi Cathy,
>>>>
>>>>Just in case I would add CAST(NVL(Budget.BudAmt, 0) as Numeric(9,2)) as BudAmt, etc.
>>>
>>>Good point ... I do this in my apps but I forgot about it when writing up this off-the-cuff example.
>>
>>Thanks Cathy and Naomi. My understanding of SQL select continues to grow. As an aside, I understand from another thread that it is unwise to use a UDF in a select. In this example the only way to assemble the budget is with a UDF because it is spread over 60+ fields, each of which must be tested to make sure it meets certain criteria before it is added to the total. (This is the result of poor table design that originated 15+ years ago.) Nevertheless I have a function that iterates the fields and sums up the budget. So, given this, would it be best to create an empty field with the select and then run the function afterward? That's the only way I can think of except using the function directly in the SELECT but I was advised against this.
>
>My answer is that "it depends". If the SQL is something that only grabs a few records here and there, the performance hit for using a UDF won't be that noticable an I would go ahead and do it. On the other hand, if you are grabbing thousands of records, there could be a significant hit. In that case, filling the SQL with an empty field, and then using a SCAN loop to go through the cursor and apply the UDF to the field is the way to go. When in doubt .. do a timing test with doing it both ways and see what the difference is.

Your response seems to imply that using the UDF inside the SELECT would be OK. Others have advised against this for reliability reasons. Or do I misunderstand? In practice it has always seemed to work OK. So I don't know what the issues are.