oracle pl/saql ORA-01790: expression must have same datatype as corresponding expression

Question

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

Answer 1

Sales Person ID seems to have the mismatch. It could possibly be a NUMERIC datatype in your table.

May be you can use TO_CHAR(SALES_PERSON_ID) in the first Union Query as below. It would help you solve the issue

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)

Answer 2

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 :)