C.PREF_FIRST_NAME (preferred first name) C.PATNER_LAST_NAME (partner last name) from table
per_person_names_f I have tried with KNOWN_AS(preferred last name field which is present in the table but data is empty.
How can I add these two columns to report in Oracle HCM cloud?
12 Answers
If you want to use preferred first name and default to partner last name, then:
NVL(C.PREF_FIRST_NAME, NVL(C.PATNER_LAST_NAME, '')) If you want to have both, then:
NVL(C.PREF_FIRST_NAME, '') || ' ' || NVL(C.PATNER_LAST_NAME, '') EDIT
I have seen your comment that you have accidentally posted as an edit to this answer, so, you have tried
NVL(ppnf.known_as Preferred_First_Name, NVL(ppnf.Previous_Last_name Patner_Last_name, '')) but got a legal identifier error. The as keyword is renaming a field in the selection. The selection starts with the select keyword and this clause consists of comma-separated fields, like
select f1, f2, f3, f4 ... You can rename a field like this:
select f1, f2, f3 as fancyfield, f4 ... however, you can only rename a field. If you have
select f1, f2, nvl(somevalue, nvl(someothervalue, '')), f4 ... then nvl(somevalue, nvl(someothervalue, '')) is a field and you can rename it like
select f1, f2, nvl(somevalue, nvl(someothervalue, '')) as fancyfield, f4 ... but you cannot rename a parameter of a function as a field, like nvl(somevalue as fancyfield, nvl(someothervalue as anotherfancyfield, '')), because renaming a field has the purpose of ensuring that the field names of your result set, or, the resulting relation, if you will has the field names you prefer. However, the parameter you pass to a function will not be a field in the result set due to being a parameter that you use as raw material to compute a field. From your try it seems that you want to get what value you need while knowing what it was. If so, then you can achieve it like this:
NVL(ppnf.known_as, '') as Preferred_First_Name, NVL(ppnf.Previous_Last_name, '') as Patner_Last_name, NVL(ppnf.known_as, NVL(ppnf.Previous_Last_name, '')) as Name In names table (per_person_names_f) we have two name types( NAME_TYPE ) global and local.
Usually users update their formal name in global name type and preferred name in local name type.
So try to use the name type in your sql to get the values.
Note: The local name type will be optional most cases. So it may not be there for some of the person records.
If you are looking for a preferred name type that holds first name and last name like PeopleSoft.. it may not be there in HCM. The nearest equivalent field is KNOWN_AS which is also option field usually.
