We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How to Use a Function to Contain a Query Value

Medium Priority
293 Views
Last Modified: 2012-08-14
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
Comment
Watch Question

Imran Javed ZiaConsultant Software Engineer - .NET Architect
CERTIFIED EXPERT

Commented:
you can use it as :


select dbo.fnCurrentTrxAmt (RMDTYPAL_Field, DATE1_Field, ORTRXAMT_Field, CURTRXAM_Field), * from tbl
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

Your function will always return a zero, you have to assign the values to @Amount varaible

Author

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
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

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

Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

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]
   .........

Author

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
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

Looks like the variable will be declared in the stored procedure and passed to this function

Author

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

Author

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

Author

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!
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:


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],

Author

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]
Software Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you!  I'm not getting any errors, now!

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

John
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.