• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

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
0
apitech
Asked:
apitech
  • 7
  • 6
1 Solution
 
Imran Javed ZiaCommented:
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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
 
apitechAuthor Commented:
Thank you!  I'm not getting any errors, now!

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

John
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now