This section is for query writers; it contains sample expressions and how they may be used.
Sql to turn a time formatted field into a 12 hour format field as in query jusds010c.
CASE WHEN (SUBSTR(E.MEETING_TIME_END,1,2) = '12' OR SUBSTR(E.MEETING_TIME_END,1,2) = '24') THEN LPAD (TO_CHAR(MOD(SUBSTR(E.MEETING_TIME_END,1,2),12)+ 12),2,'0')ELSE LPAD (TO_CHAR(MOD(SUBSTR(E.MEETING_TIME_END,1,2),12)),2,'0') END || ':' || SUBSTR(E.MEETING_TIME_END,4,2)
Working with a query in which a date/time stamp field needed to be part of the selection criteria. Specifically, needed the scheduled query to run twice a day and pull data which had occurred within the past 12 hours. This calc works. TO_TIMESTAMP (TO_CHAR (SYSTIMESTAMP - 24/24, 'MM-DD-YYYY HH24'), 'MM-DD-YYYY HH24')
Calculating age and other date expressions First of all, all dates in query are turned into character fields. So any manipulation of them as dates, requires that they be turned BACK into a date. Sounds strange, and looks even more strange.
Expression to return a date in the format: December 12, 2012: TO_CHAR(TO_DATE(A.TERM_BEGIN_DT),'MonthDD, YYYY') as a 20 character expression
Find the difference between a date and the system date: ROUND(MONTHS_BETWEEN(SYSDATE,TO_DATE(A.CMPNY_SENIORITY_DT,'YYYY-MM-DD'))/12,1)
A case statement which looks at the term field, and if the term is a summer term (last digit is a 5) sets the expression equal to the number of days between the current system date and the course end date. case when substr(D.STRM,4,1) = '5' then TO_DATE(C.END_DT) - SYSDATE else 999 end
Expression to return the number of days from the current system date and a specific database field of type date: TO_DATE(field_name) – SYSDATE. Make sure to accommodate negative values in the returned expression
or this syntax works to determine the # of years between a date and todays date: (SYSDATE-TO_DATE(A.HIRE_DT,'YYYY-MM-DD'))/365
to add a number of days to a date field: %dateadd (A.hire_dt,90)
to subtract two dates: (TO_DATE(A.ACTION_DT,'YYYY-MM-DD')-TO_DATE(A.ORIG_HIRE_DT,'YYYY-MM-DD')) BUT Oracle ends in error if you then try to use this on the criteria tab, best to include the field and delete rows you do not want, if that is feasible
I was looking to determine who would turn 65 within the next 3 months. So I simply took today's date and subtracted the number of days that it would take for them to be 65 years old. AND B.BIRTHDATE BETWEEN SYSDATE - 23742 AND SYSDATE – 23648
Needed to find the number of days between sysdate and a field of type datetime. Expression needs to be a number, syntax like this: TRUNC(SYSDATE)-TO_DATE(SUBSTR(A.ACTION_DTTM,1,16),'YYYY-MM-DD-HH24.MI') Which is basically taking the 16 characters of the date/time field, reformatting it to be a true date in 24 hour format, and subtracting it from todays date. The TRUNC function against sysdate causes it to use midnight as the time instead of the actual current time.
Extracting time from a date/time field:
Getting the time portion of a date/time field: If you create a character expression of length 10 with Expression Text: SUBSTR(A.LASTUPDDTTM,9,2) || '/' || SUBSTR(A.LASTUPDDTTM,6,2) || '/'|| SUBSTR(A.LASTUPDDTTM,1,4) You can strip the Date part out as 07/12/2016 A second Expression using a character expression of length 11 with Expression Text: TO_CHAR(TO_TIMESTAMP(SUBSTR (A.LASTUPDDTTM,12,11),'HH24.MI .SS.FF'),'HH12:MI:SSAM') You can strip the time part out as 02:37:49PM If the leading zero is an issue extending the expression into a case expression where if the first character is a 0 then return the string after the zero instead as: CASE WHEN SUBSTR(TO_CHAR(TO_TIMESTAMP(SUBSTR (A.LASTUPDDTTM,12,11),'HH24.MI .SS.FF'),'HH12:MI:SSAM'),1,1) = 0 THEN SUBSTR(TO_CHAR(TO_TIMESTAMP(SUBSTR (A.LASTUPDDTTM,12,11),'HH24.MI .SS.FF'),'HH12:MI:SSAM'),2,10) ELSE TO_CHAR(TO_TIMESTAMP(SUBSTR (A.LASTUPDDTTM,12,11),'HH24.MI .SS.FF'),'HH12:MI:SSAM') END.
Another option is to use a 15 character expression: %TimeOut(A.LASTUPDDTTM).
TO_CHAR(TO_TIMESTAMP(A.DTTM_STAMP_SEC,'YYYY-MM-DD-HH24.MI.SS.FF'),'HH12:MIam') In your expression, the type must be character set to a length of 26. The result rendered should be this 03:10pm
How to get extract only Month from a date field. The Query tool automatically converts date fields into character fields, so you'll need to turn it back to a date first, then convert it back to a character field. This is an expression I've used that works: TO_CHAR(TO_DATE(A.GIFT_DT,'YYYY-MM-DD'),'fmMONTH')
Used to extract the number of weeks (1-52) the system date is so can use in an expression: to_number(to_char(to_date(SYSDATE), 'WW'))
Needed an aggregate expression to sum fiscal year $ amount field in fprd, could not find the proper syntax, so adding it here for future reference. 25 digit signed numeric aggregate function: SUM(case when B.PYMNT_DT BETWEEN '2012-07-01' and '2013-06-30' then B.PYMNT_AMT else 0 end). In this query the date used in the expression was NOT selected as a field, so no date conversion was necessary. Actual work included a total of 4 expressions (one for each of three FY and a 4th for the overall total) and having criteria.
References for SQL commands and other query references
Need to write a numeric field without decimals to a seven digit number with leading zeros: lpad((ded_bal * 100),7,’0’)
Will the order of the table-names in the FROM clause, influence the execution speed of the query? This rule is for when the oracle optimizer is stuck for a good idea. The Oracle optimizer works in the following manner. It looks at each of the WHERE clauses and assigns the tables concerned a number based on the type of predicate e.g. field = 'const' or field(+) = field. It then chooses the table with the lowest score as the driving table. But, and its a big one, if more than one table has the same lowest score then it chooses the last table in the FROM list to be the driving table.
Case
The technical syntax for this expression is (CASE WHEN criteria1 THEN return value1 WHEN criteria2 THEN return value2….. THEN default value)
CASE WHEN A.DEPTID = '100113' THEN 'A' WHEN A.DEPTID = '100211' THEN 'B' WHEN A.DEPTID = '300159' THEN 'C' ELSE 'D' END
Case statement to change an @dukes email address in email_addresses to jmu: CASE WHEN instr(B.EMAIL_ADDR,'dukes',1)>0 THEN SUBSTR(B.EMAIL_ADDR,1,instr(B.EMAIL_ADDR,'dukes',1)-2) || '@jmu.edu' ELSE B.EMAIL_ADDR END
Case statement to convert the 4 character term field to the FA aid year. (CASE WHEN (substr(G.STRM,4,1) IN ('5','8')) THEN '20' || TO_CHAR(TO_NUMBER(substr(G.STRM,2,2)+1))
ELSE '20' || substr(G.STRM,2,2)END)
Query does NOT like a case statement to be in this format: when A.deptid=’100211’ it likes the format: a.deptid = ‘100211’ complains if the spaces are missing.
First generation college student. There are (at least) two methods to determine if a student is considered a first generation college student. The preferred method (as admitted by the office of undergraduate admissions) is the FA/ISIR version.
Using admissions data. See sample query named JADTEMPLATE_FIRST_GENERATION. You must outer join to the RELATIONSHIPS record twice, once using the PEOPLE_RELATION IN F/SF and HIGHEST_EDUC_LVL IN C,D then again for people_relation IN M/SM then either of these expressions will work. As of 8/1/2017, although the below expression is true, you can also add the record named person_comment to your query where admin_function=ADMP and cmnt_category=ADFGEN.
case when D.HIGHEST_EDUC_LVL IN ('C','D') and E.HIGHEST_EDUC_LVL IS NULL then 'Y' when D.HIGHEST_EDUC_LVL IN ('C','D') and E.HIGHEST_EDUC_LVL IN ('C','D') then 'Y' when E.HIGHEST_EDUC_LVL IN ('C','D') and D.HIGHEST_EDUC_LVL IS NULL then 'Y' else 'N' end
CASE WHEN D.HIGHEST_EDUC_LVL || E.HIGHEST_EDUC_LVL = 'D' THEN 'First Gen' WHEN D.HIGHEST_EDUC_LVL || E.HIGHEST_EDUC_LVL = 'DC' THEN 'First Gen' WHEN D.HIGHEST_EDUC_LVL || E.HIGHEST_EDUC_LVL = 'DD' THEN 'First Gen' WHEN D.HIGHEST_EDUC_LVL || E.HIGHEST_EDUC_LVL = 'C' THEN 'First Gen' WHEN D.HIGHEST_EDUC_LVL || E.HIGHEST_EDUC_LVL = 'CD' THEN 'First Gen' WHEN D.HIGHEST_EDUC_LVL || E.HIGHEST_EDUC_LVL = 'CC' THEN 'First Gen' ELSE D.HIGHEST_EDUC_LVL || E.HIGHEST_EDUC_LVL END
Using FA/ISIR data. See sample query named JSR_FIRSTGENERATION.
If you want to do an outer join from table A to table B and table B is effective dated, create the following in the query criteria AND (B.EFFDT Effdt <= Current Dt OR B.EFFDT IsNull )It's not as easy to read as the (+) syntax but it gives the same results and it allows you to use subqueries in your outer join.
Used the following expression in a query when I needed to look for “bad” characters in an email address field. The command transforms all of the characters that are “bad” into a tilde character. instr(translate(B.EMAIL_ADDR,'!?#$%;;<>|\[]','~~~~~~~~~~~~~'),'~'). I then added a row to the criteria tab stating the instring operator to locate a tilde character >0, meaning, if any of the translated characters became a tilde, then there was at least one “bad” character in the email address
Substring
The technical syntax for this formula is SUBSTR (character, starting point, length to return)
The most basic form of this expression creates a field named “course” from the subject and catalog number fields. The syntax takes into account the fact that the 1st character of the catalog number is actually blank/null. SUBSTR(A.SUBJECT,1,5) || SUBSTR(A.CATALOG_NBR,2,4). It would appear that the delivered format of the catalog_nbr field allows the format: nnnnxxxxxx where the first 4 places have to be numeric. IF a course is entered with alpha characters at the beginning of the catalog_nbr field, it appears to shift the values to the right. In these cases, when using the substr function, you must also use the TRIM function to remove the leading spaces.
In string. Returns a number equal to the position in the text field where a string is located. A value of zero indicates the absence of the string. The in string command is sometimes used in conjunction with other commands as in the case of the above example whereby the JMU email address is checked for the string=”dukes” and that portion of the email address is removed for instructors. The syntax is instr(FIELDNAME,'String_To_Search_For',starting_position). So if the expression: instr(B.EMAIL_ADDR,'dukes',1) returned a value greater than 0, it indicates the email address contains the letters “dukes” and a substr expression could be used to remove same.
Count distinct. Set the expression field type to numeric, and the expression to something like this: COUNT(DISTINCT A.ACAD_CAREER || A.ACAD_YEAR). The distinct operator will be applied to whatever is within the parenthesis. Very useful when attempting to get distinct counts when the query results return multiple rows of data by, say, emplid.
Concatenation
This action can be achieved by using a special operator or symbol of ||. On your keyboard the | key is the capital of the \ key which is located above the Enter key.
Decode. In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.
The syntax for the decode function is: decode( expression , search , result [, search , result]... [, default] )
expression is the value to compare.
search is the value that is compared against expression.
result is the value returned, if expression is equal to search.
default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).
How to create expressions that will allow a user to prompt for a particular value or get all values
Create a DECODE expression for the right hand side of a criteria. Such as AND K.ITEM_TYPE = DECODE(:25, ' ', K.ITEM_TYPE, :25)
In the above sample a user has the choice of entering a specific value for item type. If the user leaves the prompt blank the criteria will logically be read as AND K.ITEM_TYPE = K.ITEM_TYPE and will pick up all values of this field. If the user enters a specific value the criteria will logically be read as AND K.ITEM_TYPE = :25 (prompted value).
How to create expressions that will allow a user to prompt for a particular value or get all values of a date field
Create a NVL expression for the right hand side of a criteria.AND A.ADM_CREATION_DT = NVL(TO_DATE(:1,'YYYY-MM-DD'),( A.ADM_CREATION_DT)
In the above sample a user has the choice of entering a specific value for the Admission Data Creation Date. If the user leaves the prompt blank the criteria will logically be read as AND A.ADM_CREATION_DT = A.ADM_CREATION_DT and will pick up all values of this field. If the user enters a specific value the criteria will logically be read as AND A.ADM_CREATION_DT = TO_DATE(:1,'YYYY-MM-DD') (prompted value).
Queries used for drilling purposes. The following queries are defined in a manner to allow them to be used by other queries to be drilled TO. In all cases, the queries being drilled TO have prompts which must be configured in the expression being used to drill FROM. The fields used to drill from must be selected as a field in the from query.
Jusds001: class roster. Prompts for term, subject and catalog number (in a single field) and an optional course section number. Returns all students enrolled in the prompted for course.
All courses ever taken or transferred. Jusds024b: prompts for a single emplid, returns all courses ever taken at JMU as well as those transferred into JMU for credit.
Current schedule. Jusds024j: prompts for a term and a single emplid. Returns the students schedule for the given term.
Academic standing. JUSDS006_ACADSTAND. Prompts for a single emplid, returns all academic standing data for all terms; probation, suspension, good standing.
Mid-term grades. JUSDS006_MIDTERMGRADES. Prompts for a single emplid, returns any/all mid term grades for the given student.
Holds. JUSDS006_HOLDS. Prompts for a single emplid, returns all holds for the given student.
Requirements. JUSDS006_REQUIREMENTS. Prompts for a single emplid, returns all requirements from the last degree progress run and whether each requirement is completed or failed.
Major/minor history. JUSDS006_PLANHISTORY. Prompts for a single emplid, returns all plan changes in descending order by effective date.
Test scores: JUSDS006_TESTSCORES. Prompts for a single emplid, returns ALL test scores for the given student.
Gened courses. JUSDS006_GENED. Prompts for a single emplid, returns all courses with an attribute=GNED for the given student.
SYS_CONTEXT returns the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.
For a list of values see: http://psoug.org/reference/sys_context.html or http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm
The article was asking if there was a way for the query tool to return a report header. The response uses an expression and Oracle syntax to create such a field which contains the userid, database name and system date as the header for the report. I tested this in hprd and it worked fine. See URL’s above for other parameters for the command.
LISTAGG function. ONE way it may be of value is when you desire an entire sequence of rows of data to be returned in a single expression. The expression you create MSUT be set to be an aggregate function.
Want all acad plan data for a student returned in one expression field? Create an alpha expression, make sure it is long enough to contain values. listagg(A.DESCR, '*') within group (order by A.PLAN_SEQUENCE). Will put into the expression all plans separated by an asterisk and sorted by the plan sequence number. The A.DESCR can be ANY valid expression, concatenating fields, etc.
The generic form is: listagg(any logical expression including decodes, concatenated fieldsw, etc.),'THE SEPARATOR FIELD') within group (order by FIELD NAMES to sort on)
listagg(decode(D.ACAD_PROG,'UGEN','UNDER ','OVER ') || C.DESCR, '*') within group (order by C.DESCR,E.PLAN_SEQUENCE).
Return ALL courses taken into a single field: listagg(B.SUBJECT || substr(B.CATALOG_NBR,2,4) || '-' || A.CRSE_GRADE_OFF, '~') within group (order by B.SUBJECT,B.CATALOG_NBR)
Scheduled recurring objects.
JUA002 series. Notes embedded in ITR06634. Each of the eight queries in sprd (jua002,a,b,d,e,f,g,h) are scheduled to run each Sunday at 7 AM emailing the results of the query in an xls file as an attachment. Five of the eight queries include prompts since the data is term/year specific. When the term changes, need to cancel these five scheduled queries (jua002a,e,f,g,h), and reschedule them with the new term values. Jua002g (MRD data) uses a term range due to the fact that 95% of the students taking the MUAP class indicating they are a member of the MRD's take the course in the Fall, the remaining 5% in the Spring. Values used to schedule the queries:
A separate run control exists for each query named the same as the query
The recurrence value must be set to UAWeeklyQuery
Type and format must be changed to email/xls
Follow the distribution link, set appropriate values for subject (JUA002 query weekly results) and text (Attached find the results from the query in sprd named jua002, contact desmitpa@jmu.edu if there are questions)
Remove all eids from the distribution; entering eids would require the users log into sprd and navigate to the report manager panel to retrieve the results
Enter the following in the email address list: UAQA@JMU.EDU;KINGBD@JMU.EDU
Facilities Management (lock shop). There are two queries in sprd and two in hprd as follows
Jfm02 in sprd; run control by same name. Type and format need to be set to Email/Text (they prefer csv file format). On the distribution tab, set the subject= New students from Peoplesoft SA and the email body text= The atatched file contains a list of new students from the PepleSoft SA system. The results are from a query named jfm02. Contact Pete DeSmit if questions. Set the email address list= fmlockshop@jmu.edu. Leave the distribute to default if you desire results of the daily run. Uses recurrence definition=10PM RUN_JCC0028 as it was already defined as a 7 day a week run at 10 PM.
Jfm02_terms in sprd, run control by same name. Type and format need to be set to Email/Text (they prefer csv file format). On the distribution tab, set the subject= Withdrawn students from Peoplesoft SA and the email body text= The atatched file contains a list of withdrawn students from the PepleSoft SA system. The results are from a query named jfm02_terms. Contact Pete DeSmit if questions. Set the email address list= fmlockshop@jmu.edu. Leave the distribute to default if you desire results of the daily run. Uses recurrence definition=10PM RUN_JCC0028 as it was already defined as a 7 day a week run at 10 PM.
Jfm01 in hprd. All employe data for lock shop. Run control by same name. Type and format must be Email/Text. Distribution email subject= New hires from PeopleSoft HRMS system. Email body= The attached file contains new hires for today from the PeopleSoft HRMS system. The results are from a query named jfm01 in HRMS. Please contact Pete DeSmit if there are questions. Email address list= fmlockshop@jmu.edu. Scheduled to recur daily via a recurrence definition=JFM01
Jfm01_terms in hprd. Terms for lock shop. Run control by same name. Type and format must be Email/Text. Distribution email subject= Terminations from PeopleSoft HRMS. Email body= The attached employees were termianted effective today. These results are from a query named jfm01_term in the HRMS system. Contact Pete DeSmit for further details. Email address list= fmlockshop@jmu.edu. Scheduled to recur daily via a recurrence definition=JFM01
BIP reports used for building coordinators list serv. Cindy Leake (FM lock shop) maintains a list serv with approx. 100 email addresses of the building coordinators. There are two BIP reports used to send email with a PDF attached to this list daily. In hprd, the BIP report is named jfm01_terms. In sprd, the BIP report is named jfm02_terms. Both use a recurrence named BLDCOORD which recurs daily at 10 PM. See the respective run controls for the scheduled BIP reports for the email subject and email body.
Housing. There are two queries in sprd scheduled by Dee Nilsen to run and recur daily.
JHS_EREZLIFE_DATA. Includes a prompt for the current term and current housing period. Housing refers to this as programming and student data. Although desmitpa has run controls by the same name as the queries, not sure what values Dee uses as run controls are userid based and cannot be viewed by other users. Type and format must be set to File/Text and the output destination= /udata/sa90/ as there are CRON scripts which expect to find the files on the server for import into the erezlife application.
JHS_EREZLIFE_DATA1. Includes a prompt for the current term and current housing period. Housing refers to this as staff select and applicant data. Although desmitpa has run controls by the same name as the queries, not sure what values Dee uses as run controls are userid based and cannot be viewed by other users. Type and format must be set to File/Text and the output destination= /udata/sa90/ as there are CRON scripts which expect to find the files on the server for import into the erezlife application.
Affiliates. Query and BI PUBLISHER report named jhr_BI publisher014 in hprd. Uses bursting to send email to the sponsor of an affiliate whose JOB row is terminating 14 days in the future from the run date so they can take action so affiliate does not lose any services. Meant to be run/recur daily by Chris Jones in payroll due to fact payroll is the office which receives and processes affiliate renewal paperwork to key new future dated JOB row for terminations. Logic in expression follows the affiliate policy which states how long each affiliate company can receive services. Scheduled to recur daily via a recurrence definition=JFM01.
Student athletes. Scheduled to recur daily M-F at7 AM via a recurrence named jad_athlete. Sends data to Suzann Meyerhoffer and Steve Henderson meyerhsl and hendersw. Underlying BI publisher report named jad_athlete3. Jad_athlete2 cannot be scheduled because it prompts for emplids of the athletes. Decided to schedule the report after Suzann called to say the report would no longer run interactively as it was timing out.
Cancelled housing contracts: as of 7/3/2014, created a query in sprd named jcs007, selects cancelled housing contracts for today, run daily via a recurrence named 10PM RUN_JCC0028 as it was already defined as a 7 day a week run at 10 PM. Spreadsheet is emailed to Diane Nash. 3/14/2016: spoke with Diane Nash after a tools upgrade, this scheduled recurring query is no longer required. They run the query ad hoc, so no need for a scheduled query.
Mymadison (aka pprd) recurring queries:
5 AM: jidm01 sent to Mamata and Steve Kozup. Contains a hard coded list of admit terms, used by IDM to determine if the proper roles exist in pprd/sprd to allow recent matriculated students to log into the systems. On 1/25/2018 Mamata emailed asking that the recurring scheduled query be cancelled.
8 AM:
jus003 sent to bryanra, rabieta, kiracola, barstora and kozupsj uses a run control named jus003.
Jus001 sent to desmitpa, kozupsj and bryanra uses a run control named jus001.
Jus002 sent to bryanra and kozupsj uses a run control named jus00028AM
Jus005 sent to bryanra and kozupsj uses a run control named jus0005AM
Noon:
Jus002 sent to bryanra and kozupsj uses a run control named jus0002
Jus005 sent to bryanra and kozupsj uses a run control named jus0005
Midnight:
Jus001 sent to desmitpa, kozupsj and bryanra uses a run control named jus001.
Jus002 sent to bryanra and kozupsj uses a run control named jus00028AM
Jus005 sent to bryanra and kozupsj uses a run control named jus0005AM
Advisors
jcap17a and jcap17b. Uses recurrence named jcap4am. As of 5/12/2016, desmitpa is the user who is running and rescheduling these two BIP reports. The list of advisor SA emplids is provided by CAP each year and added to a student group named FYAD. Email subject line: List of your advisees who have been dropped. Email body: The attached spreadsheet represents a list of advisees who were dropped from your list effective yesterday. Questions about this email and it's content should be directed to Pete DeSmit desmitpa@jmu.edu 540-568-3605 with a reference to data analysis objects named jcap17a.
COB/MBA program. As of 2/19/2018 query jmba06 is scheduled to run weekly on Monday’s @ 10:30 AM using a recurrence definition named MBA and a run control named jmba06. Results are emailed to Tracie Esmali
