Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

If/Then statements in a SQL query, help!

Posted on 2011-04-28
19
Medium Priority
?
532 Views
Last Modified: 2012-05-11
Hello, I have this simple SQL query:

SELECT Imported.[Call Number], Imported.ID, Imported.[Reprint Edition], Imported.[Reviewed Item], Imported.PPopulation, Imported.DDesign1, Imported.O1Outcome1, Imported.D2Design2, Imported.O2Outcome2, Imported.NTotal, Imported.SpecialPop, Imported.NRD, Imported.NRO1, Imported.NRO2, Imported.NDE, Imported.SMN
FROM Imported
WHERE (((Imported.[Reprint Edition])='get'));

I want to make it a bit more complicated by adding some if/then statements but I am having a hard time getting it to work.  The fields PPopulation, DDesign1, O1Outcome1, D2Design2, O2Outcome2 are all 'yes/no' fields and NTotal is a numeric field.
I want to make a decision tree like the image in the screen shot.  Where, for example if PPopulation, DDesign1, and O1Outcome1 are all yes then Reviewed Item is equal to INC.  Could anyone show me an example of how to do this?


viewer.png
0
Comment
Question by:Bevos
  • 11
  • 7
19 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35487368
You don't say what you want the [Reviewed Item] to be set to if your conditions aren't met, so I set it to '???'.
SELECT Imported.[Call Number]
  ,Imported.ID
  ,Imported.[Reprint Edition]
  ,Imported.[Reviewed Item]
  ,Imported.PPopulation
  ,Imported.DDesign1
  ,Imported.O1Outcome1
  ,Imported.D2Design2
  ,Imported.O2Outcome2
  ,Imported.NTotal
  ,Imported.SpecialPop
  ,Imported.NRD
  ,Imported.NRO1
  ,Imported.NRO2
  ,Imported.NDE
  ,Imported.SMN
  ,CASE WHEN (PPopulation = 'yes') AND (DDesign1 = 'yes') AND (O1Outcome1 = 'yes')
        THEN 'INC'
        ELSE '???'
   END [Reviewed Item]
FROM Imported
WHERE (Imported.[Reprint Edition]= 'get';

Open in new window

0
 
LVL 7

Expert Comment

by:Cboudroz
ID: 35487394
--> INC

is it a column or a string?

-- > If/Then statements in a SQL

you need to used CASE WHEN THEN ELSE END

see example:

SELECT 
	-- return different string
	CASE 
		WHEN CONDITION1 = 1 AND CONDITION2 = 1 THEN 'MATCHCONDITION1'
		WHEN CONDITION1 = 1 AND CONDITION2 = 0 THEN 'MATCHCONDITION2'
		WHEN CONDITION1 = 0 AND CONDITION2 = 0 THEN 'MATCHCONDITION3'
		ELSE 'DON''T MATCH'
	END AS vchConditionResults
	, -- return different column
	CASE 
		WHEN CONDITION1 = 1 AND CONDITION2 = 1 THEN col1
		WHEN CONDITION1 = 1 AND CONDITION2 = 0 THEN col2
		WHEN CONDITION1 = 0 AND CONDITION2 = 0 THEN col3
		ELSE col4
	END AS vchConditionResults
FROM 
	...

Open in new window



The first condition to match will be the one used.

            WHEN CONDITION1 = 1 AND CONDITION2 = 1 THEN 'MATCHCONDITION1'
            WHEN CONDITION1 = 1 THEN 'MATCHCONDITION2'

if condtion1 and condtion2 are equal to 1 the results will be 'MATCHCONDITION1'
0
 

Author Comment

by:Bevos
ID: 35487399
That's awesome 8080! Thanks for helping me again :)
So the path that you described using the case statement is the uppermost one on the decision tree.
So if I understand correctly can I just add this type of line for the next (this has 'two' cases one for entering EXC as the reviewed item field and then entering 'yes' in the NRD field.
CASE WHEN (PPopulation = 'no')
        THEN 'EXC'
 END [Reviewed Item]
CASE WHEN (PPopulation = 'no')
        THEN 'Yes'
End [NRD]

Can I just keep chaining these case commands to create the decision tree in the picture?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Expert Comment

by:8080_Diver
ID: 35487403
Note: My response is based upon T-SQL and not Access SQL but it may still work.

Just noticed that you already had [Reviewed Item] in your selection list . . . so, I am ow assuming that if your requirements aren't met, you want to return whatever was already in [Reviewed Item (as shown below).
SELECT Imported.[Call Number]
  ,Imported.ID
  ,Imported.[Reprint Edition]
  ,CASE WHEN (PPopulation = 'yes') AND (DDesign1 = 'yes') AND (O1Outcome1 = 'yes')
        THEN 'INC'
        ELSE Imported.[Reviewed Item]
   END [Reviewed Item]
  ,Imported.PPopulation
  ,Imported.DDesign1
  ,Imported.O1Outcome1
  ,Imported.D2Design2
  ,Imported.O2Outcome2
  ,Imported.NTotal
  ,Imported.SpecialPop
  ,Imported.NRD
  ,Imported.NRO1
  ,Imported.NRO2
  ,Imported.NDE
  ,Imported.SMN
FROM Imported
WHERE (Imported.[Reprint Edition]= 'get';

Open in new window


If not, try the attached.
SELECT Imported.[Call Number]
  ,Imported.ID
  ,Imported.[Reprint Edition]
  ,[Reviewed Item] = IIF(((PPopulation = 'yes') AND (DDesign1 = 'yes') AND (O1Outcome1 = 'yes')),'INC', Imported.[Reviewed Item]) 
  ,Imported.PPopulation
  ,Imported.DDesign1
  ,Imported.O1Outcome1
  ,Imported.D2Design2
  ,Imported.O2Outcome2
  ,Imported.NTotal
  ,Imported.SpecialPop
  ,Imported.NRD
  ,Imported.NRO1
  ,Imported.NRO2
  ,Imported.NDE
  ,Imported.SMN
FROM Imported
WHERE (Imported.[Reprint Edition]= 'get';

Open in new window

0
 

Author Comment

by:Bevos
ID: 35487410
Hi Cboudroz, and thanks for that very detailed reply! I am still a bit confused about how I 'chain' these commands.  Do I need to do one very long statement for all of the paths on the decision picture I included, or segment it like I listed in my response above?

Thanks again,
Bevo
0
 

Author Comment

by:Bevos
ID: 35487430
Yes, sorry I should have been clearer.  This is in an MS Access Database.  I think at the end of all of these CASE statements the [Reviewed Item] field have a code of INC or EXC.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35487498
Can I just keep chaining these case commands to create the decision tree in the picture?

Sort of but not exactly.

You can only assign a value to a given column once.  So, if other conditions cause you to want [Reviewed Item] to have a different value, then you need that to be in the one CASE statement for Reviewed Item, e.g.:
SELECT Imported.[Call Number]
  ,Imported.ID
  ,Imported.[Reprint Edition]
  ,CASE WHEN (PPopulation = 'yes') AND (DDesign1 = 'yes') AND (O1Outcome1 = 'yes') THEN 'INC'
            WHEN (PPopulation = 'no') THEN 'EXC'
            ELSE Imported.[Reviewed Item]
   END [Reviewed Item]
  ,Imported.PPopulation
  ,Imported.DDesign1
  ,Imported.O1Outcome1
  ,Imported.D2Design2
  ,Imported.O2Outcome2
  ,Imported.NTotal
  ,Imported.SpecialPop
  ,Imported.NRD
  ,Imported.NRO1
  ,Imported.NRO2
  ,Imported.NDE
  ,Imported.SMN
FROM Imported
WHERE (Imported.[Reprint Edition]= 'get');

Open in new window


In effect, you need to have a single case statement for each of the decision trees for a given column.  So, if you have a decision tree that defines what value, for example, NRD is supposed to get, then you need to have a CASE statement that includes all of the controling options in a manner similar to what I presented above for the [Reviewed Item].

To explain a bit further, from your diagram, it looks like you have teh following decision paths that result in [Reviewed Item] being set to 'INC':

1) P = Y, D1 = Y, O1 = Y
2) P = Y, D1 = N, D2 = Y, O2 = Y, SP = Y
3) P = Y, D1 = N, D2 = Y, O2 = Y, SP = N, n >= 100

Each of those will define a WHEN line ending in THEN 'INC'.  (I would recommend having each on a separate WHEN line even though, theoretically, you could build them into one when line using OR to choose which "Rule" is to be followed.  The maintenance will be much easier if they are each on separate lines. ;-)

Similarly, you would have a WHEN line tracing each of the other decision paths that result in other values for [Reviewed Item.

0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35487539
In Access, this may get to be an ugly, complicated IIF statement if you can't use a CASE statement.

Your equivalent of the case statement is a set of nested IIF statements (and, by the way, there is a limit to the depth of nesting but I don't remember what it is):
,[Reviewed Item] = IIF(((PPopulation = 'yes') AND (DDesign1 = 'yes') AND (O1Outcome1 = 'yes')),'INC', 
                                       IIF(((P = Y) AND (D1 = N) AND (D2 = Y) AND (O2 = Y) AND (SP = Y)), 'INC',
                                              IIF(((P = Y) AND (D1 = N) AND (D2 = Y) AND (O2 = Y) AND (SP = N) AND (n >= 100)), Imported.[Reviewed Item]))))

Open in new window


0
 

Author Comment

by:Bevos
ID: 35487629
Hi 8080, I tried out using your code and got the following below.  So we have these three paths that you mentioned which lead to INC, and then if not INC I would like to make the [Reviewed Item] be 'Exc'  I tried adding an Else 'EXC' to the query but I think I did it incorrectly.  Can you please look at the code and tell me where I went wrong?

Thank you so much,
Bevo

SELECT Imported.[Call Number]
  ,Imported.ID
  ,Imported.[Reprint Edition]
  ,[Reviewed Item] = IIF(((PPopulation = 'yes') AND (DDesign1 = 'yes') AND (O1Outcome1 = 'yes')),'INC', 
                                       IIF(((PPopulation = 'yes') AND (DDesign1 = 'no') AND (DDesign2 = 'yes') AND (O2Outcome2 = 'Yes') AND (SP_YN = 'Yes')), 'INC',
                                              IIF(((PPopulation = 'yes') AND (DDesign1 = 'no') AND (DDesign2 = 'yes') AND (O2Outcome2 = 'Yes') AND (SP_YN = 'no') AND (NTotal >= 100)),'INC' ELSE 'EXC',   Imported.[Reviewed Item]))));
  ,Imported.PPopulation
  ,Imported.DDesign1
  ,Imported.O1Outcome1
  ,Imported.D2Design2
  ,Imported.O2Outcome2
  ,Imported.NTotal
  ,Imported.SpecialPop
  ,Imported.NRD
  ,Imported.NRO1
  ,Imported.NRO2
  ,Imported.NDE
  ,Imported.SMN
  ,Imported.SP_YN
FROM Imported
WHERE (Imported.[Reprint Edition]= 'get';

Open in new window

0
 

Author Comment

by:Bevos
ID: 35487634
Sorry, I forgot to say I get a missing operator at the ELSE. I tried adding a comma before the  Else but it still wasn't accepting the SQL code.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35487670
When you are using the IIF construct, ELSE is not only meaningless but it is an error . . . as you may have noticed. ;-)

SELECT Imported.[Call Number]
  ,Imported.ID
  ,Imported.[Reprint Edition]
  ,[Reviewed Item] = IIF(((PPopulation = 'yes') AND (DDesign1 = 'yes') AND (O1Outcome1 = 'yes')),'INC', 
                                       IIF(((PPopulation = 'yes') AND (DDesign1 = 'no') AND (DDesign2 = 'yes') AND (O2Outcome2 = 'Yes') AND (SP_YN = 'Yes')), 'INC',
                                              IIF(((PPopulation = 'yes') AND (DDesign1 = 'no') AND (DDesign2 = 'yes') AND (O2Outcome2 = 'Yes') AND (SP_YN = 'no') AND (NTotal >= 100)),'INC', 'EXC'))));
  ,Imported.PPopulation
  ,Imported.DDesign1
  ,Imported.O1Outcome1
  ,Imported.D2Design2
  ,Imported.O2Outcome2
  ,Imported.NTotal
  ,Imported.SpecialPop
  ,Imported.NRD
  ,Imported.NRO1
  ,Imported.NRO2
  ,Imported.NDE
  ,Imported.SMN
  ,Imported.SP_YN
FROM Imported
WHERE (Imported.[Reprint Edition]= 'get';

Open in new window


As shown above, when using the IIF construct, the equivalent of the ELSE clause is the second "answer" after the CONDITION ExPRESSION.  You can, sort of, read the IIF construct as follows:

IIF(([CONDITION Expression], [THEN Expression], [ELSE IIF(([CONDITION Expression], [THEN Expression], [ELSE Expression]) ])

Open in new window


So, a nested IIF reads sort of like:
IIF(([CONDITION Expression], [THEN Expression], [ELSE Expression])

Open in new window


IIF
0
 

Author Comment

by:Bevos
ID: 35487739
With those changes the query is working! However, because I am an Access newbie I think I've made a mistake in how I wanted to implement this.
Basically I have a form with this query as the record source and a series of check boxes (indicating the paths described above). When I goto the query (in design view) I am getting an Expr1:[Reviewed Item] = Iif statement.  And if I look at the query as a datasheet I just  get an error in this column.  
Do you know why this might be?

Bevo
0
 

Author Comment

by:Bevos
ID: 35487771
Sorry, just to clarify - when I said the query is working I meant that Access allowed me to save it.  It looks like the conditional if statement is returning an error for all entries even if I go through and make the path selections outlined above.  The error is present for each entry in the query.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35488106
Bevo (any connection to UofTX?)

where you have Expr1:[Reviewed Item] = Iif statement, you need ot have Reviewed Item] : Iif statement.

However, based upon your post #35487739, it may be that you need to have the IFF statement (or, actually, "statements) embedded in the form; although, I am trying to picture exactly what you are trying to accomplish between the query and the form.

Are the series of checkboxes associated with columns in the query?  

If you try to associate a checkbox with the Reviewed Item results that are, in fact, a string, then you can expect to have some sort of problem because a checkbox expects to deal with a boolean (Y/N, 0/-1, T/F) value.  

It looks like the conditional if statement is returning an error for all entries even if I go through and make the path selections outlined above.  The error is present for each entry in the query.

What error are you getting?  Are you getting it when you execute the query from the query designer or from the form?
0
 

Author Comment

by:Bevos
ID: 35488209
I went to U of T to study Biology :)  [This is probably why I'm so bad at programming]

So, the form is basically a decision tree to say if an academic study should be included in a literature review.

The check boxes are the 'yes' and 'no' options in the image above and are included in the query and form
So, you are saying that I need to change how I have set these up (right now I have ='yes' = 'no').  So I should replace these with ='Y' ='N'?  Sorry, I haven't used check boxes in a query before.  I've attached the code changing the expression1 to simply Reviewed Item: IIf and then all of the yes and no items in the statement to Y and N respectively.  

The error I am getting is in the query 'datasheet' view.  In reviewed item I get an error for all entries.

I am going to attach the database to this form as it might help you understand quicker than my limited knowledge of Access and SQL can explain things.  The form I'm talking about is frmDecisionTree and the query is qryDecisionTree.

Thanks again :)

SELECT Imported.[Call Number], Imported.ID, Imported.[Reprint Edition], IIf((([PPopulation]='Y') And ([DDesign1]='Y') And ([O1Outcome1]='Y')),'INC',IIf((([PPopulation]='Y') And ([DDesign1]='N') And ([D2Design2]='Y') And ([O2Outcome2]='Y') And ([SP_YN]='Y')),'INC',IIf((([PPopulation]='Y') And ([DDesign1]='N') And ([D2Design2]='Y') And ([O2Outcome2]='Y') And ([SP_YN]='N') And ([NTotal]>=100)),'INC','EXC'))) AS [Reviewed Item], Imported.PPopulation, Imported.DDesign1, Imported.O1Outcome1, Imported.D2Design2, Imported.O2Outcome2, Imported.NTotal, Imported.SpecialPop, Imported.NRD, Imported.NRO1, Imported.NRO2, Imported.NDE, Imported.SMN, Imported.SP_YN
FROM Imported
WHERE (((Imported.[Reprint Edition])='get'));

Open in new window

0
 

Author Comment

by:Bevos
ID: 35488213
I went to U of T to study Biology :)  [This is probably why I'm so bad at programming]

So, the form is basically a decision tree to say if an academic study should be included in a literature review.

The check boxes are the 'yes' and 'no' options in the image above and are included in the query and form
So, you are saying that I need to change how I have set these up (right now I have ='yes' = 'no').  So I should replace these with ='Y' ='N'?  Sorry, I haven't used check boxes in a query before.  I've attached the code changing the expression1 to simply Reviewed Item: IIf and then all of the yes and no items in the statement to Y and N respectively.  

The error I am getting is in the query 'datasheet' view.  In reviewed item I get an error for all entries.

I am going to attach the database to this form as it might help you understand quicker than my limited knowledge of Access and SQL can explain things.  The form I'm talking about is frmDecisionTree and the query is qryDecisionTree.

Thanks again :)
0
 

Author Comment

by:Bevos
ID: 35488218
Sorry, for the double post, the server is behaving strangely when I'm trying to upload the database.

EE-example.accdb
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 2000 total points
ID: 35488453
Okay, here's teh $64,000 question:
This [Reviewed Item] column . . . you are currently attempting to set it to either 'INC' or 'Exc', which I assume means Include or Exclude.  If you are trying to display it in a checkbox, it needs to be a Yes/No, Y/N, -1/0, or a True/False column instead of a string/text column.  If 'Inc' would translate to True and an 'Exc' would translate to False, then that's what you need instead of 'Inc' or 'Exc'.

I looked at the Decision Tree form and couldn't figure out where you were displaying the [Reviewed Item] results, though.

Since all of the column that you are testing in the query are boolean types, you should not be comparing them to a character (either 'y' or 'n') but, instead, just testing them to see if they are true or false.  I have modified your qryDecisionTree so that it now has a new column ([New Reviewed Item]) that reflects how to use the boolean columns.  I also added a test row to the data driving the query so that I could test for a True condition. EE-example-2-.accdb
0
 

Author Comment

by:Bevos
ID: 35488544
Hi again 8080, this code works great! So the value -1 corresponds to 'true' in this boolean column?
I think I will leave the code as you have it right now and post a new question asking how to pass the -1 or 0 to [ Reviewed Item] as INC or EXC respectively.  Also I need to ask how to correctly set the record as the query (right now the selection combo isn't working as I intended).

Thanks for being so thorough with this as always :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question