| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Update WHERE question. |
« View previous topic :: View next topic » |
| Author |
Message
|
| billybong |
 Posted: Mon Oct 10, 2005 5:59 am Post subject: Update WHERE question. |
 |
|
Disciple
Joined: 22 Jul 2005
Posts: 150
Location: Stockholm, Sweden
|
Hi all!
This question might be basic, or might not. I've searched quite some time and havent got an answer so please dont flame me if its too easy.
If I have groups of fields (called Bookings), each with a REF_NO and SEQUENCE_NO, is it possible to do an inner join on both fields against a database table? In my table I dont have one primary key but two and it makes the query that much more cumbersome.
My environment fields might look something like this:
<Environment>
<Variables>
<Temp>
<Booking>
<REF_NO>
17869
</REF_NO>
<SEQUENCE_NO>
1
</SEQUENCE_NO>
</Booking>
<Booking>
<REF_NO>
17869
</REF_NO>
<SEQUENCE_NO>
2
</SEQUENCE_NO>
</Booking>
</Temp>
</Variables>
</Environment>
What I want to do is a query like this:
UPDATE Database.X as X
SET REPORT_ID = 'SomeID'
WHERE X.REF_NO = Environment.Variables.Temp.Bookings.REF_NO
AND X.SEQUENCE_NO = Environment.Variables.Temp.Bookings.SEQUENCE_NO;
But the above query only updates the first table row, not the second.
So, I tried using:
UPDATE Database.X as X
SET REPORT_ID = 'SomeID'
WHERE X.REF_NO = Environment.Variables.Temp.Bookings[].REF_NO
AND X.SEQUENCE_NO = Environment.Variables.Temp.Bookings[].SEQUENCE_NO;
But this code wont even deploy.
Any advice? |
|
| Back to top |
|
 |
| hopsala |
| Posted: Mon Oct 10, 2005 8:32 am Post subject: |
|
|
Guardian
Joined: 24 Sep 2004
Posts: 960
|
Hm, what you want is for WBI to dynamically create a number of UPDATE statements in *runtime* according to your array length - and while I find this a nifty concept, I highly doubt this is at all possible, considering the way WBI compiles flows; so other than looping it, I can think of no other solution. (besides, since when does a programmer devoid himself the pleasures of loop-coding?)
I may be way off, though  |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Oct 10, 2005 8:49 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
If I understand your question correctly you should be able to place the UPDATE in a WHILE statement, and loop over all your Booking elements in the environment (hint use CARDINALITY to see how many times you should loop) and then use array indexs to refer to each Booking as you go around the loop e.g. : (psudo code not tested)
SET NumberOfBookingElements = CARDALITY (Environment.Variables.Temp.Booking[]);
WHILE ( I < NumberOfBookingElements ) DO
UPDATE Database.X as X
SET REPORT_ID = 'SomeID'
WHERE X.REF_NO = Environment.Variables.Temp.Bookings[i].REF_NO
AND X.SEQUENCE_NO = Environment.Variables.Temp.Bookings[i].SEQUENCE_NO;
SET I = I +1;
END WHILE;
This will make one update call for each Booking you have
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 |
|
|
| elvis_gn |
| Posted: Mon Oct 10, 2005 10:06 pm Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi billybong,
I dont think there is a way out of looping.....
As mgk's post, the code should work like a charm, just to add to it, you could use a REFERENCE....
i.e (Not tested)
DECLARE Book_Ref REFERENCE TO Environment.Variables.Temp.Booking[1];
WHILE LASTMOVE(Book_Ref) DO
UPDATE Database.X as X
SET REPORT_ID = 'SomeID'
WHERE X.REF_NO = Book_Ref.REF_NO
AND X.SEQUENCE_NO = Book_Ref.SEQUENCE_NO;
MOVE Book_Ref NEXTSIBLING;
END WHILE;
Hope it helps.
Regards. |
|
| Back to top |
|
|
| billybong |
| Posted: Mon Oct 10, 2005 11:23 pm Post subject: |
|
|
Disciple
Joined: 22 Jul 2005
Posts: 150
Location: Stockholm, Sweden
|
| Thanks for the replies guys. It seems that there's no way around using multiple updates in a loop even though I really would like to solve it in a smoother way. I'll go with Elvis suggestion about using REFERENCE since I've read that its more efficient. |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Oct 11, 2005 12:25 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Indeed, a reference is the best way to go.
_________________
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 |
|
|
|
|
|
| |
|
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
|
|
|
|
