Forums / Categories / PeopleSoft / Technology / PeopleSoft Query - EXACT expression

    PeopleSoft Query - EXACT expression

    I am trying to compare two fields to see if they are identical and would like for the expression to return a "True/False" result.  I found the "Exact" function below on the Oracle site under PeopleCode Built-in Functions but I can't get it work.  I'm trying to compare the METHOD field on the BOOK table to see if it is different between the TAX and FINANCIAL books.  My query brings the BOOK table in twice, one filtered on the TAX book and the other on the FINANCIAL book.  I've tried 'Exact(A.METHOD, B.METHOD) but i get an error saying  "EXACT": invalid identifier, which makes me think I've got the wrong function.  Any help would be greatly appreciated.

    Syntax

    
    Exact(<var>string1</var>, <var>string2</var>)
    

    Description

    Use the Exact function to compare two text strings and returns True if they are the same, False otherwise. Exact is case-sensitive because it uses the internal character codes.

    Thank you,

    Chris Timek

    If I understand the question correctly, you are trying to use a PeopleCode function in a PSQuery expression.

    This is not possible. PSQuery expressions need to be based on your database platform's SQL functions.

    Thanks Linda,

    I was hoping there was an equivalent that would work in PSQuery.  I'm checking with our PS Admin to see if he can provide a list of valid expressions.

    Chris

    Hello Chris,

    Without knowing your DB platform, it is difficult to give you a precise answer. For the sake of providing some sort of an example, I'll just assume you are using Oracle, but I'm sure other databases have something similar.

    Just use the function DECODE(). You can make the "True/False" return values be anything you want.

    In this example, I return a 1 for 'True' and a 0 for 'False'
    decode('ABC','123',1,0)

    Translation of example above:

    ​IF 'ABC' = '123' THEN

    Return a value of 1     /* True */

    ELSE

    Return a value of 0     /* False */

    decode('ABC','ABC','TRUE','FALSE')

    Translation of example above:

    ​IF 'ABC' = 'ABC' THEN

    Return a value of 'TRUE'

    ELSE

    Return a value of 'FALSE'

    In your case, you would simply replace my hardcoded strings with your column names like so:
    DECODE(book.method,tax.method,'T','F')

    You can do something similar using the Searched CASE method:
    CASE WHEN book.method = tax.method THEN 'T' ELSE 'F' END

    Hope this helps!

    Javier

     

Looks like your connection to Quest Oracle Community was lost, please wait while we try to reconnect.