- 4 Posts
- 527 Views
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
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
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
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.