asked on

Problem with Decode

I having issues with this decode and I dont understand why?

Thank you

update IMAGE
SET IMAGEASCID = DECODE(NEW_ID, NULL, IMAGEASCID, NEW_ID)
FROM TEMP
WHERE IMAGE_CD = 'Template'
AND IMAGEASCID = PRJOLDID;
COMMIT;

Error starting at line 1 in command:
update IMAGE
SET IMAGEASCID = DECODE(NEW_ID, NULL, IMAGEASCID, NEW_ID)
FROM TEMP
WHERE IMAGETYCD = 'Template'
AND IMAGEASCID = PRJOLDID
Error at Command Line:2 Column:61
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:

Open in new window

Sean Stuber

it's not the DECODE

there is no FROM clause

in an UPDATE statement

SOLUTION

slightwv (䄆 Netminder)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

lulubell-b

ASKER

do I place a where exists then?

update IMAGE I
SET IMAGEASCID = (SELECT DECODE(T.NEW_ID, NULL, I.IMAGEASCID, T.NEW_ID)
FROM TEMP T
WHERE I.IMAGE_CD = 'Template')
where exists (select * from TEMP T
I.IMAGEASCID = T.PRJOLDID;

awking00

Besides the update/from issue, seems like a better function would be -
nvl(new_id, imagescid)

lulubell-b

ASKER

update IMAGE I
SET IMAGEASCID = DECODE(T.NEW_ID, NULL, I.IMAGEASCID, T.NEW_ID)
FROM TEMP T
WHERE I.IMAGE_CD = 'Template')
where exists (select * from TEMP T
I.IMAGEASCID = T.PRJOLDID;

slightwv (䄆 Netminder)

You are back to the original syntax? "SET IMAGEASCID = DECODE(T.NEW"

There is no "update from".

Can you provide the table definitions, some sample data and expected results?

lulubell-b

ASKER

I want to find records from IMAGE where IMAGE_CD = 'Template' and update the IMAGEASCID with either the existing ID or the new ID

CREATE
TABLE "CDB"."IMAGE"
(
"IMAGEID" NUMBER NOT NULL ENABLE,
"IMAGEASCID" NUMBER,
"IMAGE_CD" VARCHAR2(15 BYTE),
"IMAGE_TX" VARCHAR2(15 BYTE),
"IMAGE_SZ" NUMBER)

CREATE
TABLE "CDB"."TEMP"
(
"PRJOLDID" NUMBER,
"NEW_ID" NUMBER,
"PID" NUMBER,
"STATUS" VARCHAR2(1 BYTE)
)

slightwv (䄆 Netminder)

Thanks for the table.

Now that I'm looking at it the requirement seems strange to me. You want to update IMAGEASCID to a 'new' value based on a PRJOLDID=IMAGEASCID from the TEMP table?

I'm not following the relationship between the tables.

Can you provide some sample data and expected results?

lulubell-b

ASKER

TEMP

PRJOLDID NEW_ID
----------------------------------------
1234514462      10547
1234514572      10548
10111            null

10547

IMAGEID IMAGE_CD IMAGEASCID
------------------------------------------------------------------------
1 Template 1234514462

AFTER UPDATE.......
10547

IMAGEID IMAGE_CD IMAGEASCID
------------------------------------------------------------------------
1 Template 10547

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

lulubell-b

ASKER

This worked perfectly. :) nice work around

Thank you