Use the views with CURR_TERM in the name if you are only looking for data from the current active term (e.g. currently set to 200409). You do not need to specify a TERM_CODE_KEY in your query. The CURR_TERM views change the first day of classes each term. Queries using CURR_TERM views will run much quicker.
The non CURR_TERM views contain all terms since Banner went live. When using these views always specify TERM_CODE_KEY(s) in your query.
Whenever possible use the columns that contain _CODE in the name in your "where" clause. Generally your queries will run much quicker using a code value (and '=') rather than searching the associated description column using a 'LIKE' operator (e.g. use PRIM_FACULTY_CODE = 'AR' rather than PRIM_FACULTY like 'Faculty of Arts'). Remember that when you use 'LIKE' any typo's will not return the results you expect.
To see only registered students, include the REGD_NON_WITHDRAWN_IND = 'Y' in your queries.
To find out all students who have a Prim_major1 or Prim_major2 in Psychology.
One way of writing this would be:
1) Prim_Major1_code = 'PSY' OR Prim_Major1_code = 'PSY1' OR Prim_major1_code = 'PSY3' OR Prim_major1_code = 'PSY4' OR Prim_Major1_code = 'PSY5' OR Prim_major1_code = 'PSY8' OR Prim_Major2_code = 'PSY' OR Prim_Major2_code = 'PSY1' OR Prim_major2_code = 'PSY3' OR Prim_major2_code = 'PSY4' OR Prim_Major2_code = 'PSY5' OR Prim_major2_code = 'PSY8'
However, combining "like" with the wildcard (%), we could write:
2) Prim_Major1_code like 'PSY%' OR Prim_Major2_code like 'PSY%
In scenerio #2, we would still get all the students in psychology that fall under the codes ('PSY' OR 'PSY1' OR 'PSY3' OR 'PSY4' OR 'PSY5' OR 'PSY8'). Notice how a code was used rather than a description to select those students majoring in Psychology (allows for less errors).
Combining "like" with a wildcard (%) allows for less typos and makes queries much easier to write.
a) Another situation in which combining "like" with a wildcard is useful is when using descriptions in our queries. For example, if one needs all the students who are pursuing a History program whether it is a majors or a joint majors of history with another program, a wildcard would also be useful.
3) Prim_major1 like '%History%' - we would get a list of all students who are pursuing any kind of major in history
Also, any codes (such as PSY, AR etc) or any indicator (Y or N indicator) are case sensitive. If an incorrect case is used for a code or an indicator, it will not return any results.
Using the percent sign (%) as a wildcard is useful when you are writing your own SQL script (since the proper wildcard character used in Oracle’s SQL is the percent sign). However, not all query tools recognize the same wildcard character. Crystal, for example, uses an asterix (*) instead of a percent sign (%) when combining “like” with a wildcard. Therefore, in Crystal, combining like with a wildcard would be as follows: Prim_Major1_code like “PSY*”
When including address columns in your reports,it is recommended that you use the "suggested" address columns. There are three types of address columns:
1) Columns that start with SUGGD_ – These columns contain the student’s mailing address (type 'MA') . If the 'MA' data is null or if the information is outdated (if there is an end date to the address that has passed), then the student’s permanent address (type 'PR') is selected.
2) Columns that start with PR_ – These columns always contain the student’s permanent address (type 'PR').
3) Columns that start with SUGGD_PRINT_ – These columns contain the student’s "suggested" address and are formatted for printing purposes according to Canada post’s standards (blank lines removed , no embedded periods or commas).
Performing Counts in Crystal using Cross-tab [.pdf]