Categories
- All Categories
- 101 Oracle Analytics News
- 9 Oracle Analytics Videos
- 14.3K Oracle Analytics Forums
- 5.3K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 56 Oracle Analytics Trainings
- 60 Oracle Analytics Data Visualizations Gallery
- 5 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
Compare two fields in analysis

Summary
How to compare two fields within an analysis
Content
How do I compare two fields in an analysis. I need "Workforce Management - Person Real Time"."Person Legislative Information"."Country" equal to "Legal Employer"."Legislation"
Thank you
Comments
-
Ruth,
Within an analysis this can be accomplished by a CASE statement, where 'True' is the behavior if there is a match and 'False' if there is not:
CASE WHEN "Workforce Management - Person Real Time"."Person Legislative Information"."Country" = "Legal Employer"."Legislation" THEN 'True' ELSE 'False' END
If you are filtering on this criteria you will want to check "Convert this filter to SQL" and then set the SQL filter to "Workforce Management - Person Real Time"."Person Legislative Information"."Country" = "Legal Employer"."Legislation"
0 -
I entered the case setting a filter on one of the two columns and using convert to SQL. I then put in the "Workforce Management - Person Real Time"."Person Legislative Information"."Country" = "Workforce Management - Work Relationship Real Time"."Legal Employer"."Legislation" When I view the results I receive a huge error.
Odbc driver returned an error (SQLExecDirectW).Error Details
Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65PState: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)State: HY000. Code: 43119. [nQSError: 43119] Query Failed: (HY000)State: HY000. Code: 14023. [nQSError: 14023] None of the fact sources for Dim - Legal Employer.Effective End Date are compatible with the detail filter [Dim - Person Legislative Information.Legislation Code Meaning = Dim - Legal Employer.Legislation]. (HY000)State: HY000. Code: 14081. [nQSError: 14081] You may be able to evaluate this query if you remove one of the following column references: Dim - Legal Employer.Effective End Date, Dim - Legal Employer.Effective Start Date, Dim - Legal Employer.OrgUnitClassificationId (HY000)0 -
For testing purposes, does the analysis work if you remove Legal Employer.Effective End Date (or one of the two other fields suggested: Legal Employer.Effective Start Date or Legal Employer.OrgUnitClassificationId)? Adding CAST to the filter fields may change the behavior but you'll want to test removing those fields first.
0 -
Yes if I remove the filter the analysis works fine. I'm attaching a screenshot of my filter, perhaps I'm doing something incorrectly.
Thanks,
Ruthie
0 -
Please add a screenshot of entire Advanced SQL Filter.
I tested a similar situation that worked. Please refer to the following screenshot. I guess you are missing "='Ture'" or "='False'" criteria based on your need.
If this answers your question than please mark this question as answered.
0 -
You should be able to set the fields equal to each other without the case statement using the Advanced SQL Filter
0 -
The applied filter would look like this:
0 -
CASE WHEN
"Workforce Management - Person Real Time"."Person Legislative Information"."Country" = "Workforce Management - Worker
Assignment Real Time"."Legal Employer"."Legislation" THEN 'True' ELSE 'False'
ENDI'm also attaching the entire error message I'm receiving.
0 -
If the fields are different types this is the cast function I referenced above:
0 -
Did you built this statement manually? Please try to use Expression Builder to build the same statement. Even converting filter to SQL statement filter is not required. You will be still able to filter the data just like a normal column.
Can you please go to Advanced tab of analysis and copy & paste text in SQL Issued text box? It will allow me to duplicate your analysis to debug this issue.
0