oracle11gcommon-table-expression

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


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.