Case statement help!!!

[dwight1]

Can any one advise how to write the following inter-related case statements in a single function.

1. IIf([actiondirectors] Is Null,[action],[actiondirectors]) AS Actiontogether, IIf([actiontogether]="accepted Report",[action],[actiontogether]) AS Actionoverall, IIf([actionoverall] Is Null,[actiontogether],[actionoverall]) AS actionsummary,

2. IIf([directors meeting day] Is Null,[meeting day],[directors meeting day]) AS Combineddate,
Year([combineddate]) AS [Year], Month([combineddate]) AS [Month], Format([combineddate],"mmmm" & " " & "yy") AS Alphamonth,

Thanks in advance for your help.

Dwight

 

[dwight1]

I want to use a case statement to name an alias and use the same alias again to make another case statement. Example,

CASE WHEN [actiontogether] = 'accepted Report' THEN [action] ELSE [actiontogether] END AS Actionoverall,
case when (CASE WHEN [actiontogether] = 'accepted Report' THEN [action] ELSE [actiontogether] END) Is Null then [actiontogether] else [actionoverall]) AS actionsummary

Any help is appreciated.

Dwight

 

[cmmrfrds]

I am not exactly sure what you are looking for, but you can't reference an alias at the same level it is created. You can reference an alias in an outer level of the sql statement.

Select
CASE WHEN Actionoverall = 'something' THEN 'X' ELSE 'Y' END AS FinalAction
From
(Select
CASE WHEN [actiontogether] = 'accepted Report' THEN [action] ELSE [actiontogether] END AS Actionoverall
From yourtable
) as InLevel

 

[dwight1]

Cmmrfrds,

Select
CASE WHEN Actionoverall = 'blank' THEN 'actiontogether' ELSE 'actionoverall' END AS FinalAction
From
(Select
CASE WHEN [actiontogether] = 'accepted Report' THEN [action] ELSE [actiontogether] END AS Actionoverall
From yourtable
) as InLevel

I am sorry but i did not quite understand you. The second "SELECT" is it a part of a current function/sp or we are refering to another function/sp altogether.

Thanks for your help.

Dwight

 

[cmmrfrds]

In the example the select in the parens creates an intermediate table that is passed back to the outer select. This is a simple SQL statement. Did you want to make a function or sp?

 

[dwight1]

CMMRFRDS,

No not really. As i was not aware of this i created three different functions modifying each time i had some thing that required a subquery.

Well as you can understand, though i did transform five databases to adp still i follow steps/create differnt functions one after the other to get to my required fields.(which is obviously not a good method).

Anyways, can you tell me where i am going wrong in the following statement. It complains about "invalid syntax near the key word from".

SELECT CASE WHEN Actionoverall IS NULL THEN 'actiontogether' ELSE 'actionoverall' END AS FinalAction
FROM (SELECT CASE WHEN [actiontog] = 'accepted Report' THEN [action] ELSE [actiontogether] END AS Actionoverall
FROM mainqueryforallapprovedloans_step2) AS InLevel
FROM mainqueryforallapprovedloans_step2


Thank you

Dwight

 

[cmmrfrds]

What happens when you run this? The 2nd From is not needed at this point. Does the query as stated work?


SELECT CASE WHEN Actionoverall IS NULL THEN 'actiontogether' ELSE 'actionoverall' END AS FinalAction
FROM
(SELECT CASE WHEN [actiontog] = 'accepted Report' THEN [action] ELSE [actiontogether] END AS Actionoverall
FROM mainqueryforallapprovedloans_step2) AS InLevel

 

[dwight1]

It complains invalid object name "mainqueryforallapprovedloans_step2". I checked the name of the function and it is correct.


The following are the three steps i am currently using to get to my required fields.

Step1:

ALTER FUNCTION dbo.MainQueryForAllApprovedLoans_Step1
()
RETURNS TABLE
AS
RETURN ( SELECT case when [actiondirectors] Is Null then [action] else [actiondirectors] end AS Actiontogether,
FROM [Loan Info1] LEFT JOIN
Officers ON [Loan Info1].[OFF] = Officers.Officer )

Step2:

ALTER FUNCTION dbo.MainQueryForAllApprovedLoans_Step2
()
RETURNS TABLE
AS
RETURN ( SELECT CASE WHEN [actiontogether] = 'accepted Report' THEN [action] ELSE [actiontogether] END AS Actionoverall

FROM dbo.MainQueryForAllApprovedLoans_Step1() MainQueryForAllApprovedLoans_Step1 )

Step3:

ALTER FUNCTION dbo.MainQueryForAllApprovedLoans_Step3
()
RETURNS TABLE
AS
RETURN ( SELECT CASE WHEN [actionoverall] IS NULL
THEN [actiontogether] ELSE [actionoverall] END AS Finalaction,
FROM dbo.MainQueryForAllApprovedLoans_Step2() MainQueryForAllApprovedLoans_Step2 )

THanks for your time and advise.

Dwight

 

[cmmrfrds]

You should be able to go against your table(s) and return the results with 1 sql statement. Try a simple step as below and maybe you can eliminate all the functions. Although the function should work it should be prefixed with dbo.mainqueryforallapprovedloans_step2

SELECT CASE WHEN Actionoverall IS NULL THEN 'actiontogether' ELSE 'actionoverall' END AS FinalAction
FROM
(SELECT CASE WHEN [actiontog] = 'accepted Report' THEN [action] ELSE [actiontogether] END AS Actionoverall
FROM [Loan Info1]
LEFT JOIN Officers
ON [Loan Info1].[OFF] = Officers.Officer) AS InLevel

 

[dwight1]

cmmrfrds,

What is inlevel? why and what are we defining there?

Thanks

Dwight

 

[cmmrfrds]

It is a name for the temporary table that is created by the following SQL. It is unnecessary in this case but would be needed to reference the fields in the temporary table if you wanted to join to another table.

(SELECT CASE WHEN [actiontog] = 'accepted Report' THEN [action] ELSE [actiontogether] END AS Actionoverall
FROM [Loan Info1]
LEFT JOIN Officers
ON [Loan Info1].[OFF] = Officers.Officer)

Lets say we join the temporary table to another table.

SELECT
A.somefieldfromA,
A.anIDfield,
CASE WHEN InLevel.Actionoverall IS NULL THEN 'actiontogether' ELSE 'actionoverall' END AS FinalAction
FROM anothertable as A
Inner Join
(SELECT anIDfield,
CASE WHEN [actiontog] = 'accepted Report' THEN [action] ELSE [actiontogether] END AS Actionoverall
FROM [Loan Info1]
LEFT JOIN Officers
ON [Loan Info1].[OFF] = Officers.Officer) AS InLevel
ON inLevel.anIDfield = A.anIDfield