TVF UDF does not return the same data as SELECT

Question

Calling the UDF like so:

SELECT
       product_name,
       SUM(quantity) AS SumQty,
       SUM(face_value) AS SumFaceValue,
       SUM(net_cost)AS SumNetCost,
       SUM(face_value - net_cost) AS SumScripRebate,
       organization_name
FROM getSalesSummary(@GLSCOrgId, @BeginDate, @EndDate) getSalesSummary
GROUP BY product_name, organization_name
ORDER BY product_name

yields:

   "Chili's      1    25.00   22.75  2.25   Sample Organization 1
    CVS/pharmacy 1  25.00   23.50  1.50   Sample Organization 1
    Macy's       1  100.00  90.00  10.00  Sample Organization 1"

Using the UDF logic and testing the results with SELECT:

SELECT 
       product_name,
       SUM(quantity) AS SumQty,
       SUM(face_value) AS SumFaceValue,
       SUM(net_cost) AS SumNetCost,
       SUM(face_value - net_cost) AS SumScripRebate,
       organization_name
FROM @ReturnTable
GROUP BY product_name, organization_name
ORDER BY product_name

yields:

   "Chili's       4   100.00  91.00   9.00   Sample Organization 1
    CVS/pharmacy  1   25.00   23.50   1.50   Sample Organization 1
    Macy's        1   100.00  90.00   10.00  Sample Organization 1"

@ReturnTable is the table returned by the UDF and is created like so:

INSERT INTO @ReturnTable(product_name,
                         unit_price,
                         quantity,
                         face_value,
                         net_cost,
                         organization_name)
(select * from @TablePartial UNION select * from @TableClosed)

The test with the SELECT and variables is returning the correct data, but calling the UDF is not getting those other 3 Chili's records. I am using the same data for parameters. I'm quite new to UDFs and I'm not sure why it would return different data than what the SELECT does. Any suggestions and/or answers?

Answer 1

You probably need UNION ALL not UNION

Looking at the two result sets it adds up as though the 4 Chilli's rows are all the same.

Chili's      1    25.00   22.75  2.25   Sample Organization 1
Chili's      1    25.00   22.75  2.25   Sample Organization 1
Chili's      1    25.00   22.75  2.25   Sample Organization 1
Chili's      1    25.00   22.75  2.25   Sample Organization 1
-------------------------------------------------------------
Chili's       4   100.00  91.00   9.00   Sample Organization 1

Using UNION will remove the duplicates leaving you with one row.

Answer 2

The only thing I can think of is the UNION change it to UNION ALL UNION will eliminate dups

Run these queries to see the difference

select 1 as a
union 
select 1
union 
select 1


select 1 as a
union  all
select 1
union all
select 1