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
LVL 1
apitechAsked:
Who is Participating?
 
ThomasianCommented:
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.
0
 
ThomasianCommented:
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

GO

INSERT #table1		--insert test data
SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4

;WITH CTE AS (
	SELECT *
	      ,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 
)
SELECT *
      ,CASE number WHEN 3 THEN 'three'
                   WHEN 4 THEN 'four'
       ELSE [text1]		--reuse result of [text1] and add more conditions 
       END [text2]
FROM CTE

GO

DROP TABLE #table1

Open in new window

0
 
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!
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ThomasianCommented:
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

0
 
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.
0
 
ThomasianCommented:
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:

;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

Open in new window

0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.