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!
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
AMA.txt
Your function will always return a zero, you have to assign the values to @Amount varaible
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
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
Wherever you need it, just call the function
eg
select field1, dbo.fnCurrentTrxAmt(RMDTYP
field2 * dbo.fnCurrentTrxAmt(RMDTYP
.........
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
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
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
Thanks, so much!
I will start testing this and so forth. If I need anything further, I will let you know!
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(RMDTYP AL, 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
CASE WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 1 THEN
dbo.fnCurrentTrxAmt(RMDTYP
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
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!
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(RMDTYP
else
0.0
end [CurrentTrxAmt],
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(RMDTYP AL, DATE1, ORTRXAMT, CURTRXAM) [CurrentTrxAmt]
WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') > 1 AND RM20101.RMDTYPAL = 2 THEN dbo.fnCurrentTrxAmt(RMDTYP AL, 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(RMDTYP AL, DATE1, ORTRXAMT, CURTRXAM) [CurrentTrxAmt]
WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 4 THEN dbo.fnCurrentTrxAmt(RMDTYP AL, DATE1, ORTRXAMT, CURTRXAM) [CurrentTrxAmt]
WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 5 THEN dbo.fnCurrentTrxAmt(RMDTYP AL, DATE1, ORTRXAMT, CURTRXAM) [CurrentTrxAmt]
WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 6 THEN dbo.fnCurrentTrxAmt(RMDTYP AL, DATE1, ORTRXAMT, CURTRXAM) [CurrentTrxAmt]
WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') > 1 AND RM20101.RMDTYPAL = 7 THEN dbo.fnCurrentTrxAmt(RMDTYP AL, 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(RMDTYP AL, 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(RMDTYP AL, DATE1, ORTRXAMT, CURTRXAM) [CurrentTrxAmt] * -1
ELSE 0 END as [NotDue]
CASE WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 1 THEN dbo.fnCurrentTrxAmt(RMDTYP
WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') > 1 AND RM20101.RMDTYPAL = 2 THEN dbo.fnCurrentTrxAmt(RMDTYP
WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 3 THEN dbo.fnCurrentTrxAmt(RMDTYP
WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 4 THEN dbo.fnCurrentTrxAmt(RMDTYP
WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 5 THEN dbo.fnCurrentTrxAmt(RMDTYP
WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') < 1 AND RM20101.RMDTYPAL = 6 THEN dbo.fnCurrentTrxAmt(RMDTYP
WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') > 1 AND RM20101.RMDTYPAL = 7 THEN dbo.fnCurrentTrxAmt(RMDTYP
WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') > 1 AND RM20101.RMDTYPAL = 8 THEN dbo.fnCurrentTrxAmt(RMDTYP
WHEN datediff(day, RM20101.DUEDATE, '2011-03-31 00:00:00.000') > 1 AND RM20101.RMDTYPAL = 9 THEN dbo.fnCurrentTrxAmt(RMDTYP
ELSE 0 END as [NotDue]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you! I'm not getting any errors, now!
I'll let you all know if there is anything else!
John
I'll let you all know if there is anything else!
John
select dbo.fnCurrentTrxAmt (RMDTYPAL_Field, DATE1_Field, ORTRXAMT_Field, CURTRXAM_Field), * from tbl