Create the database view for department field which is text field

[supriyabehe]

Create the database view for department field which is text field from question_answer table and sn_hr_core_case_talent_management.

Then create report where i can use this database view by showing hr cases by department field values which is text field. Please help me with clauses . Its not working what i used

[danmjunqueira]

To create a database view that joins question_answer and sn_hr_core_case_talent_management based on the department field, which is a text field (not a reference), you need to ensure:

Both fields you’re joining on are in the same format (text)

You use a proper INNER JOIN or LEFT JOIN using the department name value

Below is the step-by-step guide to build the database view and then a report from it.

1. Identify the Text Fields
Assuming:

In question_answer, the department is stored in a field like u_department_text

In sn_hr_core_case_talent_management, the department is stored in a field like u_department_text (or similar)

If your field names differ, adjust accordingly.

2. Create the Database View
Go to: System Definition > Database Views
Click "New", and name it for example: x_hr_case_department_view

Now add the two tables:

question_answer (alias: qa)

sn_hr_core_case_talent_management (alias: hr)

Join condition (example):

Field from question_answer Operator Field from sn_hr_core_case_talent_management
qa.u_department_text equals hr.u_department_text

You can use INNER JOIN or LEFT JOIN depending on your use case:

Use INNER JOIN to only get records present in both tables

Use LEFT JOIN if you want all HR cases, even without a match in question_answer

3. Create a View Table
After saving the view, go to System Definition > Tables, search for your database view (e.g., x_hr_case_department_view), and verify that it's queryable.

If it is not visible for reporting, check the view definition and ensure both joined fields exist and the table is marked for reporting (Extends Table: none).

4. Create a Report
Go to: Reports > Create New

Select your database view as the data source

Choose type: Bar Chart, Pie, or List

Group by: u_department_text (from HR case or QA depending on which you want)

Display: count or any field like number, short description, etc.

Troubleshooting Clauses
If the join isn't working:

Check if there are extra spaces or case mismatches in department names.

You may need to use LOWER() or TRIM() functions via Scripted Database Views if needed.

Confirm both fields are populated consistently.

Example clause for scripted join (if using a Scripted DB View):

LOWER(TRIM(qa.u_department_text)) = LOWER(TRIM(hr.u_department_text))

Summary

• Use u_department_text = u_department_text join in the view

• Ensure both fields are text and properly filled

• Use the view as the data source in your report

• If mismatch persists, sanitize data or use script to normalize values

[Ankur Bawiskar]

@supriyabehe 

your query is wrong in where clause

it should be this

1) don't give anything in where clause of table sn_hr_core_case_talent_management

2) give this in where clause of question_answer table

case_table_sys_id = qa_sys_id

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar]

@supriyabehe 

Hope you are doing good.

Did my reply answer your question?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[supriyabehe]

How i will create report after this? Can you please suggest?