Oracle Transactional Business Intelligence

Products Banner

Compare two fields in analysis

274
Views
14
Comments

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"

  • Ruthie
    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)
  • 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
    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

     

  • 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

  • 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

  • The applied filter would look like this:

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

  • Ruthie
    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.

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

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

  • 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.

  • Ruthie
    Ruthie ✭✭✭

    Attached is a text file with the error.

    Thanks,

    Ruthie

  • Sorry, I am unable to see the text file.

    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.

  • Ruthie
    Ruthie ✭✭✭

    Ooops selected the upload image instead of Attach file.  Lets try this again!

  • Hi Ruth,

    I believe the problem is that you are not filtering last clause. For example, following line is actually a filter as it is looking for PERSON_TYPE = 300000000120758.

    (DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Worker"."Person Type") = 300000000120758) 

    Similarly, following line is a VALID filter as well as it is looking for ASSIGNMENT STATUS in (1, 4001....)

    AND (DESCRIPTOR_IDOF("Compensation - Salary Details Real Time"."Worker"."Assignment Status") IN (1, 4001, 2001, 2)) 

    However, the last line is not a filter. It is just a decode statement to return TRUE and FALSE. It doesn't say which value(s) you are interested in as it has no EQUAL TO or IN clause at it's end. Please look at might last image attachment. I have specifically pointed it out:

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

    Above statement should have ='True' , or ='False' at it's end to make it a valid filter.

    Please add filters one by one and test it.