| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| how to select and update |
« View previous topic :: View next topic » |
| Author |
Message
|
| terminator_xyz |
 Posted: Mon Nov 17, 2014 1:15 pm Post subject: how to select and update |
 |
|
Novice
Joined: 29 Jul 2014
Posts: 15
|
Hi tested below code with multiple update statement, but nothing worked.
1. i am doing a select query
2. then i need to update all the rows which i selected in step 1
step 1 is working, but step 2 is not working. Can anyone please help me?
DECLARE cQuery CHARACTER;
SET cQuery='SELECT * FROM '||ext_SchemaName||'.'||ext_Header||' JOIN '|| ext_SchemaName||'.'||ext_Detail||' ON HORD = DORD WHERE HCON = ?'
SET OutputRoot.XMLNSC.TEST[] = PASSTHRU(cQuery,'001');
SET Environment.Variables.ORD[]=SELECT MM.ORD FROM OutputRoot.XMLNSC.TEST[] AS MM;
DECLARE cQuery1 CHARACTER;
SET cQuery1='UPDATE '||ext_SchemaName||'.'||ext_Header||' AS S SET BAT=? WHERE S.ORD IN (?)';
PASSTHRU(cQuery1,'20141117',Environment.Variables.ORD[]); |
|
| Back to top |
|
 |
| Vitor |
| Posted: Mon Nov 17, 2014 1:29 pm Post subject: Re: how to select and update |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| terminator_xyz wrote: |
| step 1 is working, but step 2 is not working. Can anyone please help me? |
Define "not working"; returns SQL code, doesn't update any rows, what?
| terminator_xyz wrote: |
SET Environment.Variables.ORD[]=SELECT MM.ORD FROM OutputRoot.XMLNSC.TEST[] AS MM;
DECLARE cQuery1 CHARACTER;
SET cQuery1='UPDATE '||ext_SchemaName||'.'||ext_Header||' AS S SET BAT=? WHERE S.ORD IN (?)';
PASSTHRU(cQuery1,'20141117',Environment.Variables.ORD[]); |
Can you pass a WMB tree variable to a database IN clause that expects a string?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| terminator_xyz |
| Posted: Mon Nov 17, 2014 1:46 pm Post subject: |
|
|
Novice
Joined: 29 Jul 2014
Posts: 15
|
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Nov 17, 2014 1:51 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| terminator_xyz wrote: |
| "not working" means the table is not getting updated |
Ok
| terminator_xyz wrote: |
| in above link, it is mentionedESQL IN operator accepts a list attribute. |
Yes, but you're not using it in ESQL are you? You're passing it via the PASSTHRU function to the database.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| terminator_xyz |
| Posted: Mon Nov 17, 2014 1:53 pm Post subject: |
|
|
Novice
Joined: 29 Jul 2014
Posts: 15
|
I am not using in ESQL. You are correct
Apart from concatenating (to create like '123','456','111'), do you have any better solution to update the table? |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Nov 18, 2014 5:54 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| terminator_xyz wrote: |
| Apart from concatenating (to create like '123','456','111'), do you have any better solution to update the table? |
You could itterate through the values:
| Code: |
PASSTHRU(cQuery1,'20141117',Environment.Variables.ORD[1]);
PASSTHRU(cQuery1,'20141117',Environment.Variables.ORD[2]);
PASSTHRU(cQuery1,'20141117',Environment.Variables.ORD[3]);
|
(obviously using variable or other method, I've shown the absolute indexes just for clarity)
The question is do you want the overhead of multiple update statements, or do you want the overhead of string processing (which is not WMB's strong suit) in the form of concatenation?
The answer to that will be based on futher questions including but not limited to:
- how many rows are in the database table?
- where are the indexes?
- how many elements will you likely be iterating through?
- could you safely rewrite the flow to issue multiple PASSTHRU (i.e. parallel process) without locking the database?
- what's the SLA of this process?
For example (and only for example) if you only have 3 elements as you show, it's probably better to concatenate. If you have 30, it's probably better to itterate.
Pick a method and go for it. You might want to consider performance testing both methods against production sized data with a small test harness flow to help you decide. You may well see no siginficant difference with small test databases.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
