oracle pl/saql ORA-01790: 表达式必须与相应的表达式具有相同的数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24053294/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
oracle pl/saql ORA-01790: expression must have same datatype as corresponding expression
提问by Qaisar Tariq
SELECT SUM(SALES_AMOUNT)SALES,
YEAR,
MONTH,
CATAGORY_ID,
SALES_PERSON_ID,
ITEM_TYPE_ID
FROM APEX_FINAL
where sales_amount is not null
and catagory_id is not null
GROUP BY (YEAR,MONTH,CATAGORY_ID,SALES_PERSON_ID,ITEM_TYPE_ID)
union all
SELECT SUM(SALES_AMOUNT)SALES,
YEAR,
MONTH,
CATAGORY_ID,
'all others' SALES_PERSON_ID,
ITEM_TYPE_ID
FROM APEX_FINAL
where sales_amount is not null
and sales_person_id is null
GROUP BY (YEAR,MONTH,CATAGORY_ID,SALES_PERSON_ID,ITEM_TYPE_ID)
this is my code plz help me in resolving my problem as i m getting the eroor of mis math data types but all data types are same
这是我的代码请帮助我解决我的问题，因为我得到了错误的数学数据类型，但所有数据类型都是相同的
回答by Nishanthi Grashia
Sales Person ID seems to have the mismatch. It could possibly be a NUMERICdatatype in your table.
销售人员 ID 似乎不匹配。它可能是NUMERIC您表中的数据类型。
May be you can use TO_CHAR(SALES_PERSON_ID)in the first Union Query as below. It would help you solve the issue
可能你可以TO_CHAR(SALES_PERSON_ID)在第一个联合查询中使用，如下所示。它会帮助你解决问题
SELECT SUM(SALES_AMOUNT)SALES,
YEAR,
MONTH,
CATAGORY_ID,
TO_CHAR(SALES_PERSON_ID) SALES_PERSON_ID,
ITEM_TYPE_ID
FROM APEX_FINAL
where sales_amount is not null
and catagory_id is not null
GROUP BY (YEAR,MONTH,CATAGORY_ID,SALES_PERSON_ID,ITEM_TYPE_ID)
union all
SELECT SUM(SALES_AMOUNT)SALES,
YEAR,
MONTH,
CATAGORY_ID,
'all others' SALES_PERSON_ID,
ITEM_TYPE_ID
FROM APEX_FINAL
where sales_amount is not null
and sales_person_id is null
GROUP BY (YEAR,MONTH,CATAGORY_ID,SALES_PERSON_ID,ITEM_TYPE_ID)
回答by Frank Schmitt
As @Nisha already pointed out, you're probably getting this error because SALES_PERSON_IDis a numeric field - you'll either have to convert it to a string or (preferably) join to another table to get the name of the sales person.
正如@Nisha 已经指出的那样，您可能会收到此错误，因为它SALES_PERSON_ID是一个数字字段 - 您要么必须将其转换为字符串，要么（最好）加入另一个表以获取销售人员的姓名。
Additionally, I believe you've got a logic error in your query - in your first SELECT, you filter by catagory_person_id is not null; this should probably be sales_person_id is not null.
此外，我相信您的查询中存在逻辑错误 - 在您的第一个 中SELECT，您过滤了catagory_person_id is not null; 这应该是sales_person_id is not null。
Assuming this is the case, then you can simplify the query with a CASEstatement (you could also use DECODEor COALESCE):
假设是这种情况，那么您可以使用CASE语句简化查询（您也可以使用DECODE或COALESCE）：
SELECT
SUM(SALES_AMOUNT) SALES,
YEAR,
MONTH,
CATAGORY_ID,
(case
when sales_person_id is not null then to_char(SALES_PERSON_ID)
else 'all others'
end) as sales_person_id,
ITEM_TYPE_ID
FROM APEX_FINAL
where sales_amount is not null
GROUP BY (YEAR,MONTH,CATAGORY_ID,SALES_PERSON_ID,ITEM_TYPE_ID)
回答by cool_kid
I was struggling hours on a similar issue and realized that the column orders of the two tables we are unioning should be the same!
我在一个类似的问题上苦苦挣扎了几个小时，并意识到我们联合的两个表的列顺序应该是相同的！
It still gives you the same error, so make sure columns are in the same order :)
它仍然给你同样的错误，所以确保列的顺序相同:)
