Oracle SQL case statement with join gives weird results-CodePudding

I have a table per_person_names_f with columns full_name and name_type, when name_type is equal to SA, then fullname is Arabic, if GLOBAL then it's English. I want the result to show 2 columns for Arabic and English names.

SELECT
    rownum,
    CASE name_type
        WHEN 'Global' THEN full_name 
    END AS "english name",
    CASE name_type
        WHEN 'SA' THEN full_name 
    END AS "arabic name"
FROM 
    per_person_names_f

But it ends with weird results, column english name is empty and arabic name shows some null values, what's wrong here knowing that data does certainly exist?

CodePudding user response：

That's expected, I'd say, because one name excludes another - you can't have BOTH English and Arabic name AT THE SAME TIME, can you? Name is either English, or Arabic.

Sample data:

SQL> with per_person_names_f (name_type, full_name) as
  2    (select 'Global', 'my English name' from dual union all
  3     select 'SA'    , 'my Arabic name'  from dual
  4    )

Query:

  5  select name_type,
  6    case when name_type = 'Global' then full_name end as english_name,
  7    case when name_type = 'SA'     then full_name end as arabic_name
  8  from per_person_names_f;

NAME_T ENGLISH_NAME    ARABIC_NAME
------ --------------- ---------------
Global my English name
SA                     my Arabic name

SQL>

From my point of view, that's OK. If you think it is not, what result do you expect?

CodePudding user response：

WITH PERSON_NAMES_F(FULL_NAME,NAME_TYPE) AS
(
   SELECT 'ABDUL','SA' FROM DUAL UNION ALL
   SELECT 'JONNY','GLOBAL' FROM DUAL
)
SELECT 
 CASE 
    WHEN P.NAME_TYPE='SA' THEN P.FULL_NAME
    ELSE NULL
 END  ARABIC_NAME,
 CASE
  WHEN P.NAME_TYPE='GLOBAL' THEN P.FULL_NAME
 ELSE NULL
END  INTERNATIONAL_NAME
FROM PERSON_NAMES_F P

CodePudding user response：

Your description refers to GLOBAL in uppercase, but your code has 'Global' in mixed case. Based on your comment that should be 'GLOBAL' in the code too.

but still give nulls, not like the result i expected, they are related through person_id

So it seems you do want to get the matching English and Arabic names for the same person, based on the person_id value.

You can use conditional aggregation for that:

SELECT
    person_id,
    MAX(CASE name_type
        WHEN 'GLOBAL' THEN full_name 
    END) AS "english name",
    MAX(CASE name_type
        WHEN 'SA' THEN full_name 
    END) AS "arabic name"
FROM 
    per_person_names_f
GROUP BY
    person_id

fiddle with some made-up data.