If you’re getting the error “ORA-01790: expression must have same datatype as corresponding expression” in Oracle Database, it’s probably because you’re using an operator such as UNION, INTERSECT, or EXCEPT to run a compound query, but the columns returned by each query use different data type groups.
To fix this issue, you’ll need to ensure that each column returned by the second query uses the same data type group as the corresponding column in the first query.
Example of Error
Here’s an example of code that produces this error:
SELECT TeacherName FROM Teachers
UNION
SELECT StudentId FROM Students;Result:
ORA-01790: expression must have same datatype as corresponding expression
The problem here, is that I’m trying to combine the TeacherName column in the first query, with the StudentId column in the second query.
In my case, the TeacherName column is a varchar(50) column but the StudentId column is an int column. This causes the error to occur.
Solution 1
The first (and probably most common) solution to the above error is to ensure that we have the correct column/s in each query.
In my case, it seems quite obvious that I passed the wrong columns. Therefore, I can modify the above query as follows:
SELECT TeacherName FROM Teachers
UNION
SELECT StudentName FROM Students;Result:
| TEACHERNAME |
|---|
| Ben |
| Bill |
| Cathy |
| Ein |
| Faye |
| Jet |
| Spike |
| Warren |
Or I could do the following:
SELECT TeacherId, TeacherName FROM Teachers
UNION
SELECT StudentId, StudentName FROM Students;Result:
| TEACHERID | TEACHERNAME |
|---|---|
| 1 | Faye |
| 1 | Warren |
| 2 | Ben |
| 2 | Jet |
| 3 | Cathy |
| 3 | Spike |
| 4 | Cathy |
| 4 | Ein |
| 5 | Bill |
| 5 | Warren |
| 6 | Bill |
In both cases, the column types returned by the second query matched the types returned by the first query.
Solution 2
In some cases, you may find that you’ve got the correct columns, but their types don’t match. In such cases, you may need to convert one of the columns to a different data type.
Using our example, we could do this:
SELECT TeacherName FROM Teachers
UNION
SELECT TO_CHAR(StudentId) FROM Students;Result:
| TEACHERNAME |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| Ben |
| Bill |
| Cathy |
| Warren |
This probably isn’t the best example, as it’s combining names with IDs, but I’m sure you get the picture. We were able to avoid the error by using the TO_CHAR(number) function to convert the StudentId column to a char type.