Calling a user-defined function?

In the below statement where I am calling user-defined function
dbo.udf_calcbill_hotel, is that a legal select statement?  I keep
getting syntax errors and I can't figure out what's wrong.  I'm
attempting to call the function by passing in data from the other table
that the function table is joined to.

If this is wrong, is there another way to do this?  Sorry I can't post
all the table data.



DECLARE @initfee money, @fee money, @billbase int, @folup tinyint
SET @folup = NULL

CREATE TABLE #bill_bldgs
	(
	BldgSec_ID int,
	BldgSec_Units smallint NULL,
	FolUp tinyint NULL
	)

	-- get a table of the billable buildings
	INSERT #bill_bldgs
	SELECT BldgSec_ID, BldgSec_Units, @folup AS FolUp
	FROM dbo.tblBldg_Sec
	WHERE Prop_ID = 2721 AND BldgSec_SectionOfID IS NULL AND BldgSec_Active
= 1 AND dbo.udf_Billable(BldgSec_ID) = 1


SELECT @initfee = MAX(cbh.Flat_Fee), @fee = SUM(cbh.ByRate_Fee),
@billbase = COUNT(*)
FROM #bill_bldgs bb INNER JOIN 
dbo.udf_calcbill_hotel(bb.BldgSec_ID, bb.BldgSec_Units, bb.FolUp) cbh 
ON bb.BldgSec_ID = cbh.BldgSec_ID

DROP TABLE #bill_bldgs

SELECT @initfee AS InitFee, @fee AS ByRateFee


Mia J.

*** Sent via Developersdex http://www.developersdex.com ***
0
Mij
6/11/2010 1:47:24 PM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
643 Views

Similar Articles

[PageSpeed] 10

More information is that I am trying to take a table of Buildings, i.e.

BldgSec_ID  |  BldgSec_Units  | FolUp
1              35                N
2              58                N
3              300               N

and somehow put each row into my function that calculates the billing
fees, so that I get a table like this:

BldgSec_ID  |  Flat_Fee  |  ByRate_Fee
1              435            210
2              435            406
3              435            2400
----------------------------------------
            Max               Sum

so that I can get a max(Flat_Fee) and a SUM(ByRate_Fee) from the table.

Mia J.

*** Sent via Developersdex http://www.developersdex.com ***
0
Mij
6/11/2010 3:57:26 PM
Mij (mdsj@infi.net) writes:
> In the below statement where I am calling user-defined function
> dbo.udf_calcbill_hotel, is that a legal select statement?  I keep
> getting syntax errors and I can't figure out what's wrong.  I'm
> attempting to call the function by passing in data from the other table
> that the function table is joined to.
> 
> If this is wrong, is there another way to do this?  Sorry I can't post
> all the table data.

If you are on SQL 2000, you will probably habe to run a cursor 
and run a query row by row.

On SQL 2005 and later you need to use the CROSS APPLY operator:
 
 SELECT @initfee = MAX(cbh.Flat_Fee), @fee = SUM(cbh.ByRate_Fee),
        @billbase = COUNT(*)
  FROM #bill_bldgs bb 
  CROSS APPLY dbo.udf_calcbill_hotel(bb.BldgSec_ID, 
                      bb.BldgSec_Units, bb.FolUp) cbh 

You should verify that this query gives the desired result.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
6/11/2010 10:20:25 PM
Reply:

Similar Artilces:

Testing a range of cells in an IF FUNCTION
Trying to test a range of cells in an IF Function. I would like the function to look at 15 cells in a single row, find a value within those cells, and then return another value if TRUE/FALSE. As of now, I can only apply the "logical test" in the IF formula for ONE CELL ONLY. I would like it to test a RANGE of cells in one row, find if any of the values match my logical test, and then return a value. Anyone understand or know how to do this? I tried apply "lookup" function, but I don't think it will work Please help Aaro aaronplange at hotmail.com Hi maybe someth...

defining accounts receivable default accounts
We would like the ability to have limitations on the accounts that can be selected for accounts receviable for a sales order transaction or receivable transaction. Currently you can select any account number for the RECEV trx type. We would like to define allowable accounts receivable accounts to avoid our current out of balance condition. This would limit the possibilities of reasons for ar trade to be out of balance to gl. This is a very hot issue at many clients in trying to reconcile ar to gl. This could also apply to AP!! Thanks, lori b. ---------------- This post is a sug...

Multiple user names at one bank or brokerage
I have 3 accounts at one bank. I have 3 corresponding accounts set up in MS Money 2007. One of them is setup for online banking while the other two are not. I now want to set the other two up for online banking but I can't find a way to specify the unique login info for each of the other two accounts. ...

instructions disppear when users begin type (text field)
Hi all, I need to customize the outlook contact form and I want to add one text field to allow users to add details info and instruct users how to add. Instructions shows in the field and the instructions disappear when users click and begin to type. How should I do this? exchange 2003/outlook2003 Thank you. It's hard for me to visualize exactly what you're expecting to happen. If you want the instructions to stay on the screen, you could display them in a label control. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumps...

Alert user to check the filed when it is final
Hi, I want to create an alert to check the Final( checkbox ) field, if date_issued is enter. if date_issued is null then do nothing, In what even I have to code this and how? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1 mls via AccessMonster.com wrote: >Hi, I want to create an alert to check the Final( checkbox ) field, if >date_issued is enter. >if date_issued is null then do nothing, In what even I have to code this and >how? Based on just that one requirement, you can use the date text b...

Re: Function to copy data from a variable range?
I have a sheet with about 20,000 lines of data. From that sheet I am looking to group the data based on a parameter, into multiple different sheets. ie: If the category is "1" copy all that lines data into sheet A, If the category is "2", copy all the lines into sheet B. The problem is, the number of lines for each acatgeory type is not always the same. Some categories may only have 1 or two lines, other may have 200-300. Is there any type of function for this? Thanks! Doable but why not just use data>filter>autofilter -- Don Guillett Microsoft MVP Excel SalesA...

viewing a user's email account
I have been charged with reviewing a user's email activity / mailbox for a possible impropriety. Is there a way to do this from exchange, or better yet what is the best way to accomplish this steathfuly? On Mon, 23 Oct 2006 18:20:02 -0700, Rossel1 <Rossel1@discussions.microsoft.com> wrote: >I have been charged with reviewing a user's email activity / mailbox for a >possible impropriety. Is there a way to do this from exchange, or better yet >what is the best way to accomplish this steathfuly? I really hope that the users' employment contract provides for this ...

eService Call with GP 9.0
eService GP 8 release was unchanged for GP 9.0 as per Article ". eService & eReturns NOTE: eService & eReturns for Microsoft Dynamics GP 9.0 utilizes the same download as Microsoft Great Plains 8.0. There are no changes to eService & eReturns requiring an upgrade or installation difference. " I tried to configure eService for GP 9.0 but not able to view the proper page, it seems the COM components “Webservice” not working properly. Anyone come across the same situation? You need to go to Component Services, Computers, My Computers, Com+ Apps and make sure th...

Should unimplmented pure function throw a compile error?
It's been a while since I have written anything with abstract classes. If I derive a class from my abstract class and do not implement a pure virtual function of the base class in the derived class, shouldn't the compiler throw an error? -- Bruce E. Stemplewski GarXface OCX and C++ Class Library for the Garmin GPS www.stempsoft.com yes, if you try to instantiate the class in your code it should. "Bruce E. Stemplewski " <BruceStemplewski@hotmail.com> wrote in message news:uaGJZhYCFHA.3732@TK2MSFTNGP14.phx.gbl... > It's been a while since I have written anyt...

function keys
Hello, Is there a site where I can get a recap of RMS function keys? Not that I know of, but F1 - Help is a really good place to start! Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.com ---------------------------------------------- Please DO NOT respond to me directly but post all responses here in the newsgroup so that all can share the information. newbie wrote: > Hello, > Is there a site where I can get a recap of RMS function keys? This is a multi-part message in MIME format. ------=_NextPart_000_02DF_01C6B6FC.B3481FA0 Content-Type: text/plain; ...

switch users without closing program
Hi, Is there a way to switch profiles without closing Outlook 2002? Thanks, Duane ...

reconnect to mailbox after Disabling user?
Hello, I was wondering if there is a easier way then to recycle the Exchange Services to renable a mailbox after a users mailbox has been disabled for x amount of time. I want to give access to this mailbox to another user so right now.. I simply go to Mailbox Rights -> (add the user I want to add) -> give allow rights to Read / Full but this requires a recycle of the services. Is there another way without recycling the services? G. On Mon, 23 Aug 2004 11:08:36 -0400, "Just Another Admin" <just@dmin.net> wrote: >Hello, > >I was wondering if there ...

Attach Trigger to Save Function of Form
I am in the processes of trying to attach a trigger to the Save function of an already existing GP form using Dex. In one of the documents, it states that the CM_Checkbook_Maitenance has a SaveRecord function. I am trying to acces the function that is similar to this on the PM_Vendor_Maitenance form. I tried using SaveRecord and when I tried to compile it it says that it is an unknown reference. Is there some documentation with a list of the functions for each form? Mike, PM_Vendor_Maintenance is an older form and probably doesn't have a SaveRecord function. Try running t...

How to let OWA users to change their password ?
How to let OWA users to change their password ? On Wed, 9 Nov 2005 01:58:03 -0800, "Enid" <Enid@discussions.microsoft.com> wrote: >How to let OWA users to change their password ? There are quite a few steps but if you follow this: http://support.microsoft.com/default.aspx?scid=kb;en-us;297121 carefuly, you'll be ok. Thanks for your information. Follow the document to enable the change password funcation, but got error when using IE6 w/ SP2 to do change password "error number : 5". It is so tricky if using FireFox, the password change is work. Any suggesti...

Cannot add user to CRM
I have trouble adding user to the CRM. I have 50 users in AD, however, when it comes to adding user in CRM, I can only choose from 15 of them. I wonder why!? I suppose I should be able to pick from all 50 users whoever is in the AD, right? If anyone have any clue on this problem and give me some feedback, I would surely appreciate that!!! CRM deployment manager will not display certain categories of users - eg if they have been migrated from NT domains or Novel NDS without passwords. Search the group archives for UserAccountControl "Martijn van Halen" <MartijnvanHalen@d...

Re-exporting functions from dynamically linked DLL
Hi, I have the following situation: I have a 3rd party DLL which does not include a LIB file, just a header file with typedef declarations for exported functions (It is intended for dynamic linking) I need to wrap the DLL to allow for callbacks to my own code (in Lisp), and to do this I need to load the DLL using LoadLibrary and access each function that I need using GetProcAddress. So for example: The third party header will say: typedef INT (WINAPI * lp_function)(); Then in my c wrapper file: #include "3rdparty.h" #ifdef _WIN32 #define DllExport __declspec(dllexport) #else #...

how can i use countif function inside a filtered range
How can I use countif function inside a filtered range Hi, Not much detail here, so this is the general idea: =SUMPRODUCT(SUBTOTAL(3,OFFSET(C13:C18,ROW(13:18)-13,0,1)),--(C13:C18="Red")) -- If this helps, please click the Yes button Cheers, Shane Devenshire "jayin" wrote: > How can I use countif function inside a filtered range Shane Devenshire wrote on 02/24/2009 02:05 ET : > Hi, > > Not much detail here, so this is the general idea: > > =SUMPRODUCT(SUBTOTAL(3,OFFSET(C13:C18,ROW(13:18)-13,0,1)),--(C13:C18=&quot;Red&quot;)) > > ...

User authentication/protection
I'm a professor and use Excel extensively in my classes. I need to be able to assign Excel projects to my students and have some increased assurance that the students have submitted their own work. I've tried my hand at VBA programming and also simply used track changes. None of these solutions (even the track changes) worked reliably across both mac and pc platforms. I would like some advice about whether what I need from Excel is even possible. I Here's what I need and what I think would work: 1. I'd like to prevent a student from getting someone else's sol...

RMS Integration user access
How can I give users in GP access to the RMS Import (Transactions > Retail Management > RM Import) function? I found KB 900636, but it did not work. I'm using GP10 and HQ & RMS 2.0. Thanks, Jocelyn ...

OWA Delegate to non-Exchange user?
Hi all, is it possible to delegate OWA access to a user account that doesn't use Exchange? If I try to set up a delegate from my Outlook 2003 client it requests a mail id rather that a login ID. I've tried giving the account permissions in AD U and C but test show that the pasword for the delegate isn't accepted when trying to log in to OWA. Could anyone advise please. Regards Graeme <graeme_smith77@hotmail.com> wrote in message news:1132923835.303433.156340@g43g2000cwa.googlegroups.com... > Hi all, > is it possible to delegate OWA access to a user account that ...

Outlook 2002/2003 auto-complete function
How do i clear out the "database" of the auto-complete function in the To,cc and bcc fields. i know how to do a single name clear by clicking delete is there a way to clear all the names out? To clear the entire cache, follow the instructions here: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q287623 -- Russ Valentine [MVP-Outlook] "Brian James" <bjames@beconet.com> wrote in message news:009101c3ad51$120b6a00$a501280a@phx.gbl... > How do i clear out the "database" of the auto-complete > function in the To,cc and bcc fields. i know how...

Creating an OWA access only user.
Hello chaps/ladies, The pointy hairs have asked that some one external to our organisation be given access to an email account within our organisation. So my thoughts were to create a user account and lock it down to prevent interactive access to any workstation machines in any way and only allow OWA access, this has prroven trickier than I first assumed. Does anyone have a quick guide going over best practices for this type of situation? TIA. -- Alex Griffin On Tue, 14 Jun 2005 16:26:11 +0100, Alex Griffin <nntpuser@hotmail.com> wrote: >Hello chaps/ladies, > >The pointy...

user shares vs. folder redirection
Just finished a brute force migration of SBS 2003 to 2008 and I am wondering if there is any reason why I would want to place the old user shares files into the new user shares folder. I should have prefaced by saying that the only reason they used the user shares in sbs 03 is because someone never setup redirection. Instead just told them to copy everything into their ..lnk folder. So I am thinking since these are not files that they cared to share with others anyway why can't I just copy them into their redirected folders? I would just put the files wherever the u...

granting users Send as permissions causes errors unless they select the FROM address from the GAL
I've got some users who have two mailboxes, each with a different address. They need to be able to select which address they send from, on occasion. I've given them "send on behalf" and "send as" permissions the 2nd accounts. But, unless they select the FROM address from the GAL, they get the following error: You do not have permission to send to this recipient. If they manually type in the address, they receive this error. This is only a problem because I would prefer to hide the 2nd account from the GAL, to avoid confusion. Does anyone know a way around this?...

Function if()
Hello! ***If I use If() =IF(N82:N84="N/A",0,SUM(O82:O84)/SUM(M82:M84)*S82) The formula box shows the results below: logical_test = {FALSE;TRUE;TRUE} value if true = 0 Value if false = 0.8 ={0.8;0;0} =0.8 But I have a problem!!! My cell shows = 0 (and this is not due to any kind of formatting) ***Now I have the same problem is the logical test is (as shown in th formula box) {TRUE;FALSE;FALSE} ={0;0;0.8} =0.8 My cell actually shows =0 ***However if my logical test is (as shown in the formula box) {TRUE;FALSE;TRUE} ={0;0.8;0} =0.8 Then my cell will finally shows the right num...