apitech
asked on
Using the Results of an "If, Then" Statement in a Second "If, Then" Statement
In a SQL query, I have an "If, Then" statement (i.e. a "Select Case When" statement). That part of the query represents a field (column) named "[CurrentTrxAmt]". That field's name, as you can tell, is in brackets.
Later in the query, I need to use that particular "If, Then" statement within another "If, Then" statement. That second "If, Then" statement is to say "If this happens, then use the results of that first 'If, Then' statement".
Truthfully, what I would like to do is to take that bracketed "[CurrentTrxAmt]" field and nest it within that second "If, Then" statement rather than re-typing that entire first "If, Then" statement. But, when I try that, SQL gives me an error message basically saying that there is no such thing as that bracketed field. It's one of those situations where, when you have a bracketed field name, SQL will not let you use that bracketed name again later in that same query.
Nevertheless, how do I nest that very complex first "If, Then" statement within the second?
Attached is the code. I want to use [CurrentTrxAmt] within [NotDue]. The [NotDue] field is the seconfd "If, Then" statement that I referred to earlier.
CurrentTrxAmt.docx
Later in the query, I need to use that particular "If, Then" statement within another "If, Then" statement. That second "If, Then" statement is to say "If this happens, then use the results of that first 'If, Then' statement".
Truthfully, what I would like to do is to take that bracketed "[CurrentTrxAmt]" field and nest it within that second "If, Then" statement rather than re-typing that entire first "If, Then" statement. But, when I try that, SQL gives me an error message basically saying that there is no such thing as that bracketed field. It's one of those situations where, when you have a bracketed field name, SQL will not let you use that bracketed name again later in that same query.
Nevertheless, how do I nest that very complex first "If, Then" statement within the second?
Attached is the code. I want to use [CurrentTrxAmt] within [NotDue]. The [NotDue] field is the seconfd "If, Then" statement that I referred to earlier.
CurrentTrxAmt.docx
ASKER
Thanks, Thomasian! I appreciate the response!
Wow! That is very complex and goes beyond what I was looking for.
There's no way to do this, without creating a table? I really would prefer not having to create a table or view to get this to work.
Is there way of doing this with a nested "if, then" statement? I didn't want to have to go that route, as I was saying in my posting. But, if that's the only other way to go, so be it.
Again, thank you! Any help is appreciated!
Wow! That is very complex and goes beyond what I was looking for.
There's no way to do this, without creating a table? I really would prefer not having to create a table or view to get this to work.
Is there way of doing this with a nested "if, then" statement? I didn't want to have to go that route, as I was saying in my posting. But, if that's the only other way to go, so be it.
Again, thank you! Any help is appreciated!
No, you won't have to create another table. The table "#table1" is just used to show you how it works. The actual query starts with ";With CTE ...".
More about CTE: http://msdn.microsoft.com/en-us/library/ms190766.aspx
More about CTE: http://msdn.microsoft.com/en-us/library/ms190766.aspx
ASKER
Thanks, Thomasian! I'm still not clear, though, on how to incorporate the SQL syntax of CTE.
Following is an example from my code:
CASE WHEN datediff(day, RM30101.DUEDATE, '2011-03-01 00:00:00.000') < 1 AND RM30201.APTODCTY = 1 THEN [CurrentTrxAmt].
Again, [CurrentTrxAmt] represents my If then statement that I want to use in subsequent if, then statements such as the one above.
So, how would I fit this syntax in? I really don't want to have to "dissect" the [CurrentTrxAmt] syntax. I just want to plug it in.
Following is an example from my code:
CASE WHEN datediff(day, RM30101.DUEDATE, '2011-03-01 00:00:00.000') < 1 AND RM30201.APTODCTY = 1 THEN [CurrentTrxAmt].
Again, [CurrentTrxAmt] represents my If then statement that I want to use in subsequent if, then statements such as the one above.
So, how would I fit this syntax in? I really don't want to have to "dissect" the [CurrentTrxAmt] syntax. I just want to plug it in.
Do you need the result of the 1st if then statement or the logic itself? If it is the latter, then it is not possible unless you create a udf (user defined function).
If it is the former, the you can implement it like this:
If it is the former, the you can implement it like this:
;WITH CTE AS (
SELECT [1st Result] = CASE WHEN datediff(day, RM30101.DUEDATE, '2011-03-01 00:00:00.000') ...
FROM tablename
)
SELECT
[1st Result] --Result of 1st if-then statement
,[2nd result] = CASE WHEN other conditions THEN value if true ELSE [1st Result] END
ASKER
Hi Thomasian:
I'm not 100% sure. I mean, it needs to be the results of the if, then statement for that particular record. I don't know if that means the former or the latter.
In any case, what do I put in for "1st result"? Also, what is being said for "2nd result"? I'm unclear.
I'm not 100% sure. I mean, it needs to be the results of the if, then statement for that particular record. I don't know if that means the former or the latter.
In any case, what do I put in for "1st result"? Also, what is being said for "2nd result"? I'm unclear.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Open in new window