| View previous topic :: View next topic |
| Author |
Message |
Mukunda
Beginner
Joined: 11 Dec 2002
Posts: 46
Topics: 15
|
 Posted: Tue Nov 22, 2005 3:19 pm Post subject: What is whacky with this query (Update + select) |
 |
|
Gurus of the board
I'm trying to figure out what is wrong with this query but haven't been quite successful yet. I tried searching the board. But could not get much clue either.
| Code: | UPDATE TABLE1 CUSTMAST
SET CUSTMAST.COL1 =
CASE
WHEN CUSTMAST.COL1 = '000000000000000000000000000000'
THEN (SELECT CUOFFA.COL1 FROM
TABLE2 CUOFFA
WHERE CUOFFA.COL2 = CUSTMAST.COL2 AND
CUOFFA.COL3 = CUSTMAST.COL3 AND
CUOFFA.COL4 = CUSTMAST.COL4 AND
CUOFFA.COL1 <> '000000000000000000000000000000' AND
CUSTMAST.COL5 = 390)
ELSE CUSTMAST.COL1
END; |
I get this error
| Code: | ---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL ".". SOME SYMBOLS THAT MIGHT BE
LEGAL ARE: = |
The sub-select inside the CASE works fine alone, but not with the update statement.
Thank you! |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Nov 22, 2005 3:27 pm Post subject: |
|
|
Mukunda,
mukunda,
You cannot have a select with update statement. Try this
| Code: |
UPDATE TABLE1 CUSTMAST
SET CUSTMAST.COL1 = (SELECT CUOFFA.COL1
FROM TABLE2 CUOFFA
WHERE CUOFFA.COL2 = CUSTMAST.COL2
AND CUOFFA.COL3 = CUSTMAST.COL3
AND CUOFFA.COL4 = CUSTMAST.COL4
AND CUOFFA.COL1 <> '000000000000000000000000000000'
AND CUSTMAST.COL5 = 390
AND CUSTMAST.COL1 = '000000000000000000000000000000')
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Mukunda
Beginner
Joined: 11 Dec 2002
Posts: 46
Topics: 15
|
| Posted: Tue Nov 22, 2005 5:36 pm Post subject: |
|
|
Kolusu
Thanks for the quick response!
Infact, I tried that and later thought the format is inappropriate
| Code: | UPDATE XXXX.TABLE1 CUSTMAST
SET CUSTMAST.END_CUST_NAME =
(SELECT CUOFFA.END_CUST_NAME FROM
XXXX.TABLE2 CUOFFA
WHERE CUOFFA.END_CUST_ACCOUNT = CUSTMAST.END_CUST_ACCOUNT AND
CUOFFA.APPL_CUST_ID = CUSTMAST.APPL_CUST_ID AND
CUOFFA.PL_BANK_NBR = CUSTMAST.PL_BANK_NBR AND
CUOFFA.END_CUST_NAME <> '000000000000000000000000000000' AND
CUSTMAST.SOURCE_ID = 390 AND
CUSTMAST.END_CUST_NAME = '000000000000000000000000000000');
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL ".". SOME SYMBOLS THAT MIGHT BE
LEGAL ARE: = |
May be I'm missing something silly  |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Nov 22, 2005 6:14 pm Post subject: |
|
|
Mukunda,
You don't need to qualify the updating column as well as the select column as you are refering them from the table.
Try this
| Code: |
UPDATE XXXX.TABLE1 CUSTMAST
SET END_CUST_NAME = (SELECT END_CUST_NAME
FROM XXXX.TABLE2 CUOFFA
WHERE CUOFFA.END_CUST_ACCOUNT = CUSTMAST.END_CUST_ACCOUNT
AND CUOFFA.APPL_CUST_ID = CUSTMAST.APPL_CUST_ID
AND CUOFFA.PL_BANK_NBR = CUSTMAST.PL_BANK_NBR
AND CUOFFA.END_CUST_NAME <> '000000000000000000000000000000'
AND CUSTMAST.SOURCE_ID = 390
AND CUSTMAST.END_CUST_NAME = '000000000000000000000000000000');
|
If this also gives error, then copy this sql into QMF and run it, QMF points the exact error line and position of the error
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Mukunda
Beginner
Joined: 11 Dec 2002
Posts: 46
Topics: 15
|
| Posted: Wed Nov 23, 2005 1:17 pm Post subject: |
|
|
Thanks Kolusu. It works fine...I just had to take care of -407. Thanks for your time  |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
