How to Use a Function to Contain a Query Value

Attached is a T-SQL query.  The fourth column in the query, you will notice, is called [CurrentTrxAmt].

I want to use the value of this column in several other "spots" further down in the query.  Specifically, I want the value of this column to replace the "RM20101.ORTRXAMT" value found in the following columns in the query:  [NotDue], [1-30], [31-60], [61-90], and [Over90].  

Someone told me that the only way to do this is to create a user-defined function (UDF).  I do not know how to do this, so that's why I'm here.

Now, I have been reading about these functions and I have the code that I composed attached.

So, the following are my questions:

(1) Is the syntax of my UDF correct?
(2) To replace RM20101.ORTRXAMT, can I simply replace it with "fnCurrenttTxAmt"?
(3) The '2011-03-31 00:00:00.000' date used throughout the query and the attached function will actually be "replaced" by a date-based variable that I have in a stored procedure that this query is a part of.  That variable is "@TargetDate".  I simply placed this hard-coded date in the attached query and function for reference and testing functions.  My question, now, is can I use such a variable in this function when I "go-live" with this function?

Thanks!  Very much appreciated!
Create function fnCurrentTrxAmt @RMDTYPAL SMALLINT, @DATE1 datetime, @ORTRXAMT numeric(19,5), @CURTRXAM numeric(19,5)
returns numeric(19,5)
as
 begin
declare @Amount numeric(19,5)
select @Amount = 0
CASE WHEN RM20101.RMDTYPAL = 1 and RM20201.DATE1 > '2011-03-31 00:00:00.000' THEN RM20101.ORTRXAMT
WHEN RM20101.RMDTYPAL = 2 and RM20201.DATE1 > '2011-03-31 00:00:00.000' THEN RM20101.ORTRXAMT * -1 
WHEN RM20101.RMDTYPAL = 3 and RM20201.DATE1 > '2011-03-31 00:00:00.000' THEN RM20101.ORTRXAMT
WHEN RM20101.RMDTYPAL = 4 and RM20201.DATE1 > '2011-03-31 00:00:00.000' THEN RM20101.ORTRXAMT 
WHEN RM20101.RMDTYPAL = 5 and RM20201.DATE1 > '2011-03-31 00:00:00.000' THEN RM20101.ORTRXAMT 
WHEN RM20101.RMDTYPAL = 6 and RM20201.DATE1 > '2011-03-31 00:00:00.000' THEN RM20101.ORTRXAMT
WHEN RM20101.RMDTYPAL = 7 and RM20201.DATE1 > '2011-03-31 00:00:00.000' THEN RM20101.ORTRXAMT * -1 
WHEN RM20101.RMDTYPAL = 8 and RM20201.DATE1 > '2011-03-31 00:00:00.000' THEN RM20101.ORTRXAMT * -1 
WHEN RM20101.RMDTYPAL = 9 and RM20201.DATE1 > '2011-03-31 00:00:00.000' THEN RM20101.ORTRXAMT * -1 
ELSE RM20101.CURTRXAM END as [CurrentTrxAmt]
return @Amount
end

Open in new window

AMA.txt
LVL 1
apitechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
you can use it as :


select dbo.fnCurrentTrxAmt (RMDTYPAL_Field, DATE1_Field, ORTRXAMT_Field, CURTRXAM_Field), * from tbl
0
Ephraim WangoyaCommented:

Your function will always return a zero, you have to assign the values to @Amount varaible
0
apitechAuthor Commented:
Hi:

Thank you, for the responses!

It's not your fault, but I'm afraid that I'm not understanding.  If I use what IJZ is saying, then is the query going to "understand" how to pull the amount from [CurrentTrxAmt}?

On assigning values to @Amount, do I have to do that?  Otherwise, do I have the syntax of my query OK?

I just want to pull the value of [CurrentTrxAmt] elsewhere in the query.  If there is another and better way besides using a UDF, then I am all for that and would like to hear it!

Apitech
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Ephraim WangoyaCommented:

Change the function to
Create function fnCurrentTrxAmt(
	@RMDTYPAL SMALLINT, 
	@DATE1 datetime, 
	@ORTRXAMT numeric(19,5), 
	@CURTRXAM numeric(19,5))
returns numeric(19,5)
as
 begin
	declare @Amount numeric(19,5)
	set @Amount =
	CASE 
		WHEN @RMDTYPAL = 1 and @DATE1 > '2011-03-31 00:00:00.000' THEN 
			@ORTRXAMT
		WHEN @RMDTYPAL = 2 and @DATE1 > '2011-03-31 00:00:00.000' THEN 
			@ORTRXAMT * -1 
		WHEN @RMDTYPAL = 3 and RM20201.DATE1 > '2011-03-31 00:00:00.000' THEN 
			@ORTRXAMT
		WHEN @RMDTYPAL = 4 and @DATE1 > '2011-03-31 00:00:00.000' THEN 
			@ORTRXAMT 
		WHEN @RMDTYPAL = 5 and @DATE1 > '2011-03-31 00:00:00.000' THEN 
			@ORTRXAMT 
		WHEN @RMDTYPAL = 6 and @DATE1 > '2011-03-31 00:00:00.000' THEN 
			@ORTRXAMT
		WHEN @RMDTYPAL = 7 and @DATE1 > '2011-03-31 00:00:00.000' THEN 
			@ORTRXAMT * -1 
		WHEN @RMDTYPAL = 8 and @DATE1 > '2011-03-31 00:00:00.000' THEN 
			@ORTRXAMT * -1 
		WHEN @RMDTYPAL = 9 and @DATE1 > '2011-03-31 00:00:00.000' THEN 
			@ORTRXAMT * -1 
		ELSE 
			 @CURTRXAM 
	END
	return @Amount
end

Open in new window

0
Ephraim WangoyaCommented:

Wherever you need it, just call the function

eg

select field1, dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM) [CurrentTrxAmt],
          field2 * dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM) [CurrentTrxAmt by Field2]
   .........
0
apitechAuthor Commented:
Thank you, so much!

On my third question regarding @TargetDate, since that date comes from a separate SQL object (a stored proedure) and since this function fnCurrentTrxAmt will reside in this query and therefore in this stored procedure, I'm guessing that I will need to declare that variable in this function.  Am I correct?

If so, then is it as simple as declaring the variable as "@TargetDate datetime"?

Apitech
0
Ephraim WangoyaCommented:

Looks like the variable will be declared in the stored procedure and passed to this function
0
apitechAuthor Commented:
Very good, then!  

Thanks, so much!

I will start testing this and so forth.  If I need anything further, I will let you know!

Apitech
0
apitechAuthor Commented:
Actually, I did get an error later in the query on the following:

CASE WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 1 THEN
dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM) [CurrentTrxAmt],

As you can see, I have replaced RM20101.ORTRXAMT in the [NotDue] column with the function.  I'm getting "incorrect syntax near [CurrentTrxAmt]".  Why?

The reason there is a comma afterward is simply because the above is an excerpt from a large CASE statement.

Thanks!

Apitech
0
apitechAuthor Commented:
OK.  I took out that comma. Yes, I should not have had that in there.

I'm still getting the same syntax error.  HELP!
0
Ephraim WangoyaCommented:


CASE
   WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 1 THEN
      dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM)
  else
     0.0
end [CurrentTrxAmt],
0
apitechAuthor Commented:
Well, let me post the full syntax of the case statement to show what I'm getting at.  I think that that's where the confusion is:

CASE WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 1 THEN dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM) [CurrentTrxAmt]
 WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') > 1 AND RM20101.RMDTYPAL = 2 THEN dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM) [CurrentTrxAmt] * -1
 WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 3 THEN dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM) [CurrentTrxAmt]
 WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 4 THEN dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM) [CurrentTrxAmt]
 WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 5 THEN dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM) [CurrentTrxAmt]
 WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 6 THEN dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM) [CurrentTrxAmt]
 WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') > 1 AND RM20101.RMDTYPAL = 7 THEN dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM) [CurrentTrxAmt] * -1
 WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') > 1 AND RM20101.RMDTYPAL = 8 THEN dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM) [CurrentTrxAmt] * -1
 WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') > 1 AND RM20101.RMDTYPAL = 9 THEN dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM) [CurrentTrxAmt] * -1
ELSE 0 END as [NotDue]
0
Ephraim WangoyaCommented:
Change it to
CASE 
 WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 1 THEN 
	dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM)
 WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') > 1 AND RM20101.RMDTYPAL = 2 THEN 
	dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM) * -1
 WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 3 THEN 
	dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM)
 WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 4 THEN 
	dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM) 
 WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 5 THEN 
	dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM) 
 WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 6 THEN 
	dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM) 
 WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') > 1 AND RM20101.RMDTYPAL = 7 THEN 
	dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM) * -1
 WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') > 1 AND RM20101.RMDTYPAL = 8 THEN 
	dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM) * -1
 WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') > 1 AND RM20101.RMDTYPAL = 9 THEN 
	dbo.fnCurrentTrxAmt(RMDTYPAL, DATE1, ORTRXAMT, CURTRXAM) * -1
 ELSE 0 
END [NotDue]

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
apitechAuthor Commented:
Thank you!  I'm not getting any errors, now!

I'll let you all know if there is anything else!

John
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.