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 queries 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
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.