0

I have a problem with an update (Database is AS400):

UPDATE      TABLE1 t1
SET      
(t1.VRNUMO,t1.VRNUSE) = (SELECT XVRNUMO,XVRNUSE   FROM TABLE2 t2  where  t2.XVRPRTE = t1.VRPRTE AND t2.XVRPROG=t1.VRPROG)
 WHERE
 t1.VRDIRI='M' AND t1.VRCODA=1 AND t1.VRNUMO=0

I get this error message:

[Error Code: -802, SQL State: 22023] [SQL0802] Errore nella definizione o nella conversione dei dati.

I get the same error if I try to do a SELECT with JOIN with the same conditions:

SELECT *
FROM
TABLE1 t1
LEFT JOIN
TABLE2 t2
ON
t2.XVRPRTE=t1.VRPRTE  and t2.XVRPROG=t1.VRPROG
WHERE
t1.VRDIRI='M' AND t1.VRCODA=1 AND t1.VRNUMO=0

There isn't any problem if I do the same SELECT using RIGHT JOIN or if I delete the second condition from the JOIN ( t2.XVRPROG=t1.VRPROG) or

SELECT *
FROM
TABLE1 t1
RIGHT JOIN
TABLE2 t2
ON
t2.XVRPRTE=t1.VRPRTE  and t2.XVRPROG=t1.VRPROG
WHERE
t1.VRDIRI='M' AND t1.VRCODA=1 AND t1.VRNUMO=0


SELECT *
FROM
TABLE1 t1
LEFT JOIN
TABLE2 t2
ON
t2.XVRPRTE=t1.VRPRTE  
WHERE
t1.VRDIRI='M' AND t1.VRCODA=1 AND t1.VRNUMO=0

What could be the problem?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mark
  • 1
  • 1
    Please check if you can find help here: https://stackoverflow.com/questions/13075585/why-am-i-getting-a-sql0802-data-conversion-of-data-mapping-error-exception/48078871#48078871 – VBoka Jan 19 '20 at 20:03
  • Please provide the definitions of `t2.xvrprog` and `t1.vrprog`. Also, were the tables defined using SQL or DDS? You can determine that using `DSPFD `. If it is SQL defined, there will see a line on the first page that says `SQL file type`. – jmarkmurphy Jan 22 '20 at 13:47

0 Answers0