## PS Query Expressions

Posted by jeggent on 29th June 2020

**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)))

Posted in PS Query | Comments Off on PS Query Expressions