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.