I have two select statements joined by «union». While executing that statement I’ve got:
Error report:
SQL Error: ORA-01790: expression must have same datatype as corresponding expression
01790. 00000 — «expression must have same datatype as corresponding expression»
Maybe you can give me an advise on how to diagnose this problem?
OMG Ponies
326k82 gold badges523 silver badges502 bronze badges
asked Dec 7, 2009 at 21:42
Roman KaganRoman Kagan
10.5k26 gold badges86 silver badges127 bronze badges
Without looking at your SQL, I would guess that you have columns being UNION’ed that have different data types.
answered Dec 7, 2009 at 21:44
Otávio DécioOtávio Décio
73.9k17 gold badges161 silver badges228 bronze badges
2
Here’s what found:
ORA-01790: expression must have same datatype as corresponding expression
Cause: A SELECT list item corresponds to a SELECT list item with a different datatype in another query of the same set expression.
Action: Check that all corresponding SELECT list items have the same datatypes. Use the TO_NUMBER, TO_CHAR, and TO_DATE functions to do explicit data conversions.
I haven’t seen your query, but I am guessing that one select in your union is not selecting the same columns as the other.
answered Dec 7, 2009 at 21:44
0
Clearly the issue for the poster was solved over half a decade ago, nonetheless I wanted to point out to anyone reading this post in search of help that the order of the selected properties (columns) must match from one unioned statement to the next. It is not enough to simply have the names and the data types match, though that is in a sense the root cause. But due to the way the Union statements are handled in Oracle, it is possible to get the ORA-01790 error due to a mismatch in the ordering of columns only.
In my case, I had a query with a UNION ALL of two selects. One select had a column named «generic_column_name» as the 25th item in the select, and the other select had that same column named «generic_column_name» of the very same data type (I tested several ways through hard coding and also using forced data type conversions). However the second select had this item in the 19th place, so all of the columns from there on were offset and this triggered the ORA-01790 error.
answered Mar 23, 2016 at 13:33
1
As I mention in the question I’d like to have SUGGESTIONS for how to troubleshoot my problem.
What I’ve done is enabled one column at a time in each select statement and found that I had mismatch at the very last column of my SQL UNION. Thanks a lot for participating and helping me, but I knew I had type mismatch, WHAT I didn’t know is how to troubleshoot.
answered Dec 9, 2009 at 20:22
Roman KaganRoman Kagan
10.5k26 gold badges86 silver badges127 bronze badges
1
The error is telling you that you’re union-ing columns with different datatypes. There are oracle functions that will convert one type to another (e.g. «to_char»), you’ll have to convert the datatypes into a common format, or at least one into the other. If you post the actual query/types it’d be possible to be more specifc.
answered Dec 7, 2009 at 21:46
Steve B.Steve B.
55.5k12 gold badges94 silver badges132 bronze badges
You need to make sure that corresponding columns in your union have the same data type. The easiest way would be to comment out columns one by one to narrow down to the column and then use explicit type conversion function in one of them to make types match.
answered Dec 7, 2009 at 21:46
Eugene KuleshovEugene Kuleshov
31.5k5 gold badges66 silver badges67 bronze badges
You tried to execute a SELECT statement (probably a UNION or a UNION ALL), and all of the queries did not contain matching data types in the result columns.
Techonthenet — ORA-01790
answered Dec 7, 2009 at 21:47
OMG PoniesOMG Ponies
326k82 gold badges523 silver badges502 bronze badges
I had the same problem today. The issue is in the union, basically in the order of the columns
Ali Kleit
3,0892 gold badges23 silver badges39 bronze badges
answered Jan 27, 2020 at 20:08
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.
ORA-01790
ORA-01790: выражение должно иметь тот же тип данных, как соответствующее выражение
Причина:
Элемент данных SELECT списка соответствует SELECT списку с отличным типом данных в другом запросе того же множественного выражения.
Действие:
Убедитесь, что все соответствующие элементы SELECT списка имеют те же типы данных. Вы можете использовать функции TO_NUMBER, TO_CHAR, TO_DATE для того чтобы сделать явно заданные конверсии данных.
I have two select statements joined by «union». While executing that statement I’ve got:
Error report:
SQL Error: ORA-01790: expression must have same datatype as corresponding expression
01790. 00000 — «expression must have same datatype as corresponding expression»
Maybe you can give me an advise on how to diagnose this problem?
OMG Ponies
326k82 gold badges523 silver badges502 bronze badges
asked Dec 7, 2009 at 21:42
Roman KaganRoman Kagan
10.5k26 gold badges86 silver badges127 bronze badges
Without looking at your SQL, I would guess that you have columns being UNION’ed that have different data types.
answered Dec 7, 2009 at 21:44
Otávio DécioOtávio Décio
73.9k17 gold badges161 silver badges228 bronze badges
2
Here’s what found:
ORA-01790: expression must have same datatype as corresponding expression
Cause: A SELECT list item corresponds to a SELECT list item with a different datatype in another query of the same set expression.
Action: Check that all corresponding SELECT list items have the same datatypes. Use the TO_NUMBER, TO_CHAR, and TO_DATE functions to do explicit data conversions.
I haven’t seen your query, but I am guessing that one select in your union is not selecting the same columns as the other.
answered Dec 7, 2009 at 21:44
0
Clearly the issue for the poster was solved over half a decade ago, nonetheless I wanted to point out to anyone reading this post in search of help that the order of the selected properties (columns) must match from one unioned statement to the next. It is not enough to simply have the names and the data types match, though that is in a sense the root cause. But due to the way the Union statements are handled in Oracle, it is possible to get the ORA-01790 error due to a mismatch in the ordering of columns only.
In my case, I had a query with a UNION ALL of two selects. One select had a column named «generic_column_name» as the 25th item in the select, and the other select had that same column named «generic_column_name» of the very same data type (I tested several ways through hard coding and also using forced data type conversions). However the second select had this item in the 19th place, so all of the columns from there on were offset and this triggered the ORA-01790 error.
answered Mar 23, 2016 at 13:33
1
As I mention in the question I’d like to have SUGGESTIONS for how to troubleshoot my problem.
What I’ve done is enabled one column at a time in each select statement and found that I had mismatch at the very last column of my SQL UNION. Thanks a lot for participating and helping me, but I knew I had type mismatch, WHAT I didn’t know is how to troubleshoot.
answered Dec 9, 2009 at 20:22
Roman KaganRoman Kagan
10.5k26 gold badges86 silver badges127 bronze badges
1
The error is telling you that you’re union-ing columns with different datatypes. There are oracle functions that will convert one type to another (e.g. «to_char»), you’ll have to convert the datatypes into a common format, or at least one into the other. If you post the actual query/types it’d be possible to be more specifc.
answered Dec 7, 2009 at 21:46
Steve B.Steve B.
55.5k12 gold badges94 silver badges132 bronze badges
You need to make sure that corresponding columns in your union have the same data type. The easiest way would be to comment out columns one by one to narrow down to the column and then use explicit type conversion function in one of them to make types match.
answered Dec 7, 2009 at 21:46
Eugene KuleshovEugene Kuleshov
31.5k5 gold badges66 silver badges67 bronze badges
You tried to execute a SELECT statement (probably a UNION or a UNION ALL), and all of the queries did not contain matching data types in the result columns.
Techonthenet — ORA-01790
answered Dec 7, 2009 at 21:47
OMG PoniesOMG Ponies
326k82 gold badges523 silver badges502 bronze badges
I had the same problem today. The issue is in the union, basically in the order of the columns
Ali Kleit
3,0892 gold badges23 silver badges39 bronze badges
answered Jan 27, 2020 at 20:08
Since you’re on the base release this looks like but 11840579. You may be able to work around it by casting the value — it shouldn’t be necessary, but that’s bugs for you:
with x (id, dateN) as
(
select 1, cast(to_date('2015-05-01 00:00:00','YYYY-MM-DD HH24:MI:SS') as date) from dual
union all
select id+1, dateN+1 from x where id < 10
)
select * from x;
Including extra elements in the conversion is a bit pointless; personally I prefer date literals anyway:
with x (id, dateN) as
(
select 1, cast(date '2015-05-01' as date) from dual
union all
select id+1, dateN+1 from x where id < 10
)
select * from x;
The two values, date '2015-01-01' and cast(date '2015-05-01' as date) are slightly different types, with different internal representations, which seems to be causing the problem:
select dump(date '2015-05-01', 16) as d1, dump(cast(date '2015-05-01' as date), 16) as d2
from dual;
D1 D2
-------------------------------- --------------------------------
Typ=13 Len=8: df,7,5,1,0,0,0,0 Typ=12 Len=7: 78,73,5,1,1,1,1
However there’s a second part to the bug which is that it can return the wrong results. If you can’t patch up to avoid the problem, you could use the older hierarchical-query approach:
select level as id, date '2015-05-01' + level - 1 as dateN
from dual
connect by level < 10;