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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can use CTE to reuse computed fields. Here's an example how to implement what you need.
CREATE table #table1 (number int) --test table


INSERT #table1		--insert test data

	      ,CASE number WHEN 1 then 'one'
	                   WHEN 2 then 'two'
	       ELSE 'greater than two'
	       END [text1]	--[text1] is the result of first If-Then statement
	FROM #table1 
      ,CASE number WHEN 3 THEN 'three'
                   WHEN 4 THEN 'four'
       ELSE [text1]		--reuse result of [text1] and add more conditions 
       END [text2]


DROP TABLE #table1

Open in new window

apitechAuthor Commented:
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!
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:

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

apitechAuthor Commented:
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.
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:

    SELECT [1st Result] = CASE WHEN datediff(day, RM30101.DUEDATE, '2011-03-01 00:00:00.000') ...
    FROM tablename
     [1st Result] --Result of 1st if-then statement
    ,[2nd result] =  CASE WHEN other conditions THEN value if true ELSE [1st Result] END

Open in new window

apitechAuthor Commented:
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.
The 1st result is the name of the result of your 1st if then statement. Likewise, the 2nd result corresponds to the 2nd if-then statement. You can use the result of the 1st statement if you use CTE through its alias name.

i.e. If the alias name for your 1st statement is [Current Amount], you can use that alias to return the result of the 1st statement.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.