obiee query to find out multiple races in a column in one

Question

SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1';SELECT
   0 s_0,
   "People - People Real Time"."People Details"."People Full Name" FullName,
case
when  "People - People Real Time"."Job Application - Legislative Information"."Ethnicity" like '%,%' then 'Two Or More Races'
else    "People - People Real Time"."Job Application - Legislative Information"."Ethnicity" end as  Ethnicity,
   "People - People Real Time"."Job Application - Legislative Information"."Gender" Gender,
 "People - People Real Time"."Job Requisition - Basic Information"."Requisition Number" RequisitionNumber
FROM "People - People Real Time"
where  "People - People Real Time"."Job Requisition - Basic Information"."Requisition Number"  = :reqNumber
ORDER BY "People - People Real Time"."Job Application - Job Application Dates"."Creation Date" desc, 1, 2 ASC NULLS LAST, 3 ASC NULLS LAST, 4 ASC NULLS LAST, 5 ASC NULLS LAST

I am getting the output like -

FullName                        Ethnicity                   Gender              RequisitionNumber
Smith                       Hispanic                        M                       10
Smith                       Englush                         M                       10
Smith                       Hindi                           M                       10
Test                        English                         F                       11

I want the above query to be tweaked in a way that i get the output as

FullName                    Ethnicity                   Gender              RequisitionNumber
Smith                       Two Or More Races               M                       10
Test                        English                         F                       11

I cannot use many functions in the query ( i can use Count) because it is OBIEE

score 0 · Answer 1 · answered Oct 23 '22 at 06:58

Continuing on the output you got already...

WITH
    dataset AS
        (
            Select 'Smith' "FULL_NAME", 'Hispanic' "ETHNICITY", 'M' "GENDER", 10 "REQUISITION_NUMBER" From Dual Union All
            Select 'Smith' "FULL_NAME", 'English'  "ETHNICITY", 'M' "GENDER", 10 "REQUISITION_NUMBER" From Dual Union All
            Select 'Smith' "FULL_NAME", 'Hindi'    "ETHNICITY", 'M' "GENDER", 10 "REQUISITION_NUMBER" From Dual Union All
            Select 'Test'  "FULL_NAME", 'English'  "ETHNICITY", 'F' "GENDER", 11 "REQUISITION_NUMBER" From Dual
        )
SELECT DISTINCT
    FULL_NAME,
    CASE WHEN Count(*) OVER(PARTITION BY FULL_NAME, GENDER, REQUISITION_NUMBER) > 1 THEN 'Two Or More Races' ELSE ETHNICITY END "ETHNICITY",
    GENDER,
    REQUISITION_NUMBER
FROM
    dataset
/*  R e s u l t :
FULL_NAME ETHNICITY         GENDER REQUISITION_NUMBER
--------- ----------------- ------ ------------------
Test      English           F                      11 
Smith     Two Or More Races M                      10
*/

One of the options is to use CASE expresion with Count() analytic function to transform the ETHNICITY column to the text you want. There are multiple rows initialy so the DISTINCT keyword is used to get your expected result.
Regards...

obiee query to find out multiple races in a column in one

1 Answers1