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

Thomasian

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

apitech

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!

Thomasian

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

apitech

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.

Thomasian

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

apitech

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.

ASKER CERTIFIED SOLUTION

Thomasian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Using the Results of an &quot;If, Then&quot; Statement in a Second &quot;If, Then&quot; Statement

Using the Results of an "If, Then" Statement in a Second "If, Then" Statement