basically I don’t know what is wrong with my query, can u help? :
It gives me: [42000][907] ORA-00907: missing right parenthesis
UPDATE
(SELECT CAR_CODE as OLD, V.FKEY as VCODE, S.FKEY as SCODE, UA.FKEY UAKEY
FROM CARS EU_U
inner join DICT_CAR_CODES UA
On EU_U.CARCODE = UA.id
left join DICT_CAR_CODES_S S
On EU_U.CARCODE_S = S.id
left join DICT_CAR_CODES_V V
On EU_U.CARCODE_V = V.id
WHERE EU_U.CAR_CODE is null
) t SET t.OLD = (CASE WHEN t.UAKEY='X' THEN (t.SCODE )
WHEN t.UAKEY='Y' THEN (t.VCODE )
WHEN t.UAKEY='Z' THEN ('ZAT' as NVARCHAR2(10))
Else null End);
I was able to reproduce your issue, and as @Littlefoot’s answer pointed out it is coming from the AS part.
I think you can use cast('ZAT' as NVARCHAR2(10)) instead.
I am not sure the data types you are using for the tables, I just created few sample tables to reproduce your issue.I also tried using MERGE and CAST and it works fine, however do check the data types as mine are just examples for testing.
Fiddle
MERGE INTO CARS EU_U
USING (
SELECT
EU_U.CAR_CODE AS OLD,
V.FKEY AS VCODE,
S.FKEY AS SCODE,
UA.FKEY AS UAKEY,
EU_U.CARCODE
FROM CARS EU_U
INNER JOIN DICT_CAR_CODES UA
ON EU_U.CARCODE = UA.id
LEFT JOIN DICT_CAR_CODES_S S
ON EU_U.CARCODE_S = S.id
LEFT JOIN DICT_CAR_CODES_V V
ON EU_U.CARCODE_V = V.id
WHERE EU_U.CAR_CODE IS NULL
) t
ON (EU_U.CARCODE = t.CARCODE)
WHEN MATCHED THEN
UPDATE SET
EU_U.CAR_CODE =
CASE
WHEN t.UAKEY = 100 THEN t.SCODE
WHEN t.UAKEY = 200 THEN t.VCODE
WHEN t.UAKEY = 300 THEN cast('ZAT' as NVARCHAR2(10))
ELSE NULL
END;
2
How about removing the as part of the query?
UPDATE (
SELECT CAR_CODE AS OLD,
V.FKEY AS VCODE,
S.FKEY AS SCODE,
UA.FKEY UAKEY
FROM CARS EU_U
INNER JOIN DICT_CAR_CODES UA ON EU_U.CARCODE = UA.id
LEFT JOIN DICT_CAR_CODES_S S ON EU_U.CARCODE_S = S.id
LEFT JOIN DICT_CAR_CODES_V V ON EU_U.CARCODE_V = V.id
WHERE EU_U.CAR_CODE IS NULL
) t
SET t.OLD =
(CASE
WHEN t.UAKEY = 'X' THEN (t.SCODE)
WHEN t.UAKEY = 'Y' THEN (t.VCODE)
WHEN t.UAKEY = 'Z' THEN ('ZAT') -- as NVARCHAR2 ( 10))
ELSE NULL
END);
1