Date Expressions
What does it do: Date Expression to return 1/1/XXXX Begining of year for current year.
Expression: %datein(%SQL(FUNCLIB_HR_DATE_YEAR,%currentdatein) %concat '01-01')
Expression 2: TO_DATE('01-01-'||TO_CHAR(SYSDATE,'YYYY'),'MM-DD-YYYY')
What does it do: Output Next Month (based on current month), e.g. 03/2015:
Expression: TO_CHAR(ADD_MONTHS(SYSDATE, +1),'MM/YYYY')
What does it do: Output Previous Month (based on current month), e.g. 01/2015:
Expression: TO_CHAR(ADD_MONTHS(SYSDATE, -1),'MM/YYYY')
What does it do: Output Last Day of Previous Month (based on current month), e.g. 31-JAN-2015:
Expression: TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)),'DD-MON-YYYY')
What does it do: Output Last Day of Current Month (based on current month), e.g. 28-FEB-2015:
Expression: TO_CHAR(LAST_DAY(SYSDATE),'DD-MON-YYYY')
What does it do: First and Last day of month
Last day of the previous month: LAST_DAY(SYSDATE - 1)
First day of the previous month: LAST_DAT(ADD_MONTHS(SYSDATE, - 2)) + 1)
What does it do: Calculate a person’s age
Expression: FLOOR(MONTHS_BETWEEN(TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD') , 'YYYY-MM-DD'),TO_DATE('table'.'birthdate field', 'YYYY-MM-DD'))/12)
What does it do: Calculates years based on a date.
Expression: ((TO_DATE(SYSDATE)- TO_DATE(C.CMPNY_SENIORITY_DT,'YYYY-MM-DD'))/365)
What does it do: Calculate the difference between two date-time fields
Expression: This will display number of days. If less then 24 hours it shows as a fraction of an hour: TO_DATE(SUBSTR(A.HRS_ROW_UPD_DTTM,1,16),'YYYY-MM-DD-HH24.MI') - TO_DATE(SUBSTR(A.HRS_ROW_ADD_DTTM,1,16),'YYYY-MM-DD-HH24.MI')
To convert it to hours, multiply by 24: (TO_DATE(SUBSTR(A.HRS_ROW_UPD_DTTM,1,16),'YYYY-MM-DD-HH24.MI') - TO_DATE(SUBSTR(A.HRS_ROW_ADD_DTTM,1,16),'YYYY-MM-DD-HH24.MI')) * 24
Format: Why it is useful: Date time stamps are used for auditing and show when data is initially entered and then modified. For Recruiting, we've used this to show how long it took to have an applicant go from Prepared for hire status to actually hired in the system
Case When
What does it do: Case when will return a result when a field is in a condition.
Expression: Case When "field" = "condition" Then "return value or field" ELSE "if condition is not met value" End
Qualifiers:
· = equals
· <> does not equal
· IN In list ('condition','condition')
· > greater than
· < less than
Decode
What does it do: works like an If statement or case when. This is good for doing many if instead of doing many case when statements do like this.
Expression: decode(expression, search, result, default)
Expression 2:
CASE table.fieldname
WHEN 'PS value1' THEN 'your value'
WHEN 'PS value2' THEN 'your value'
WHEN 'PS value3' THEN 'your value'
ELSE 'your value'
END
NID with Dashes
What does it do: Adds the dashes in the NID “123-45-6789”
Expression: SUBSTR(B.NATIONAL_ID,1,3) || '-' || SUBSTR(B.NATIONAL_ID,4,2) || '-' || SUBSTR(B.NATIONAL_ID,6,4)
Combine two fields into one.
Expression: A.FIRST_NAME || A.LAST_NAME
Expression: CONCAT(A.FIRST_NAME,A.LAST_NAME)
Expression: A.FIRST_NAME || ' ' || A.LAST_NAME
Expression: A.LAST_NAME || ', ' || A.FIRST_NAME
Suppress a persons birth year
Expression: TO_CHAR(BIRTHDATE,'fmMon, DD') returns Feb 28
If its not stored as a date format use
Expression: TO_CHAR(TO_DATE (BIRTHDATE,'YYYY-MM-DD'), 'FmMON,DD')
Copy a list of Employee ids into a query,
Use equal to and the expression below
Expression: any('123456','234567')
Text Expressions
What does it do: Use formula if you want to get part of a field.
SUBSTR(A.DEPTID,1,4)
A.DEPTID = the field you wish to get part of. 1 = the position to start getting data. 4 = number of positions to return.
What does it do: Limits number of rows of output in your query
Expression: ROWNUM
When writing a complex query that takes a long time to run, a criteria can be added with the expression ROWNUM not greater than 100 (or any other number) and the results will only show the first 100 results. The query runs faster during initial development and can be removed once it's no longer needed.