Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
- Cisco Community
- Technology and Support
- DevNet
- DevNet Collaboration
- Collaboration Knowledge Base
- AXL SQL Update syntax for an update with a join clause
Options
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
894
Views
0
Helpful
0
Comments
Options
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
on 01-24-2014 01:32 AM
This document was generated from CDN thread
Created by: chris crowe on 05-03-2010 12:18:04 AM
I am trying to do a simple update but with a set of complex joins and I do not know the syntax of the AXL to perform this.
My AXL SQL Code is
update numplan
set alertingname = CONCAT(CONCAT(EndUser.firstname, ' '), EndUser.lastname)
from Device
INNER JOIN EndUser ON Device.fkenduser = EndUser.pkid
INNER JOIN DeviceNumPlanMap ON Device.pkid = DeviceNumPlanMap.fkDevice
INNER JOIN NumPlan ON DeviceNumPlanMap.fkNumPlan = NumPlan.pkid
INNER JOIN TypeModel tm on tm.enum = Device .tkmodel and Device.tkclass=1
where (upper(EndUser.userid) = 'CCROWE') and (numplan.alertingname = '') and tm.enum IN (446, 412, 30008, 20000, 369, 6, 307, 30007, 30002, 365, 484, 348, 9, 30019, 431, 8, 115, 309, 434, 435, 7, 30018, 308, 404, 436, 30006, 119, 437, 302, 30016)
I am getting the standard 201 - A syntax error has occurred.
I assume someone just needs to point me in the right idea for the update syntax for an update statement with a join in it.
Cheers
Chris
Subject: RE: AXL SQL Update syntax for an update with a join clause
Replied by: David Staudt on 05-03-2010 02:56:28 AM
Three possible hints:
- The underlying database is Informix IDS. Their online documentation should provide syntax specifics: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp
- Check the AXL service logs (via the RTMT), you can increase the logging verbosity and possibly get more details on the error.
- You can execute SQL via the UCM command line: SSH to the UCM IP address, login and execute:
run sql [my SQL statement here]
You may get more detailed hints/error messages this way.
Subject: RE: AXL SQL Update syntax for an update with a join clause
Replied by: chris crowe on 05-03-2010 10:00:39 PM
Thanks David
Option #1 helped a bit but not much (more below)
Option #2 Not sure how to do this
Option #3 Tried this and got "Syntax error"
OK - So I found a link to the docs on update
http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.sqlt.doc/sqltmst150.htm?resultof=%22%75%70%64%61%74%65%22%20%22%75%70%64%61%74%22%20%22%6a%6f%69%6e%22%20
Basically the syntax is this
UPDATE t SET a = t2.a FROM t, t2 WHERE t.b = t2.b
but it did not help
I reduced my complex SQL to this
update device set alertingname = 'CHRIS' from device, enduser where enduser.userid = 'CCROWE' and device.fkenduser = enduser.pkid
Still syntax error
No better off - maybe it can not do a join in an update? you can not get much simpiler than that.
The only other exampel I have found here that uses an update with a join the user also got Syntax error.
You tried to help him as well David - see http://developer.cisco.com/web/axl/forums/-/message_boards/message/1667790?_19_redirect=%2Fweb%2Faxl%2Fforums%2F-%2Fmessage_boards%2Fsearch%3F_19_redirect%3D%252Fweb%252Faxl%252Fforums%252F-%252Fmessage_boards%252Fcategory%252F1052601%26_19_breadcrum...
chris
Subject: RE: AXL SQL Update syntax for an update with a join clause
Replied by: chris crowe on 05-03-2010 10:10:48 PM
Another user with the exact same issue
https://supportforums.cisco.com/thread/292647.pdf;jsessionid=F8D864ECA2E5ADEB84E3F675F00B27F7.node0
chris
Subject: RE: AXL SQL Update syntax for an update with a join clause
Replied by: David Staudt on 06-03-2010 01:23:07 AM
The doc link you gave is interesting, but it does indicate this is a feature of the IBM 'Extended Parallel Server' a high-end datawharehousing product, I think...UCM uses the Dynamic Server product - more geared toward embedded, high-performance. The effects of this query could be pretty subtle and difficult to implement robustly.
I restated the SQL in a somewhat different way (note device.alerting name doesn't exist in UCM7...what version are you testing?):
update device set description='myPhone' where device.pkid in
(select device.pkid from device, enduser where device.fkenduser=enduser.pkid and enduser.userid='dstaudt')
UCM returned this, more infomative, error:
Cannot modify table or view used in subquery
This kind of hints at the possible difficulties in implementing the syntax the doc link describes.
Based on this, I fear you would need to perform a two-step process:
select device.pkid from device, enduser where
device.fkenduser=enduser.pkid and enduser.userid='dstaudt'
Take the returned id's and concatenate into a string for the update:
update device set description='myPhone' where device.pkid in ('{device-id1-example}','{device-id2-example}',...)
Without spending more time testing, my guess would be you can go ahead and join your other tables to one piece or the other to get the complete effect.
Subject: RE: New Message from David Staudt in Administration XML (AXL) - Administrat
Replied by: chris crowe on 06-03-2010 05:22:58 AM
Thanks for the follow-up David.
Good spotting on my error on the alertingname ¿ it is actually in the NumPlan table (my bad). I am using v6.x
I do not however think that is the cause of the error.
You are probably correct by the looks of things in regards to the SQL implementation being at fault and not supporting joins in the update statements.
I tried or SQL Snippet and got the exact some error message.
I can extract it out and run it manually as you say but I believe that there is a limit of the number of update queries you can run in a specific period of time.
I have not done updates before only selects so it is all new to me.
Thank you very much for taking the time to reply to me, it is very much appreciated.
Cheers
Chris
From: Cisco Developer Community Forums [mailto:cdicuser@developer.cisco.com]
Sent: Saturday, 6 March 2010 2:23 p.m.
To: cdicuser@developer.cisco.com
Subject: New Message from David Staudt in Administration XML (AXL) - Administration XML Questions: RE: AXL SQL Update syntax for an update with a join clause
David Staudt has created a new message in the forum "Administration XML Questions":
--------------------------------------------------------------
The doc link you gave is interesting, but it does indicate this is a feature of the IBM 'Extended Parallel Server' a high-end datawharehousing product, I think...UCM uses the Dynamic Server product - more geared toward embedded, high-performance. The effects of this query could be pretty subtle and difficult to implement robustly.
I restated the SQL in a somewhat different way (note device.alerting name doesn't exist in UCM7...what version are you testing?):
update device set description='myPhone' where device.pkid in
(select device.pkid from device, enduser where device.fkenduser=enduser.pkid and enduser.userid='dstaudt')
UCM returned this, more infomative, error:
Cannot modify table or view used in subquery
This kind of hints at the possible difficulties in implementing the syntax the doc link describes.
Based on this, I fear you would need to perform a two-step process:
select device.pkid from device, enduser where
device.fkenduser=enduser.pkid and enduser.userid='dstaudt'
Take the returned id's and concatenate into a string for the update:
update device set description='myPhone' where device.pkid in ('{device-id1-example}','{device-id2-example}',...)
Without spending more time testing, my guess would be you can go ahead and join your other tables to one piece or the other to get the complete effect.
--
To respond to this post, please click the following link:
<http://developer.cisco.com/web/axl/forums/-/message_boards/message/2019007>
or simply reply to this email.
Created by: chris crowe on 05-03-2010 12:18:04 AM
I am trying to do a simple update but with a set of complex joins and I do not know the syntax of the AXL to perform this.
My AXL SQL Code is
update numplan
set alertingname = CONCAT(CONCAT(EndUser.firstname, ' '), EndUser.lastname)
from Device
INNER JOIN EndUser ON Device.fkenduser = EndUser.pkid
INNER JOIN DeviceNumPlanMap ON Device.pkid = DeviceNumPlanMap.fkDevice
INNER JOIN NumPlan ON DeviceNumPlanMap.fkNumPlan = NumPlan.pkid
INNER JOIN TypeModel tm on tm.enum = Device .tkmodel and Device.tkclass=1
where (upper(EndUser.userid) = 'CCROWE') and (numplan.alertingname = '') and tm.enum IN (446, 412, 30008, 20000, 369, 6, 307, 30007, 30002, 365, 484, 348, 9, 30019, 431, 8, 115, 309, 434, 435, 7, 30018, 308, 404, 436, 30006, 119, 437, 302, 30016)
I am getting the standard 201 - A syntax error has occurred.
I assume someone just needs to point me in the right idea for the update syntax for an update statement with a join in it.
Cheers
Chris
Subject: RE: AXL SQL Update syntax for an update with a join clause
Replied by: David Staudt on 05-03-2010 02:56:28 AM
Three possible hints:
- The underlying database is Informix IDS. Their online documentation should provide syntax specifics: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp
- Check the AXL service logs (via the RTMT), you can increase the logging verbosity and possibly get more details on the error.
- You can execute SQL via the UCM command line: SSH to the UCM IP address, login and execute:
run sql [my SQL statement here]
You may get more detailed hints/error messages this way.
Subject: RE: AXL SQL Update syntax for an update with a join clause
Replied by: chris crowe on 05-03-2010 10:00:39 PM
Three possible hints:
- The underlying database is Informix IDS. Their online documentation should provide syntax specifics: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp
- Check the AXL service logs (via the RTMT), you can increase the logging verbosity and possibly get more details on the error.
- You can execute SQL via the UCM command line: SSH to the UCM IP address, login and execute:
Thanks David
Option #1 helped a bit but not much (more below)
Option #2 Not sure how to do this
Option #3 Tried this and got "Syntax error"
OK - So I found a link to the docs on update
http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.sqlt.doc/sqltmst150.htm?resultof=%22%75%70%64%61%74%65%22%20%22%75%70%64%61%74%22%20%22%6a%6f%69%6e%22%20
Basically the syntax is this
UPDATE t SET a = t2.a FROM t, t2 WHERE t.b = t2.b
but it did not help
I reduced my complex SQL to this
update device set alertingname = 'CHRIS' from device, enduser where enduser.userid = 'CCROWE' and device.fkenduser = enduser.pkid
Still syntax error
No better off - maybe it can not do a join in an update? you can not get much simpiler than that.
The only other exampel I have found here that uses an update with a join the user also got Syntax error.
You tried to help him as well David - see http://developer.cisco.com/web/axl/forums/-/message_boards/message/1667790?_19_redirect=%2Fweb%2Faxl%2Fforums%2F-%2Fmessage_boards%2Fsearch%3F_19_redirect%3D%252Fweb%252Faxl%252Fforums%252F-%252Fmessage_boards%252Fcategory%252F1052601%26_19_breadcrum...
chris
Subject: RE: AXL SQL Update syntax for an update with a join clause
Replied by: chris crowe on 05-03-2010 10:10:48 PM
Another user with the exact same issue
https://supportforums.cisco.com/thread/292647.pdf;jsessionid=F8D864ECA2E5ADEB84E3F675F00B27F7.node0
chris
Subject: RE: AXL SQL Update syntax for an update with a join clause
Replied by: David Staudt on 06-03-2010 01:23:07 AM
The doc link you gave is interesting, but it does indicate this is a feature of the IBM 'Extended Parallel Server' a high-end datawharehousing product, I think...UCM uses the Dynamic Server product - more geared toward embedded, high-performance. The effects of this query could be pretty subtle and difficult to implement robustly.
I restated the SQL in a somewhat different way (note device.alerting name doesn't exist in UCM7...what version are you testing?):
update device set description='myPhone' where device.pkid in
(select device.pkid from device, enduser where device.fkenduser=enduser.pkid and enduser.userid='dstaudt')
UCM returned this, more infomative, error:
Cannot modify table or view used in subquery
This kind of hints at the possible difficulties in implementing the syntax the doc link describes.
Based on this, I fear you would need to perform a two-step process:
select device.pkid from device, enduser where
device.fkenduser=enduser.pkid and enduser.userid='dstaudt'
Take the returned id's and concatenate into a string for the update:
update device set description='myPhone' where device.pkid in ('{device-id1-example}','{device-id2-example}',...)
Without spending more time testing, my guess would be you can go ahead and join your other tables to one piece or the other to get the complete effect.
Subject: RE: New Message from David Staudt in Administration XML (AXL) - Administrat
Replied by: chris crowe on 06-03-2010 05:22:58 AM
Thanks for the follow-up David.
Good spotting on my error on the alertingname ¿ it is actually in the NumPlan table (my bad). I am using v6.x
I do not however think that is the cause of the error.
You are probably correct by the looks of things in regards to the SQL implementation being at fault and not supporting joins in the update statements.
I tried or SQL Snippet and got the exact some error message.
I can extract it out and run it manually as you say but I believe that there is a limit of the number of update queries you can run in a specific period of time.
I have not done updates before only selects so it is all new to me.
Thank you very much for taking the time to reply to me, it is very much appreciated.
Cheers
Chris
From: Cisco Developer Community Forums [mailto:cdicuser@developer.cisco.com]
Sent: Saturday, 6 March 2010 2:23 p.m.
To: cdicuser@developer.cisco.com
Subject: New Message from David Staudt in Administration XML (AXL) - Administration XML Questions: RE: AXL SQL Update syntax for an update with a join clause
David Staudt has created a new message in the forum "Administration XML Questions":
--------------------------------------------------------------
The doc link you gave is interesting, but it does indicate this is a feature of the IBM 'Extended Parallel Server' a high-end datawharehousing product, I think...UCM uses the Dynamic Server product - more geared toward embedded, high-performance. The effects of this query could be pretty subtle and difficult to implement robustly.
I restated the SQL in a somewhat different way (note device.alerting name doesn't exist in UCM7...what version are you testing?):
update device set description='myPhone' where device.pkid in
(select device.pkid from device, enduser where device.fkenduser=enduser.pkid and enduser.userid='dstaudt')
UCM returned this, more infomative, error:
Cannot modify table or view used in subquery
This kind of hints at the possible difficulties in implementing the syntax the doc link describes.
Based on this, I fear you would need to perform a two-step process:
select device.pkid from device, enduser where
device.fkenduser=enduser.pkid and enduser.userid='dstaudt'
Take the returned id's and concatenate into a string for the update:
update device set description='myPhone' where device.pkid in ('{device-id1-example}','{device-id2-example}',...)
Without spending more time testing, my guess would be you can go ahead and join your other tables to one piece or the other to get the complete effect.
--
To respond to this post, please click the following link:
<http://developer.cisco.com/web/axl/forums/-/message_boards/message/2019007>
or simply reply to this email.
Labels:
Getting Started
Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community:
Customers Also Viewed These Support Documents