MQSeries.net :: View topic

warrenpage · Posted: Fri Dec 20, 2002 5:56 pm Post subject:

It appears to me after wasting much time that CSD3 changes the way ESQL functions handle NULL..

Prior to CSD3 the following code worked

SET OutputRoot.XML.root.E = COALESCE(SUBSTRING(InputRoot.XML.root.dontexist FROM 1 FOR 2),'default');

Now even though the SUBSTRING returns NULL, the COALESCE fails to set the output element to 'default'

E.g. The following code

SET OutputRoot.XML.root.A = 'Gday';
SET OutputRoot.XML.root.B = InputRoot.XML.root.dontexist;
SET OutputRoot.XML.root.C = COALESCE(InputRoot.XML.root.dontexist,'');
SET OutputRoot.XML.root.D = COALESCE(InputRoot.XML.root.dontexist,'default');
SET OutputRoot.XML.root.E = COALESCE(SUBSTRING(InputRoot.XML.root.dontexist FROM 1 FOR 2),'default');

is passed the messages <r><e1>Hi</e1></r>

It returns <root><A>Gday</A><C></C><D>default</D></root>

Note that Element E never gets set. I have attached the Trace below.

Also note that you used to be able to return NULL from a function which returns CHARACTERS. Now this is not allowed.

Executing statement 'SET OutputRoot.XML.root.A = 'Gday';' at (11, 1).
Assigning value ''Gday'' to field / variable 'OutputRoot.XML.root.A'.
Executing statement 'SET OutputRoot.XML.root.B = InputRoot.XML.root.dontexist;' at (13, 1).
Evaluating expression 'InputRoot.XML.root.dontexist' at (13, 29).
(13, 39) : Failed to navigate to path element because it does not exist.
Assigning NULL to 'OutputRoot.XML.root.B', thus deleting it.
Executing statement 'SET OutputRoot.XML.root.C = COALESCE(InputRoot.XML.root.dontexist, '');' at (15, 1).
Evaluating expression 'COALESCE(InputRoot.XML.root.dontexist, '')' at (15, 29).
Evaluating expression 'InputRoot.XML.root.dontexist' at (15, 3

.
(15, 4

: Failed to navigate to path element because it does not exist.
Assigning value '''' to field / variable 'OutputRoot.XML.root.C'.
Executing statement 'SET OutputRoot.XML.root.D = COALESCE(InputRoot.XML.root.dontexist, 'default');' at (17, 1).
Evaluating expression 'COALESCE(InputRoot.XML.root.dontexist, 'default')' at (17, 29).
Evaluating expression 'InputRoot.XML.root.dontexist' at (17, 3

.
(17, 4

: Failed to navigate to path element because it does not exist.
Assigning value ''default'' to field / variable 'OutputRoot.XML.root.D'.
Executing statement 'SET OutputRoot.XML.root.E = COALESCE(SUBSTRING(InputRoot.XML.root.dontexist FROM 1 FOR 2), 'default');' at (19, 1).
Evaluating expression 'COALESCE(SUBSTRING(InputRoot.XML.root.dontexist FROM 1 FOR 2), 'default')' at (19, 29).
Evaluating expression 'SUBSTRING(InputRoot.XML.root.dontexist FROM 1 FOR 2)' at (19, 3

.
Evaluating expression 'InputRoot.XML.root.dontexist' at (19, 4

.
(19, 5

: Failed to navigate to path element because it does not exist.
Node 'TestCoalesce.Compute1': Finished evaluating expression 'SUBSTRING(InputRoot.XML.root.dontexist FROM 1 FOR 2)' at (19, 3

. This resolved to 'SUBSTRING(NULL FROM 1 FOR 2)'. The result was 'NULL'.
Message propagated to 'out' terminal of compute node 'TestCoalesce.Compute1'.
The MQ output node 'TestCoalesce.MQOutput1' attempted to write a message to the specified queue 'test2' connected to queue manager ''. The MQCC was 0 and the MQRC was 0.
Message successfully output by MQ output node 'TestCoalesce.MQOutput1' to queue 'test2' on queue manager ''.