Link to home
Start Free TrialLog in
Avatar of apitech
apitech

asked on

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
Avatar of Imran Javed Zia
Imran Javed Zia
Flag of Pakistan image

you can use it as :


select dbo.fnCurrentTrxAmt (RMDTYPAL_Field, DATE1_Field, ORTRXAMT_Field, CURTRXAM_Field), * from tbl

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

ASKER

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

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


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]
   .........
Avatar of apitech

ASKER

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

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

ASKER

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
Avatar of apitech

ASKER

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
Avatar of apitech

ASKER

OK.  I took out that comma. Yes, I should not have had that in there.

I'm still getting the same syntax error.  HELP!


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],
Avatar of apitech

ASKER

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]
ASKER CERTIFIED SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of apitech

ASKER

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

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

John