Bevos
asked on
If/Then statements in a SQL query, help!
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
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
--> 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:
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'
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
...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'
ASKER
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?
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?
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).
If not, try the attached.
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';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';ASKER
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
Thanks again,
Bevo
ASKER
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.
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.:
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.
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');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.
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):
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]))))ASKER
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
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';ASKER
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.
When you are using the IIF construct, ELSE is not only meaningless but it is an error . . . as you may have noticed. ;-)
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:
So, a nested IIF reads sort of like:
IIF
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';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]) ])So, a nested IIF reads sort of like:
IIF(([CONDITION Expression], [THEN Expression], [ELSE Expression])IIF
ASKER
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
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
ASKER
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.
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?
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?
ASKER
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 :)
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'));ASKER
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 :)
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 :)
ASKER
Sorry, for the double post, the server is behaving strangely when I'm trying to upload the database.
EE-example.accdb
EE-example.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 :)
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 :)
Open in new window