You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Looking for PSoft Query Expression help, please

6 replies [Last post]
Ed-D's picture
User offline. Last seen 8 years 42 weeks ago. Offline
Joined: 05/14/2008
Posts: 25

Hello, I hope I am posting this to the right place. I am a PeopleSoft Query user, currently version 8.x, I believe (they keep me in the dark on things like thst.) My access to PSoft is web-based, and I cannot go in and modify SQL code. I can, and have written Expressions though. And that is where I need help.

Performance ratings for our employees include G and L ratings (in the format of G1L2 for example.) An employee is considered a high performer if the numeric values in their ratings total 4 or less (so G2L2 would equal 4.)

With an Expression, I can retrieve, and add up each set of G and L values. However, there is an issue with historic data for some employee records. Instead of G and L ratings, only "G" ratings were used several years ago for some employees.

The Expression I wrote works fine until I hit an old rating that has only a "G" rating, and then I get a blank (null?) value returned. Here is the Expression:

Expression Properties...
Expression Type: Number
Length: 1
Aggregate Function (no)
Decimals: (no)

Expression Text:
SUBSTR(REVIEW_RATING,2,1) + SUBSTR(REVIEW_RATING,4,1)
-----------------------------------
So, a rating of G2L3 returns a value of 5. No problem.

But, a "G" only rating of, say, "G2" returns nothing, zip, nada. Not even a value of 2.

And here is the second part - If I can get the G rating to come out, I would like for PSoft to take that value and multiply it by 2 - that puts the "High Performer" value from the G only ratings on par with the ratings including both G and L ratings (a G2 would return 4, and effectively be the same as a G2L2 rating.

Can anyone offer help? Or does this go beyond what I can do through my PSoft access situation?

Many thanks,

Ed

CompShack's picture
User offline. Last seen 5 years 27 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Re: Looking for PSoft Query Expression help, please

Hi Ed-D, welcome to the site!

But, a "G" only rating of, say, "G2" returns nothing, zip, nada. Not even a value of 2.

The problem is w/ your expression SUBSTR(REVIEW_RATING,2,1) + SUBSTR(REVIEW_RATING,4,1). When you have only G rating the expression is trying to add 2 (for G2) and a Null value so 2+null = nothing. In other words, the database gets confused when you are trying to add a number to a null. It just doesn't know what to do. Here is how you can solve the problem.

Re-write your expression to look like this:

substr(REVIEW_RATING,2,1) +  NVL(substr(REVIEW_RATING,4,1), substr(REVIEW_RATING,2,1))

Let me explain what NVL does. It is telling the database to substring the field value and if no data found then go ahead and assign the same value as the left side of the addition. This way you are getting your G2 value added to itself, the multiple 2 that you wanted :)

Does it make sense?

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Ashar's picture
User offline. Last seen 4 years 5 weeks ago. Offline
Joined: 03/26/2008
Posts: 227
Re: Looking for PSoft Query Expression help, please

Shouldn't it be substr(REVIEW_RATING,2,1) + NVL(substr(REVIEW_RATING,4,1), 0) ?

Peace,

Ashar

CompShack's picture
User offline. Last seen 5 years 27 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Re: Looking for PSoft Query Expression help, please

nope, Ed-D wants to multiply the rating by 2 if only a "G" rating, so I'm adding the G rating to itself if the L rating doesn't exist.

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Ed-D's picture
User offline. Last seen 8 years 42 weeks ago. Offline
Joined: 05/14/2008
Posts: 25
Re: Looking for PSoft Query Expression help, please

Hi CompShack,

The solution you provided works perfectly! Thank you very much!

And Ashar, thank you for your input as well.

This is a wonderful forum, thank you so much for the help. We recently upgraded from PSoft ver. 7.5 to ver 8.8 (I think.) Plus we have had some minor changes made over the years, one of which is the Ratings Value issue in this thread. Each time a change is made, I experience some different issues/challenges. I like to be able to figure things out on my own, but I hope the forum does not mind if I post inquiries where I cannot figure things out. Of course, I am willing to offer any help to others, assuming I know the answer!

Again, I do thank you all!

Ed

Ashar's picture
User offline. Last seen 4 years 5 weeks ago. Offline
Joined: 03/26/2008
Posts: 227
Re: Looking for PSoft Query Expression help, please

oh..ok..
i guess i didn't read the complete post.. :-)

Peace,

Ashar

CompShack's picture
User offline. Last seen 5 years 27 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Re: Looking for PSoft Query Expression help, please

You are more than welcome Ed

I like to be able to figure things out on my own, but I hope the forum does not mind if I post inquiries where I cannot figure things out. Of course, I am willing to offer any help to others, assuming I know the answer!

Its all about team work and I'm glad CompShakers were able to help!

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog