nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Compare two fields in analysis

Ruthie

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

Find more posts tagged with

Comments

Michael Scaduto

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"

Ruthie

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:OI2DL65P

State: 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)

Michael Scaduto

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.

Ruthie

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

Shakher Sharma

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.

Advanced SQL Filter.png

Michael Scaduto

You should be able to set the fields equal to each other without the case statement using the Advanced SQL Filter

4-11-2018 10-22-44 AM.jpg

Michael Scaduto

The applied filter would look like this:

4-11-2018 10-23-06 AM.jpg

Ruthie

CASE WHEN
"Workforce Management - Person Real Time"."Person Legislative Information"."Country" = "Workforce Management - Worker
Assignment Real Time"."Legal Employer"."Legislation" THEN 'True' ELSE 'False'
END

I'm also attaching the entire error message I'm receiving.

Michael Scaduto

If the fields are different types this is the cast function I referenced above:

4-11-2018 10-34-01 AM.jpg

Shakher Sharma

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.