| Author |
Message
|
| cayenne |
 Posted: Tue Jan 30, 2007 8:52 am Post subject: [where ?] UPDATE statement with nested SELECT syntax error? |
 |
|
Newbie
Joined: 07 Dec 2006
Posts: 8
|
Hello all,
I'm quite the MQ noob, and I'm also trying to hook to a DB2 database, my background is from Oracle. I'm getting a fairly non-specific syntax error message in my toolkit from this bit of ESQL where I'm trying to update a table value from a lookup table where the joining condition is DEPTID.
I know I'm a bit rusty, but, not sure where the problem is here.
I've set these two variables:
DECLARE v_database NAME 'DATABASE1';
DECLARE v_schema NAME 'SCHEMAOWNER'; --subbed fake names for post
UPDATE Database.v_database.v_schema.PS_DEPT_TBL A SET (A.Z_ALT_LOCATION) = (SELECT B.KEY1_FLD_VAL FROM Database.v_database.v_schema.PS_Z_XREF_DTL B WHERE B.KEY1 = A.DEPTID AND B.MAPNAME = 'NEW LOCATION LOOKUP') WHERE A.SETID = 'XYZ';
I've changed a couple values just for posting here, but, the real values I have are valid...I have tested this from direct queries to the DB2 database instance.
Any ideas or pointers to information out there? I think the sql is sound, but, if I've blown that...please let me know...I can't see the problem yet.
TIA,
cayenne 
Last edited by cayenne on Wed Jan 31, 2007 11:18 am; edited 1 time in total |
|
| Back to top |
|
 |
| elvis_gn |
| Posted: Tue Jan 30, 2007 9:13 am Post subject: Re: Trying an UPDATE statement with nested SELECT, syntax er |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi cayenne,
| cayenne wrote: |
DECLARE v_database NAME 'DATABASE1';
DECLARE v_schema NAME 'SCHEMAOWNER'; --subbed fake names for post
UPDATE Database.v_database.v_schema.PS_DEPT_TBL A SET (A.Z_ALT_LOCATION) = (SELECT B.KEY1_FLD_VAL FROM Database.v_database.v_schema.PS_Z_XREF_DTL B WHERE B.KEY1 = A.DEPTID AND B.MAPNAME = 'NEW LOCATION LOOKUP') WHERE A.SETID = 'XYZ'; |
Declare name ?? U could have used CHAR.
UPDATE table AS A SET....similarly AS B...
Regards. |
|
| Back to top |
|
|
| cayenne |
| Posted: Tue Jan 30, 2007 10:27 am Post subject: Re: Trying an UPDATE statement with nested SELECT, syntax er |
|
|
Newbie
Joined: 07 Dec 2006
Posts: 8
|
| elvis_gn wrote: |
Hi cayenne,
Declare name ?? U could have used CHAR.
UPDATE table AS A SET....similarly AS B...
Regards. |
Strange....when I did my first database access with MQ...I tried CHARACTER, but, it wouldn't work, I saw the NAME was used in this instance on an example in the IBM docs....and it worked...so, I went with that.
Ok..I'll try the 'AS" part in there...is this a DB2 thing? I come from an Oracle background. I did find some IBM documentation, and was working to be similar to what they did:
| Code: |
UPDATE EMPLOYEE EU
SET (EU.SALARY, EU.COMM)
=
(SELECT AVG(ES.SALARY), AVG(ES.COMM)
FROM EMPLOYEE ES
WHERE ES.WORKDEPT = EU.WORKDEPT)
WHERE EU.EMPNO = '000120' |
They didn't use the AS statement...so, didn't think to try this.
Ok, just tried adding AS before A and B...and it gives a new syntax error...with about every possible option you could think of.
I've changed it to:
| Code: |
DECLARE v_database NAME 'DATABASE1';
DECLARE v_schema NAME 'SCHEMAOWNER'; --subbed fake names for post
UPDATE Database.v_database.v_schema.PS_DEPT_TBL AS A SET (A.Z_ALT_LOCATION) = (SELECT B.KEY1_FLD_VAL FROM Database.v_database.v_schema.PS_Z_XREF_DTL AS B WHERE B.KEY1 = A.DEPTID AND B.MAPNAME = 'NEW LOCATION LOOKUP') WHERE A.SETID = 'XYZ'; |
But, this still shows syntax error when I try to save it....
Is there any other info I can post that might help?
Thanks and please keep the suggestions coming.
cayenne |
|
| Back to top |
|
|
| jmac |
| Posted: Tue Jan 30, 2007 1:45 pm Post subject: |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
Can Someone tell me how this relates to MQWorkflow?
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
| cayenne |
| Posted: Wed Jan 31, 2007 11:16 am Post subject: sorry? |
|
|
Newbie
Joined: 07 Dec 2006
Posts: 8
|
| jmac wrote: |
| Can Someone tell me how this relates to MQWorkflow? |
I'm new to the forum...this is a problem I'm having in a message flow I'm working on...
Whbic forum would this would this be best posted to? |
|
| Back to top |
|
|
| jmac |
| Posted: Wed Jan 31, 2007 11:34 am Post subject: |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
I would say the Websphere Business Integration Support (WBI/WMQI/WMB/MQSI) forum. I moved it.
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
| gregop |
| Posted: Thu Feb 01, 2007 12:11 am Post subject: |
|
|
Voyager
Joined: 24 Nov 2006
Posts: 81
|
| Quote: |
| UPDATE Database.v_database.v_schema.PS_DEPT_TBL AS A SET (A.Z_ALT_LOCATION) = (SELECT B.KEY1_FLD_VAL FROM Database.v_database.v_schema.PS_Z_XREF_DTL AS B WHERE B.KEY1 = A.DEPTID AND B.MAPNAME = 'NEW LOCATION LOOKUP') WHERE A.SETID = 'XYZ'; |
Remove brackets from A.Z_ALT_LOCATION
That should eliminate your syntax error
UPDATE Database.v_database.v_schema.PS_DEPT_TBL AS A SET A.Z_ALT_LOCATION = (SELECT B.KEY1_FLD_VAL FROM Database.v_database.v_schema.PS_Z_XREF_DTL AS B WHERE B.KEY1 = A.DEPTID AND B.MAPNAME = 'NEW LOCATION LOOKUP') WHERE A.SETID = 'XYZ'; |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Feb 01, 2007 12:18 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
The ESQL UPDATE statement does not support a SELECT to update multiple rows at once, only a VALUES clause to update a single row at once. You could try using PASSTHRU instead.
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
|
|
