Hello everybody,

I need to use an UDF (Who returns a scalar value) a few times in the same SELECT query. The easiest solution is to call every time my function, but it's not a performant way... I would like to do something like this :

SELECT MyFunction(...) AS MyResult, MyResult*7.6/100, MyResult + (MyResult*7.6/100)
WHERE MyResult > 0

And not like this :

SELECT MyFunction(...), MyFunction(...), *7.6/100, MyFunction(...), + (MyFunction(...), *7.6/100)
WHERE MyFunction(...), > 0


It's not working, because I have Invalid Column Name error... Any idea?

Thanks a lot for your answers

Hii Monnyann!!
Q1==>SELECT MyFunction(...) AS MyResult, MyResult*7.6/100, MyResult + (MyResult*7.6/100)
WHERE MyResult > 0

>>you must put Myfunction(..) with every column
Soln-1-->
SELECT MyFunction(...) AS MyResult,MyFunction(...)*7.6/100, MyFunction(...) + (MyFunction(...)*7.6/100)
WHERE MyFunction(...) > 0


Q1==>SELECT MyFunction(...), MyFunction(...), *7.6/100, MyFunction(...), + (MyFunction(...), *7.6/100)
WHERE MyFunction(...), > 0


>> not a valid sql plz check ,*7.6/100 and also other,+ so incorrct format is there
soln-plz see soln1


I do think following is the best optimal solution..
select tblA.MyResult, tblA.MyResult*7.6/100, tblA.MyResult + (tblA.MyResult*7.6/100)
from (SELECT MyFunction(...) AS MyResult) As tblA
WHERE tblA.MyResult > 0

If you have any other soln ,plz let us know
Hope this will help you





Cheers :)

vinod

Hello vinod

Yeah, I'm sorry I wrote too fast and added some "," in strange places... :-) So, you should read :

And not like this :

SELECT MyFunction(...), MyFunction(...) *7.6/100, MyFunction(...) + (MyFunction(...) *7.6/100)
WHERE MyFunction(...) > 0

But I realize my question wasn't enough precise... So this is the real SELECT I currently have :

SELECT e.FirstName,
dbo.GetProfitsByProjectAndHourType(@StartDate, @EndDate, e.EmployeeID, p.ProjectID, 1) AS Profit,
dbo.GetProfitsByProjectAndHourType(@StartDate, @EndDate, e.EmployeeID, p.ProjectID, 1) * 7.6/100 AS TVA,
dbo.GetProfitsByProjectAndHourType(@StartDate, @EndDate, e.EmployeeID, p.ProjectID, 1) + (dbo.GetProfitsByProjectAndHourType(@StartDate, @EndDate, e.EmployeeID, p.ProjectID, 1) * 7.6/100) AS Total
FROM Projects p, Employees e, EmployeeProjects ep
WHERE p.ProjectID = ep.ProjectID
AND e.EmployeeID = ep.EmployeeID
AND dbo.GetProfitsByProjectAndHourType(@StartDate, @EndDate, e.EmployeeID, p.ProjectID, 1) > 0


As you can see, this SELECT returns more than one row, but, for each row, my function "dbo.GetProfitsByProjectAndHourType" return the same value. How to have only one call to "dbo.GetProfitsByProjectAndHourType" ?

Hii monnyann!!

Hope this will help you

SELECT tmpTbl.FirstName,
tmpTbl.MyResult AS Profit,
(tmpTbl.MyResult * 7.6/100) AS TVA,
(tmpTbl.MyResult + (tmpTbl.MyResult * 7.6/100)) AS Total
from

(select e.FirstName, dbo.GetProfitsByProjectAndHourType(@StartDate, @EndDate, e.EmployeeID, p.ProjectID, 1) as AS MyResult FROM Projects p, Employees e, EmployeeProjects ep
WHERE p.ProjectID = ep.ProjectID
AND e.EmployeeID = ep.EmployeeID
 ) as tmpTbl

where tmpTbl.MyResult>0



Cheers :)

vinod

Yes, I guess I can use your method...

Thanks a lot vinod :-)

Here is another way to get the required out put with performance using CTE.

WITH cteResult(FirstName,Profit)
(
SELECT e.FirstName,
    dbo.GetProfitsByProjectAndHourType(@StartDate, @EndDate, e.EmployeeID, p.ProjectID, 1)
FROM EmployeeProjects ep
    INNER JOIN Projects p on p.ProjectID = ep.ProjectID
    INNER JOIN Employees e on e.EmployeeID = ep.EmployeeID
)
SELECT FirstName, Profit,
    (Profit * 7.6/100) AS TVA,
    (Profit + (Profit * 7.6/100)) AS Total
FROM cteResult
WHERE Profit>0

Cheers,
Pooja Falor

Hii Pooja!!
 Great solution
Cheers

Cheers :)

vinod

SELECT MyResult,
    MyResult * 0.076,
    MyResult * 1.076
FROM (
        SELECT MyFunction(...) AS MyResult
        FROM MYTable
    ) AS d
WHERE MyResult > 0