PS Query – Decode Function
Posted by jeggent on July 12, 2011
As expected, the previous semester was very busy. The next semester will likely be even busier. However it will also be my last semester. Assuming I am able to complete the two classes, I will graduate this December.
Today I have used the DECODE function for the first time in a PeopleSoft query. I am not sure which databases this function works with, we are on an Oracle DB. Decode allows you to display a specific value based on the contents of a field. For example, if a field contained integers you could output the corresponding letter of the alphabet. The syntax of the function is like:
DECODE(Field, Value 1, Output 1, Value 2, Output 2, Else)
Alphabet example:
DECODE(A.Field, 1, ‘A’, 2, ‘B’, 3, ‘C’, 4, ‘D’, …., 36, ‘Z’, ‘other’)
I am using this function in a report for repackaging aid. I am “decoding” the currently packaged item type to the item type it needs to be replaced with. If there are multiple values that translate to the same output then you may want to consider a CASE statement with an IN list. Maybe I’ll go over that next. Hopefully before Fall semester starts 🙂
Good Luck.
John