Left Function in code

[fatherofthree]

I am trying to update a field one table with a field value in another table. The problem I have is the length of the field I am trying to update is shorter than the length of the field I am trying to update it to. I do not want to change the field lengths so I tried using the LEFT function in the update code. See Below:

UPDATE DOCSADD SET DOCSADD.RET_ADDR1 = (SELECT LEFT(SETDATA.ADDRESS,35) FROM SETDATA WHERE SETDATA.TYPE = '3' AND SETDATA.AP_NUM = DOCSADD.AP_NUM)

The following error comes back:

Error number: -904, position:0-
ORA-00904: invalid column name

What am I doing wrong??

 

[Turkbear]

Hi,
LEFT is not an Oracle SqlPlus function.

Use Subtstr instead:

UPDATE DOCSADD
 SET DOCSADD.RET_ADDR1 = (SELECT SUBSTR(SETDATA.ADDRESS,1,35) FROM SETDATA WHERE SETDATA.TYPE = '3' AND SETDATA.AP_NUM = DOCSADD.AP_NUM)

Hope it helps...

 

[fatherofthree]

Thanks Turkbear. That worked perfectly!!