Using Prompts in Case Expressions

From TekiWiki
Jump to: navigation, search

PeopleSoft Query

Issue using a Prompt in Case Expression at PeopleTools 8.49

(ED: Does this happen on other PeopleTools versions?)

The following case statement in PeopleSoft Query would not give a correct value:

case 
    when DEPTID like :1 then FTE
else
    0
end

The problem was that the entered prompt value was padded with spaces to the length of the prompt. So the solution was using rtrim (Oracle):

case 
    when DEPTID like rtrim(:1) then FTE
else
    0
end

In SQL Server, rtrim(:1,' ') would resolve the issue (provided there were no leading spaces).

More About Functions

UsingFunctionsInPeopleSoftQuerySmall.jpg For more information about functions in PeopleSoft Query and some other interesting stuff too, read our eBook:

Using Functions in PeopleSoft Query