| Author |
Message
|
| McueMart |
 Posted: Thu Aug 29, 2013 12:48 am Post subject: ESQL nuance... |
 |
|
Chevalier
Joined: 29 Nov 2011
Posts: 490
Location: UK...somewhere
|
Im trying to figure our why the following code behaves the way it does (Using WMB 8.0.0.2):
Check out this code:
| Code: |
SET Environment.Variables.MyEnvVar = '2';
SET Environment.Variables.MyEnvVar = '2';
DECLARE Number3 CHARACTER '3';
SET OutputRoot.XMLNSC.test1 = SELECT
'1',
Environment.Variables.MyEnvVar,
Number3,
'4'
FROM Environment;
SET OutputRoot.XMLNSC.test2 = SELECT
'1',
Environment.Variables.MyEnvVar,
COALESCE(Number3,''),
'4'
FROM Environment;
SET OutputRoot.XMLNSC.test3.Data[] = LIST{
'1',
Environment.Variables.MyEnvVar,
Number3,
'4'};
SET OutputRoot.XMLNSC.test4.Data[] = LIST{
'1',
Environment.Variables.MyEnvVar,
COALESCE(Number3,''),
'4'};
|
It forms the output tree:
Or (if you prefer trace!):
| Code: |
(0x01000000:Folder):XMLNSC = ( ['xmlnsc' : 0x1a175b0]
(0x03000000:PCDataField):test1 = '3' (CHARACTER)
(
(0x03000000:PCDataField):Column0 = '1' (CHARACTER)
(0x03000000:PCDataField):MyEnvVar = '2' (CHARACTER)
(0x03000000:PCDataField):Column3 = '4' (CHARACTER)
)
(0x01000000:Folder ):test2 = (
(0x03000000:PCDataField):Column0 = '1' (CHARACTER)
(0x03000000:PCDataField):MyEnvVar = '2' (CHARACTER)
(0x03000000:PCDataField):Column2 = '3' (CHARACTER)
(0x03000000:PCDataField):Column3 = '4' (CHARACTER)
)
(0x01000000:Folder ):test3 = (
(0x03000000:PCDataField):Data = '1' (CHARACTER)
(0x03000000:PCDataField):Data = '2' (CHARACTER)
(0x03000000:PCDataField):Data = '3' (CHARACTER)
(0x03000000:PCDataField):Data = '4' (CHARACTER)
)
(0x01000000:Folder ):test4 = (
(0x03000000:PCDataField):Data = '1' (CHARACTER)
(0x03000000:PCDataField):Data = '2' (CHARACTER)
(0x03000000:PCDataField):Data = '3' (CHARACTER)
(0x03000000:PCDataField):Data = '4' (CHARACTER)
)
)
|
So is this a defect when using SELECT with a CHARACTER? Or is this working as expected? (If so why?). |
|
| Back to top |
|
 |
| dogorsy |
| Posted: Thu Aug 29, 2013 1:59 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
First of all, the code is wrong as you are creating a message that does not contain a unique root, i.e. you will have
| Code: |
<test1>
.
.
</test1>
<test2>
.
.
</test2>
|
etc. which is malformed XML
Next, what is wrong with what is being generated ?, what are you expecting ? What do you think is a defect and why ? |
|
| Back to top |
|
|
| McueMart |
| Posted: Thu Aug 29, 2013 2:05 am Post subject: |
|
|
Chevalier
Joined: 29 Nov 2011
Posts: 490
Location: UK...somewhere
|
Yes I know i'm creating invalid XML. That is totally unrelated to what I am trying to query with my post as it's more to do with the structure of the logical tree which has been produced.
I had assumed it was pretty obvious what I was expecting. I will be more clear:
You can see for test2,test3 and test4 they are created as Folder types with 4 sub elements (PCDataFieldType).
Whereas for test1 it is created with a type of PCDataField, and the value '3' is assigned to it.
If you study the ESQL, can you see WHY this would be the case? Compare it to the 3 other cases. |
|
| Back to top |
|
|
| dogorsy |
| Posted: Thu Aug 29, 2013 2:12 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
yes, I can see it now, but it helps if you spell it out.
I can see that test1 contains the value 3 , which is wrong, but the syntax is wrong as well and I am surprised you did not get an error, you should have the square brackets after test1
| Code: |
| SET OutputRoot.XMLNSC.test1[] = etc |
and the same for the others...
so, try adding the square brackets and see if that makes any difference.
Also, do one at the time (i.e. set test1 and output it, then set test2 and output it), the two lists are not adding anything new. |
|
| Back to top |
|
|
| McueMart |
| Posted: Thu Aug 29, 2013 2:20 am Post subject: |
|
|
Chevalier
Joined: 29 Nov 2011
Posts: 490
Location: UK...somewhere
|
No it's not invalid syntax to use
| Code: |
SET OutputRoot.XMLNSC.test1 = SELECT ...
|
In this instance. Using
| Code: |
SET OutputRoot.XMLNSC.test1[] = SELECT ...
|
IS reported as invalid syntax.
Still the difference between 'test1' and 'test2' is simply that 'test2' does a COALESCE around the Number3 variable. This results in the different logical tree being returned. This is what confuses me....! |
|
| Back to top |
|
|
| dogorsy |
| Posted: Thu Aug 29, 2013 2:33 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| McueMart wrote: |
No it's not invalid syntax to use
| Code: |
SET OutputRoot.XMLNSC.test1 = SELECT ...
|
In this instance. Using
| Code: |
SET OutputRoot.XMLNSC.test1[] = SELECT ...
|
IS reported as invalid syntax.
Still the difference between 'test1' and 'test2' is simply that 'test2' does a COALESCE around the Number3 variable. This results in the different logical tree being returned. This is what confuses me....! |
why do you say it is not invalid syntax ? you are assigning a list to a scalar, that should be a defect, the fact that the toolkit accepts it and it executes does not mean that is valid. and probably that is why you are getting unpredictable results. Maybe you should report it as a PMR |
|
| Back to top |
|
|
| McueMart |
| Posted: Thu Aug 29, 2013 2:53 am Post subject: |
|
|
Chevalier
Joined: 29 Nov 2011
Posts: 490
Location: UK...somewhere
|
| Well my hopes of posting here was to get confirmation (from the dev team), without going through the PMR process, of what is valid ESQL and working as expect, what is valid ESQL and not working as expected (will raise PMR), or as you are suggesting, what is invalid ESQL but is actually executing successfully (will raise PMR). |
|
| Back to top |
|
|
| dogorsy |
| Posted: Thu Aug 29, 2013 3:05 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| McueMart wrote: |
| Well my hopes of posting here was to get confirmation (from the dev team), without going through the PMR process, of what is valid ESQL and working as expect, what is valid ESQL and not working as expected (will raise PMR), or as you are suggesting, what is invalid ESQL but is actually executing successfully (will raise PMR). |
ok, yes, I agree that test1 and test2 should give the same result , i.e. the COALESCE function should not make any difference to the end result and understand why you are puzzled. Still, I think the syntax is incorrect , it also lets you do a select from Environment ( rather than Environment[] ) which I find odd. |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Aug 29, 2013 7:47 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
OK, so there seems to be a little confusion between list and results and none list results. When you SELECT from a message tree, the FROM list determines the "listness" of the result. Essentially the presence or absence of [] on the items in the FROM determine if one or more rows will be returned.
Case 1:
| Code: |
| SELECT ... FROM Environment.Variables |
This means a single row will be returned.
Case 2:
| Code: |
| SELECT from Environment.Variables[] |
This means one row will be returned for each repeating Variables folder.
If you have a list return (case 2) then you need to use [] on the target of the assignment. For a none list result (case 1) you cannot use [] on the assignment as its not a list. And finally, you can't put [] directly on 'Environment' as it's a Correlation Name.
Hopefully this clears up some of the confusion. However, for the original question, as to the difference between test one and test two it looks like a small defect. Internally there will be a difference between assigning a scalar variable and a function result and it looks like an issue in the result assignment in the case of the scalar when performing the SELECT. However, I'm not at work this week, so I can't check into any more details. I suggest you raise a PMR for a definitive answer.
Kind 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 |
|
|
| dogorsy |
| Posted: Thu Aug 29, 2013 8:16 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| mgk wrote: |
OK, so there seems to be a little confusion between list and results and none list results. When you SELECT from a message tree, the FROM list determines the "listness" of the result. Essentially the presence or absence of [] on the items in the FROM determine if one or more rows will be returned.
Case 1:
| Code: |
| SELECT ... FROM Environment.Variables |
This means a single row will be returned.
Case 2:
| Code: |
| SELECT from Environment.Variables[] |
This means one row will be returned for each repeating Variables folder.
If you have a list return (case 2) then you need to use [] on the target of the assignment. For a none list result (case 1) you cannot use [] on the assignment as its not a list. And finally, you can't put [] directly on 'Environment' as it's a Correlation Name.
Hopefully this clears up some of the confusion. However, for the original question, as to the difference between test one and test two it looks like a small defect. Internally there will be a difference between assigning a scalar variable and a function result and it looks like an issue in the result assignment in the case of the scalar when performing the SELECT. However, I'm not at work this week, so I can't check into any more details. I suggest you raise a PMR for a definitive answer.
Kind regards, |
Thanks for clarifying mgk ! |
|
| Back to top |
|
|
| McueMart |
| Posted: Fri Aug 30, 2013 12:31 am Post subject: |
|
|
Chevalier
Joined: 29 Nov 2011
Posts: 490
Location: UK...somewhere
|
Yep cheers for the clarification. Now I can sleep at night  |
|
| Back to top |
|
|
|
|
