ORA-00932: inconsistent datatypes: expected DATE got NUMBER

I am using Oracle 11g and running the following query in oracle toad:

WITH
CTEDevices (CREATED_ON, CUSTOMER_INFO_ID, DEVICE_MAKE, DEVICE_Model) AS (
SELECT
Trunc(RD.CREATED_ON),
RD.CUSTOMER_INFO_ID,
RD.DEVICE_MAKE,
RD.DEVICE_Model
FROM Schema.Table1 RD
WHERE RD.PARAM_CHANNEL_ID = 1
AND RD.CREATED_ON >= '01-may-2022'
AND RD.CREATED_ON <= '02-may-2022'
GROUP BY
Trunc(RD.CREATED_ON),
RD.CUSTOMER_INFO_ID,
RD.DEVICE_MAKE,
RD.DEVICE_Model
),
CTERegistration (CREATED_ON, CUSTOMER_INFO_ID, DEVICE_MAKE, DEVICE_Model) AS
(
SELECT 
CI.Customer_Info_Id,
Trunc(CI.created_on),
'CI.DEVICE_MAKE',
'CI.DEVICE_Model'
FROM Schema.Table2 CI
Where CI.CUSTOMER_TYPE = 'A'
AND CI.CREATED_ON >= '01-may-2022'
AND CI.CREATED_ON <= '02-may-2022'
) 
SELECT 
CTEDevices.CREATED_ON, CTEDevices.CUSTOMER_INFO_ID, CTEDevices.DEVICE_MAKE, CTEDevices.DEVICE_Model,
CTERegistration.CREATED_ON, CTERegistration.CUSTOMER_INFO_ID, CTERegistration.DEVICE_MAKE, CTERegistration.DEVICE_Model
FROM CTEDevices INNER JOIN CTERegistration ON (CTEDevices.CUSTOMER_INFO_ID = CTERegistration.CUSTOMER_INFO_ID AND (CTEDevices.CREATED_ON = CTERegistration.CREATED_ON));

Individual qeueries were running successfully but when going to run as combined one getting the following error:

ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Please help.

Thanks

Solution

Culprit is the 2nd CTE:

CTERegistration (CREATED_ON, CUSTOMER_INFO_ID, DEVICE_MAKE, DEVICE_Model) AS
(                1st         2nd
SELECT 
CI.Customer_Info_Id,     1st   
Trunc(CI.created_on),    2nd
'CI.DEVICE_MAKE', ...

The 1st column is supposed to be CREATED_ON (which is, apparently, DATE datatype value), while the 2nd column is then CUSTOMER_INFO_ID. Your query has it just the opposite, so - fix it:

CTERegistration (CREATED_ON, CUSTOMER_INFO_ID, DEVICE_MAKE, DEVICE_Model) AS
(                
SELECT 
Trunc(CI.created_on),
CI.Customer_Info_Id,   
'CI.DEVICE_MAKE', ...

If you wonder why separate queries work, well - no reason why not. Oracle doesn't care how you named columns, but you can't join them any way you want.