Geeks on Campus

Get the username from from an e-mail address

UPPER(SUBSTR(A.EMAIL_ADDR,0,(INSTR(A.EMAIL_ADDR,’@’)-1)))
I’m using this to join to the roll name on PSROLEUSER  so I can then get the user’s actual name.

Expression for First Generation (C = ISIR_PARENT)

CASE WHEN (C.FATHER_GRADE_LVL IN (‘ ‘, ‘4’) AND C.MOTHER_GRADE_LVL IN (‘ ‘, ‘4’)) THEN ‘Unknown’
WHEN (C.FATHER_GRADE_LVL IS NULL AND C.MOTHER_GRADE_LVL IS NULL) THEN ‘Unknown’
WHEN (C.FATHER_GRADE_LVL = ‘3’ OR C.MOTHER_GRADE_LVL = ‘3’) THEN ‘No’
ELSE ‘Yes’
END

Decode, Substr, To_number and Least

CASE WHEN L.OFFER_BALANCE > 0
THEN 0
WHEN K.OFFER_BALANCE > 0
THEN DECODE(E.VARIABLE_CHAR4,’NO NEED’,LEAST(250,TO_NUMBER(SUBSTR(A.FLD1,12,5))), ‘HIGH’,LEAST(500,TO_NUMBER(SUBSTR(A.FLD1,12,5))), ‘MODERATE’,LEAST(500,TO_NUMBER(SUBSTR(A.FLD1,12,5))), ‘LOW’,LEAST(500,TO_NUMBER(SUBSTR(A.FLD1,12,5))), 0)
ELSE DECODE(E.VARIABLE_CHAR4,’HIGH’,LEAST(1200,TO_NUMBER(SUBSTR(A.FLD1,12,5))), ‘MODERATE’,LEAST(1000,TO_NUMBER(SUBSTR(A.FLD1,12,5))), ‘LOW’,LEAST(750,TO_NUMBER(SUBSTR(A.FLD1,12,5))), ‘NO NEED’,LEAST(500,TO_NUMBER(SUBSTR(A.FLD1,12,5))), 0)
END

Here is an interesting expression that uses a few different functions. If the student has already been awarded in this term (l.offer_balance > 0) then the result is 0. If the student has previously been awarded (k.offer_balance > 0), then a decode is used to evaluate the value in e.variable_char4. If there is no current or prior offer_balance then a different version of the decode statement is used. The decode works like this:

	Look in this field	If this is the value	This is the result
DECODE	(E.VARIABLE_CHAR4,	‘HIGH’,	LEAST(1200….

LEAST(1200,TO_NUMBER(SUBSTR(A.FLD1,12,5)))
The least statement returns whichever is lower, the number (ex. 1200) or the result of TO_NUMBER(SUBSTR(A.FLD1,12,5)). The substr statement is pulling 5 characters out of the field A.FLD1 starting at position 12. Then to_number converts that to a number for comparison in the least function.

MOD and FLOOR for a file extract

This is for an odd case where a file layout calls for no fractional digits on whole number amounts. ex. 123.45 = 123.45 : 678.00 = 678

CASE WHEN MOD(A.DISBURSED_BALANCE,1) > 0
THEN TO_CHAR(A.DISBURSED_BALANCE)
ELSE TO_CHAR(FLOOR(A.DISBURSED_BALANCE))
END

Getting Calendar Year from Term Code

CONCAT(’20’,SUBSTR(C.EXP_GRAD_TERM,2,2))

Calculating age at time of application

MONTHS_BETWEEN(B.ADM_APPL_DT, H.BIRTHDATE) / 12

Removing non-number characters from phone number to compare ISIR phone number to Campus Community

CONCAT(SUBSTR(H.PHONE,1,3),CONCAT(SUBSTR(H.PHONE,5,3),SUBSTR(H.PHONE,9,4)))

Reformatting a 2020/11/06 date to 11/06/2020
TO_CHAR(TO_DATE(A.DT_APP_RECEIVED),’MM/DD/YYYY’)

Compare a date field to a date last year
I.COMM_DT > TO_DATE(CONCAT(TO_NUMBER(:1)-1,’-02-01′),’YYYY-MM-DD’)

Get the date from a DateTime field
TO_CHAR(CAST((A.COMM_DTTM) AS TIMESTAMP), ‘MM/DD/YY’)
Also check out the EXTRACT function if you just need one element (day, month, etc).