More on IF statements
This presentation will continue discussing IF statements. It will illustrate embedding IFs within IFs and the use of AND and OR.
This code is discussed in the notes for More on IF statements - page 1.
A record is selected based on user input. Then the data is tested and calculations are done based on these tests. Finally the table is updated with the results of the calculations.
Note that the commit simply makes the update permanent. If omitted from the code, the update could be rollbacked. The developer has to decide whether commit is appropriate or not.
Flowchart for the logic of donor2 - code is on the previous slide.
The selection at the top is before the procedure donor2 was executed. The selection at the bottom is after execution.
This is firstpay5. It is broken up into more readable chunks on the next slide.
This code is broken into three chunks for readability. The entire code in smaller font is on the previous slide.
The flowchart is on the next page.
Flowchart to accompany firstpay5. The code is on previous slides. Sample output is on the next slide.
The SELECT at the top shows the before.
The SELECT at the bottom shows after firstpay5 was executed with user input of 5555.
This shows two more executions of firstpay5.
This is a compound AND statement. Both statements must be true to do the processing after the THEN. If either or both statements are false, the processing after the ELSE will be done. When you do a compound you loose the precision available with separate IFs. In other words, you do not know which of the three possibilities cause an execution of the ELSE.
Notice that if you get a no to v_yrgoal > 250 you immediately go to the ELSE and increase by 20%. If you get a yes to v_yrgoal > 250 then you check to see if v_state = MA. If it does you go to the THEN and increase by 10% otherwise you go to the ELSE and increase by 20%.
This show s the output from donor3 when the user entered idno 23456.
This shows the code for firstpay6. Another example of a compound AND.
This shows the logic of the procedure firstpay6.
This shows execution of firstpay6.
When 5555 was entered, a change was made because jobcode was CI and bonus was > 1000.
When 1111 was entered, no change was made because while the jobcode was CI, the bonus was not > 1000.
When 2222 was entered, no change was made because the jobcode was not CI. The fact that bonus was greater than 1000 made no difference since both had to be true.
This slide illustrates the compound OR discussed in the notes.
Flowchart for the compound OR.
If either condition is true the goal will be increased by 10%.
In other words, if the goal is greater than 250, we don't need to ask about MA we can simply increase the goal by 10%.
If the goal is not greater than 250, then we get a second chance which is to test state. If state is MA then again the goal is increased by 10%.
If we get a NO to both conditions, we have run out of chances and the goal is increased by 20%.
This shows the output from donar3a. This example is covered in the notes.
Note that changes are made to both entries.
This slide shows another block of code with the compound or embedded OR.
Record 3333 does not meet the criteria of being either CI or having a bonus > 1000 so the salary is increased by 2%.
Record 1111 meets the criteria of CI (it does not meet the criteria of bonus > 1000 but that is fine because this is an OR relationship so we only need one condition or the other). Since it meets the criteria of CI, the salary is increased by 5%.
Record 6666 does not meet the criteria of CI but it does meet the criteria of bonus > 1000 so the 5% increase is given.
In programming, AND gets resolved before OR. This means that the the conditions around the AND get tied together and acted on first. If you want to change this order, you need to use parenthesis. If parenthesis are used, the contents of the parenthesis will be resolved first.
This example follows the format:
conditionA AND (conditionB OR conditionC)
This means that conditionA is required and either conditionB or conditionC is required. The parenthesis grouped conditionB OR condtionC so that the resolution calls for conditionA and the results from inside the parenthesis which is either conditionB or conditionC.
As can be seen in the flowchart, v_state must equal MA.
Then there are two other possibilities within the parenthesis. Either v_yrgoal must be less than 100 or v_datefst must be greater than 01-JAN-98. If either of these is true in conjunction with v_state = MA then the goal is increased by 10%.
It the state is not MA and neither of the conditions in the parenthesis are true, then there is no change to the goal.
Record 33333 has state = MA and year goal less than 100 so it met the criteria and got raised by 10%.
Record 12121 has state = RI so it does not meet the criteria.
Record 23456 has state = MA but the goal is not less than 100 and the date is not greater than 01-JAN-98 so it does not meet the criteria.
In this code, the IF statement does not have any parenthesis.
Therefore, the conditions around the AND get grouped together by default. The result is that the OR stands alone. The resolution is state = MA AND yrgoal < 100
OR just the datefst . 01-JAN-98.
This shows the logic for state = MA and yrgoal < 100
datefst > 01-JAN-98
Note that if you get a NO to either state = MA or yrgoal < 100, you need to check the OR by asking if datefst > 01-JAN-98.
This shows the same logic but coded differently. Since the OR question stands alone, I ask it first.
Note that in fact I could flowchart the logic this way and not change the code from the original. Both codings convey the same information.
Record 12121 does not change the goal because the state is RI and therefore the criteria of state = MA and yrgoal < 100 is not met. Further the date is not greater than 01-JAN-98 so the OR criteria fails as well.
Record 33333 meets the criteria of MA and goal < 100, therefore the goal is increased by 5%.
This program illustrates checking for null. In this example, the state has to be RI and we are looking for people who gave less than 100 - including nothing. To catch the nothing we have to compare to null.
Note that null must be handled separately. Simply testing for yrgoal < 100 does not catch null values in a field.
This is the format:
condA AND condB OR condC OR condD
Since ANDs are resolved before ORS, this means condA AND condB both haave to be true or condC AND condD both have to be true.
Note that there is no processing if the criteria are not met.
This shows the logic for the code from the previous block.
Note that if we were not checking for jobcode in both sets of conditions, we would have needed to ask about condC on the NO for v_bonus > 1500.
For record 1111 there is no change. It has a code of CI but the bonus is not greater than 1500.
For record 5555 there is a change. It has a code of CI and a bonus > 1500.
For record 2222 there is a change. It has a code of IN and a bonus > 1000.
For record 8888 there is also a change. It has a code of IN and a bonus > 1000.