CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
RicardoBarros
RicardoBarros

Reputation: 163

character set mismatch coalesce

I have a problem with this query, it tells me the error

ORA-00932: inconsistent datatypes: expected NCHAR got NUMBER
00932.00000 - "inconsistent datatypes: expected% s got% s"
* Cause:
* Action:
Error in line: 54, column: 43

the subquery returns the letter "f" or "m" of the sex of the students in which there is more, this works but the problem is how I assign that letter to the variable Where genero_a =

SELECT NOMBRE, COALESCE(DIRECCION,CORREO, 10) comm
FROM ESTUDIANTE
WHERE GENERO_A =  
    (SELECT GENERO_A
    FROM ESTUDIANTE
    GROUP BY GENERO_A
    HAVING COUNT(GENERO_A) = 
        (SELECT MAX(COUNT(*)) as CONTAR
        FROM ESTUDIANTE
        GROUP BY GENERO_A))
ORDER BY NOMBRE;

I am not at all proficient in sql, I appreciate your understanding.

--TABLA ESTUDIANTE

CREATE TABLE ESTUDIANTE(
    ID_LECTOR VARCHAR2(50) NOT NULL,
    NOMBRE VARCHAR2(50),
    APELLIDO VARCHAR2(50),
    GENERO_A CHAR(1) CONSTRAINT CH_GENERO_A CHECK(GENERO_A IN ('f', 'm')),
    DIRECCION NVARCHAR2(100),
    CORREO NVARCHAR2(50)
);
ALTER TABLE ESTUDIANTE ADD CONSTRAINT PK_ESTUDIANTE PRIMARY KEY(ID_LECTOR);

Upvotes: 0

Views: 2236

Answers (1)

Ofisora
Ofisora

Reputation: 2737

The error is on COALESCE(DIRECCION,CORREO, 10), since all the fields does not have same data types.

Solution: You have to cast NUMBER 10 to the similar datatype as of DIRECTION and CORREO. Simply change the above code to COALESCE(DIRECCION,CORREO, CAST(10 as NVARCHAR2(2))).

The CAST function converts a value from one data type to another.

Updated query:

SELECT NOMBRE, COALESCE(DIRECCION,CORREO, CAST(10 as NVARCHAR2(2))) comm
FROM ESTUDIANTE
WHERE GENERO_A =  
    (SELECT GENERO_A
    FROM ESTUDIANTE
    GROUP BY GENERO_A
    HAVING COUNT(GENERO_A) = 
        (SELECT MAX(COUNT(*)) as CONTAR
        FROM ESTUDIANTE
        GROUP BY GENERO_A))
ORDER BY NOMBRE;

Or, From the link you provided,

SELECT NOMBRE, COALESCE(DIRECCION,CORREO, N'10') comm
FROM ESTUDIANTE
WHERE GENERO_A =   
   (SELECT GENERO_A
    FROM ESTUDIANTE
    GROUP BY GENERO_A
    HAVING COUNT(GENERO_A) = 
        (SELECT MAX(COUNT(*)) as CONTAR
        FROM ESTUDIANTE
        GROUP BY GENERO_A))
ORDER BY NOMBRE;

Or,

SELECT NOMBRE, COALESCE(DIRECCION,CORREO, N''||10) comm
FROM ESTUDIANTE
WHERE GENERO_A =   
   (SELECT GENERO_A
    FROM ESTUDIANTE
    GROUP BY GENERO_A
    HAVING COUNT(GENERO_A) = 
        (SELECT MAX(COUNT(*)) as CONTAR
        FROM ESTUDIANTE
        GROUP BY GENERO_A))
ORDER BY NOMBRE;

Upvotes: 4

Related Questions