• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 665
  • Last Modified:

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

0
lulubell-b
Asked:
lulubell-b
2 Solutions
 
sdstuberCommented:
it's not the DECODE

there is no FROM clause

in an UPDATE statement
0
 
slightwv (䄆 Netminder) Commented:
You don't UPDATE FROM.

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


Be careful here because it will UPDATE ALL rows since the where clause is on the SELECT not the UPDATE.
0
 
lulubell-bAuthor Commented:
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;
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
awking00Commented:
Besides the update/from issue, seems like a better function would be -
nvl(new_id, imagescid)
0
 
lulubell-bAuthor Commented:
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;
0
 
slightwv (䄆 Netminder) Commented:
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?
0
 
lulubell-bAuthor Commented:
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)
  )
0
 
slightwv (䄆 Netminder) Commented:
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?
0
 
lulubell-bAuthor Commented:
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


      
0
 
slightwv (䄆 Netminder) Commented:
See if this works.  I'm still not 100% sure.

update image i
set imageascid= (select new_id from temp t where new_id is not null and i.imageascid=t.prjoldid)
where exists (select 1 from temp t where i.image_cd='Template' and i.imageascid=t.prjoldid);

0
 
lulubell-bAuthor Commented:
This worked perfectly. :) nice work around

Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now