Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 12th, 2007, 04:48 AM
Registered User
 
Join Date: Sep 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Multi-use of UDF in SELECT statement

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


 
Old September 12th, 2007, 05:36 AM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 553
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to vinod_yadav1919 Send a message via Yahoo to vinod_yadav1919
Default

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
 
Old September 12th, 2007, 06:13 AM
Registered User
 
Join Date: Sep 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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" ?

 
Old September 12th, 2007, 07:12 AM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 553
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to vinod_yadav1919 Send a message via Yahoo to vinod_yadav1919
Default

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
 
Old September 12th, 2007, 08:15 AM
Registered User
 
Join Date: Sep 2007
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Thanks a lot vinod :-)

 
Old September 28th, 2007, 01:23 AM
Authorized User
 
Join Date: Feb 2005
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 1st, 2007, 02:47 AM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 553
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to vinod_yadav1919 Send a message via Yahoo to vinod_yadav1919
Default

Hii Pooja!!
 Great solution
Cheers

Cheers :)

vinod
 
Old October 3rd, 2007, 06:01 AM
Friend of Wrox
 
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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








Similar Threads
Thread Thread Starter Forum Replies Last Post
multi select open box dialog yourfriendahsan Visual Basic 2005 Basics 1 July 19th, 2007 02:49 PM
Select from another select statement to a repeater simsen ASP.NET 2.0 Professional 0 May 2nd, 2007 04:34 PM
Multi-Select Parameters jparkgb BOOK: Professional SQL Server 2005 Reporting Services ISBN: 0-7645-8497-9 0 August 14th, 2006 06:02 AM
Multi-Select List Boxes and Calculations flrzeus BOOK: Expert One-on-One Access Application Development 0 December 5th, 2005 10:43 AM
Multi Select Combo Box acdsky VB How-To 2 March 1st, 2004 03:09 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.