| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
| DB statement issue |
« View previous topic :: View next topic » |
| Author |
Message
|
| koobz |
 Posted: Fri Feb 28, 2014 5:11 am Post subject: |
 |
|
Novice
Joined: 26 Mar 2013
Posts: 11
|
I've struggled with Oracle sequences also. Appreciate this is an old thread but as I am stuck with v6.1 due to a management decision, I'm hoping its appropriate to continue this one.
I've searched the forums and not come across a solution - ideally it would be great to have a link to some comprehensive documentation on this.
I want to select a value from a sequence so I can use it as a primary key and foreign key for parent and child records that I am inserting in my flow.
| Code: |
SET Environment.Variables.INID[] = PASSTHRU('select movilizer_in_seq.nextval nextval from dual') ;
INSERT INTO Database.MOVILIZER_INBOUND
( INID, SELECTED_LOCATION, )
VALUES (Environment.Variables.INID.NEXTVAL, FIELDVALUE(Environment.Variables.SelectedLocation)) ;
Move eqRef to eqRef.*:replyAnswer;
WHILE LASTMOVE(eqRef) DO
IF eqRef.answerKey='5_0' then
INSERT INTO Database.MOVILIZER_INBOUND_EQ_MOVES
( inid, EQUIPMENT)
VALUES (Environment.Variables.INID.NEXTVAL, eqRef.clientKey) ;
END If;
MOVE eqRef NEXTSIBLING REPEAT TYPE NAME;
END WHILE ;
|
I played around with various ways of referencing the sequence but the child insert seemed to always use a different sequence value (bizarrely a lower value) or null.
Its not clear to me from the 6.1 infocenter documentation when PASSTHRU statements are evaluated.
http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r1m0/index.jsp?topic=%2Fcom.ibm.etools.mft.doc%2Fac67241_.htm
I guess what I need is a way of evaluating it once, sticking in an a variable, and then to be able to use that same value as and when needed.
I could only get it to work by declaring a 2nd environment variable to point at CURRVAL:
| Code: |
SET Environment.Variables.INID2[] = PASSTHRU('select movilizer_in_seq.currval + 1 currval from dual') ;
|
Which in my case is just about workable because nothing else should be referencing altering the sequence, but in another situation the CURRVAL might have been incremented by some other caller.
Notice also that I am having to add 1 because it appears to be getting evaluated before the earlier lines of ESQL that reference NEXTVAL and insert in to the parent table.
I didn't try chandu's solution because I didn't understand it, it doesn't look syntactically correct (missing quote maybe), and it wasn't clear if it led to X being consistent when referenced multiple times.
| Code: |
DECLARE X ROW;
DECLARE Y INTEGER;(it is declared as NUMBER in Oracle)
SET X = Passthru('cast(select SEQ.nextval as VARCHAR(10) from sys.dual);
|
|
|
| Back to top |
|
 |
| Gralgrathor |
| Posted: Fri Feb 28, 2014 5:22 am Post subject: |
|
|
Master
Joined: 23 Jul 2009
Posts: 297
|
I've had too little sleep last night and I'm feeling a bit woozy in the head.
Could you explain in simple meta-code what you're trying to achieve, using generic terms?
_________________
A measure of wheat for a penny, and three measures of barley for a penny; and see thou hurt not the oil and the wine. |
|
| Back to top |
|
|
| koobz |
| Posted: Fri Feb 28, 2014 6:02 am Post subject: |
|
|
Novice
Joined: 26 Mar 2013
Posts: 11
|
I didn't get much sleep either but I don't have the woozy head, so I expect your night was better than mine 
| Code: |
Set environmentVariable = passthru( select id from oracle sequence)
insert into parentTable (ID) values (environmentVariable );
#for each child xml element
insert into childTable (ForiegnKeyToParentTable) values (environmentVariable);
|
-- So the environmentVariable needs to have the same value for all inserts.
Hope that makes sense... |
|
| Back to top |
|
|
| Gralgrathor |
| Posted: Mon Mar 03, 2014 6:10 am Post subject: |
|
|
Master
Joined: 23 Jul 2009
Posts: 297
|
| koobz wrote: |
| So the environmentVariable needs to have the same value for all inserts |
Right, so you assign the contents of a query on a sequence to an environment variable, and counter to expectations you see this variable change during the insert-loop.
Did you make any headway with this problem?
Out of curiosity, what do you see when you assign the insert key to a locally declared variable and then run a trace to track changes to that variable?
_________________
A measure of wheat for a penny, and three measures of barley for a penny; and see thou hurt not the oil and the wine. |
|
| Back to top |
|
|
|
|
|
|
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
|
|
|
|
