# Geeks on Campus

## PS Query Expressions

Posted by jeggent on June 29, 2020

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’
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:

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

Calculating age at time of application

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