CODEWITHSUNDEEP
oracle-databaseplsql
tcetin
tcetin

Reputation: 1089

PL SQL [Err] ORA-12704: character set mismatch

I Have a two pl/sql query but there is no two coulmns at (1) queries.They are AcademicTitles and ManagerialTitles.So I gave they as default value 'YOK' at (1) query.

I want to UNION (1) and (2) queries.

Here is My Queries:

1-

SELECT
    h1."IL_KODU",
    h1."KURUM_ILI",
    h1.ILCE_KODU,
    h1."KURUM_ILCESI",
    h1."KURUM_KODU",
    h1."KURUM_ADI",
    (
        CASE
        WHEN h1."STATU" = 'K' THEN
            'Devlet'
        END
    ) AS KURUM_STATU,
    h1."KURUM_TUR_ADI",
    br3."Type" AS Unvan,
    br."BranchName" AS Brans,
    'YOK' AS AkademikUnvan,
    'YOK' AS IdariUnvan,
    COUNT (1) AS Total
FROM KAMU_PERSONEL k1
INNER JOIN "SptsBranches" s1 ON s1."CkysBranchCode" = k1.BRANS_KODU
INNER JOIN "Branches" br ON s1."BranchId" = br."BranchId"
INNER JOIN "BranchBranchTypes" br2 ON br."BranchId" = br2."BranchId"
INNER JOIN "BranchTypes" br3 ON br3."BranchTypeId" = br2."BranchTypeId"
INNER JOIN HOSPITALS h1 ON h1.KURUM_KODU = k1.CALIS_BIRIM
GROUP BY
    h1."IL_KODU",
    h1."KURUM_ILI",
    h1.ILCE_KODU,
    h1."KURUM_ILCESI",
    h1."KURUM_KODU",
    h1."KURUM_ADI",
    h1."STATU",
    h1."KURUM_TUR_ADI",
    br3."Type",
    br."BranchName"

2-

    SELECT
    p3."IL_KODU",
    p3."KURUM_ILI",
    p3.ILCE_KODU,
    p3."KURUM_ILCESI",
    p3."KURUM_KODU",
    p3."KURUM_ADI",
    (
        CASE
        WHEN p3."STATU" = 'O' THEN
            'Özel'
        WHEN p3."STATU" = 'U' THEN
            'Üniversite'
        END
    ) AS KURUM_STATU,
    p3."KURUM_TUR_ADI",
    b2."Type" AS Unvan,
    b1."BranchName" AS Brans,
    u1."Title" AS AkademikUnvan,
    u2."Title" AS IdariUnvan,
    COUNT (1) AS Total
FROM
    "SptsDatas" p1
INNER JOIN "PersonStatus" p2 ON p1."TcKimlik" = p2."Tckn"
INNER JOIN "Branches" b1 ON p1."Brans_BranchId" = b1."BranchId"
INNER JOIN "BranchTypes" b2 ON p1."Unvan_BranchTypeId" = b2."BranchTypeId"
INNER JOIN HOSPITALS p3 ON p2."HospitalCode" = p3."KURUM_KODU"
INNER JOIN "AcademicTitles" u1 ON u1."Id" = p1."AkademikUnvan_Id"
INNER JOIN "ManagerialTitles" u2 ON u2."Id" = p1."IdariUnvan_Id"
WHERE
    p2."Statu" = 1
AND p3."AKTIF" = 1
GROUP BY
    p3."IL_KODU",
    p3."KURUM_ILI",
    p3.ILCE_KODU,
    p3."KURUM_ILCESI",
    p3."KURUM_KODU",
    p3."KURUM_ADI",
    b1."BranchName",
    b2."Type",
    u1."Title",
    u2."Title",
    p3."STATU",
    p3."KURUM_TUR_ADI"

When try to UNION two queries this error occuring.

[Err] ORA-12704: character set mismatch

How can I fix this error.

Thanks

Upvotes: 0

Views: 4273

Answers (2)

Gnans
Gnans

Reputation: 16

If you cast the two columns in second query that should solve it I believe.

In your second query try replacing

u1."Title" AS AkademikUnvan, u2."Title" AS IdariUnvan,

with

CAST( u1."Title" AS varchar2(1000) ) AS AkademikUnvan, CAST( u2."Title" AS varchar2(1000) ) AS IdariUnvan

Let me know how that goes.

Upvotes: 0

Krzysztof Kaszkowiak
Krzysztof Kaszkowiak

Reputation: 896

My guess is that value of KURUM_STATU column in the second query is converted to NVARCHAR2, because of "Ö" and "Ü" in its value. As a result, Oracle tries to mix varchar2 with nvarchar2 giving the mentioned ORA error. To confirm this, try to remove those characters from the statement and if it helps, convert the values explicitly to nvarchar2, or get rid of the trouble characters.

Upvotes: 1

Related Questions