| Author |
Message
|
| arun57 |
 Posted: Thu Feb 07, 2008 1:11 am Post subject: esql issue |
 |
|
Novice
Joined: 14 Jan 2008
Posts: 21
|
Hi -
This looks simple but I'm having some issues. I have a table DATACHECK in database.I need to extract the <Name> field from the table corresponding to the id which i am giving as input in form of XML . I want the <name> xml append with the <id> xml as a output. but in output there is only the input <id>xml. no <name> xml is coming. input is coming as it is as output.
here is the input:-
<numbers><id>157116</id></numbers>
here is the code:-
CREATE COMPUTE MODULE DBReadFlow_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
-- CALL CopyMessageHeaders();
CALL CopyEntireMessage();
SET OutputRoot.XMLNSC.numbers.num3 = GetNumber();
RETURN TRUE;
END;
CREATE FUNCTION GetNumber() RETURNS CHARACTER
BEGIN
DECLARE mySum INTEGER 0;
DECLARE nextNumber INTEGER 0;
DECLARE mySumChar CHAR 'undefined';
DECLARE myRef REFERENCE TO OutputRoot.XMLNSC.numbers.num1 ;
WHILE LASTMOVE(myRef) DO
SET nextNumber = THE (SELECT ITEM nt.number_integer
FROM Database.vic.number_text as nt
WHERE nt.number_text = 'FIELDVALUE(myRef)');
SET mySum= mySum + nextNumber;
MOVE myRef NEXTSIBLING;
END WHILE;
SET mySumChar = THE (SELECT ITEM nt.number_text
FROM Database.vic.number_text as nt
WHERE nt.number_integer = mySum);
RETURN mySumChar;
END;
please help. |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Thu Feb 07, 2008 2:19 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
You have to pass in a reference to OutputRoot.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| arun57 |
| Posted: Thu Feb 07, 2008 2:40 am Post subject: |
|
|
Novice
Joined: 14 Jan 2008
Posts: 21
|
| i pass that reference in form of myRef.please tell if i m not correct. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Feb 07, 2008 2:43 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| arun57 wrote: |
| i pass that reference in form of myRef.please tell if i m not correct. |
No.
You create mRef insde your function. It's too late.
You need to send a reference to OutputRoot AS A PARAMETER on your function.
"create function GetNumber(OutputRoot Reference)" or something.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| arun57 |
| Posted: Thu Feb 07, 2008 2:48 am Post subject: |
|
|
Novice
Joined: 14 Jan 2008
Posts: 21
|
| but can u explain what is the need of doing that as we are not using myRef outside the function? please elobrate. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Feb 07, 2008 4:20 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Because the correlation name "OutputRoot" is not visible inside the function scope.
That's the clearest I can be.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Feb 07, 2008 5:22 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Quote: |
| Because the correlation name "OutputRoot" is not visible inside the function scope. |
This is not quite correct since sometime before V6. All the trees (such as OutputRoot, InputRoot etc) are implicitly declared MODULE level variables in ESQL. Therefore all the trees ARE in scope for MODULE level functions. However, they are (obviously) out of scope for SCHEMA level functions because MODULEs only exist inside SCHEMAs. Therefore, to access OutputRoot, you only need to pass a reference to it if it is a SCHEMA level function you are calling. In this case you do not need a reference. Infact, if you did need a reference you would have had an error at deploy telling you something like "The variable OutputRoot could not be found". As you do not get this you are fine in not using a reference. However, why you code is not working as you expect is another matter  For example are you sure that "OutputRoot.XMLNSC.numbers.num1" exists BEFORE you create a reference to it? If it does not, then your lastmove test will terminate immediately. Can you post your input message as well? Is your input message in the XMLNSC domain?
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 |
|
|
| jefflowrey |
| Posted: Thu Feb 07, 2008 5:24 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| arun57 |
| Posted: Sun Feb 10, 2008 9:37 pm Post subject: |
|
|
Novice
Joined: 14 Jan 2008
Posts: 21
|
I am sure that "OutputRoot.XMLNSC.numbers.num1" exist as i copy entire message from input root to output root..my message is in XMLNSC domain.my input is :-
<numbers><id>157116</id></numbers> |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Feb 11, 2008 3:33 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
But, where in your input is the field num1?
You declare a reference to it:
| Code: |
| DECLARE myRef REFERENCE TO OutputRoot.XMLNSC.numbers.num1 |
Yet it does not appear to exist in your input message:
| Code: |
| <numbers><id>157116</id></numbers> |
_________________
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 |
|
|
|
|
