| Author |
Message
|
| akidase |
 Posted: Wed Mar 02, 2016 7:05 pm Post subject: ESQL Coding problem? |
 |
|
Centurion
Joined: 10 Jan 2011
Posts: 124
|
Hi Everyone,
I am using v7 Broker.
| Code: |
SET Environment.Data.IdsToInsert[] = SELECT xml1.no from InputRoot.XMLNSC.Root.xml.unit[] as xml1
WHERE xml1.no NOT IN ( SELECT ITEM db1.no from Environment.Data.Temp.Test[] as db1 ); |
Is not working
| Code: |
SET Environment.Data.IdsToInsert[] = SELECT xml1.no from InputRoot.XMLNSC.Root.xml.unit[] as xml1
WHERE xml1.no IN ( SELECT ITEM db1.no from Environment.Data.Temp.Test[] as db1 ); |
is working. Working in the sense, it is returning a result set.
I am not able to understand , why the code is not able to select the values which are not in the second list. Please help. I am spent quite sometime looking at various options but couldn't do. |
|
| Back to top |
|
 |
| maurito |
| Posted: Wed Mar 02, 2016 11:55 pm Post subject: Re: ESQL Coding problem? |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| akidase wrote: |
Hi Everyone,
I am using v7 Broker.
| Code: |
SET Environment.Data.IdsToInsert[] = SELECT xml1.no from InputRoot.XMLNSC.Root.xml.unit[] as xml1
WHERE xml1.no NOT IN ( SELECT ITEM db1.no from Environment.Data.Temp.Test[] as db1 ); |
Is not working
| Code: |
SET Environment.Data.IdsToInsert[] = SELECT xml1.no from InputRoot.XMLNSC.Root.xml.unit[] as xml1
WHERE xml1.no IN ( SELECT ITEM db1.no from Environment.Data.Temp.Test[] as db1 ); |
is working. Working in the sense, it is returning a result set.
I am not able to understand , why the code is not able to select the values which are not in the second list. Please help. I am spent quite sometime looking at various options but couldn't do. |
Have you obtained a user trace ?. If not, get a user trace with the -l debug flag and analyse it.
Only you know what to expect in both cases, what values are held in the Environment.Data tree, etc. so anything we can say is pure guesswork. |
|
| Back to top |
|
|
| akidase |
| Posted: Thu Mar 03, 2016 6:21 pm Post subject: |
|
|
Centurion
Joined: 10 Jan 2011
Posts: 124
|
| Code: |
| 2016-03-04 07:29:37.864304 9412 UserTrace BIP2539I: Node 'MsgFlow.Compute': Evaluating expression ''CAST(xml.no AS INTEGER) NOT IN ((SELECT CAST(COLUMN(0) AS INTEGER) AS :no FROM DATABASE()))'' at ('.MsgFlow_Compute.Main', '5.34'). This resolved to ''1 IN (1)''. The result was ''TRUE''. |
I don't find the NOT appearing when evaluation somehow... causing this error. |
|
| Back to top |
|
|
| maurito |
| Posted: Thu Mar 03, 2016 10:48 pm Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| akidase wrote: |
| Code: |
| 2016-03-04 07:29:37.864304 9412 UserTrace BIP2539I: Node 'MsgFlow.Compute': Evaluating expression ''CAST(xml.no AS INTEGER) NOT IN ((SELECT CAST(COLUMN(0) AS INTEGER) AS :no FROM DATABASE()))'' at ('.MsgFlow_Compute.Main', '5.34'). This resolved to ''1 IN (1)''. The result was ''TRUE''. |
I don't find the NOT appearing when evaluation somehow... causing this error. |
I can see the NOT in the above statement just before the IN. And that statement is not the one you posted in your original query. It has a CAST |
|
| Back to top |
|
|
| akidase |
| Posted: Thu Mar 03, 2016 11:53 pm Post subject: |
|
|
Centurion
Joined: 10 Jan 2011
Posts: 124
|
| Quote: |
| I can see the NOT in the above statement just before the IN. And that statement is not the one you posted in your original query. It has a CAST |
But not in
| Code: |
| This resolved to ''1 IN (1)''. |
rt
I was thinking it should be
| Code: |
| This resolved to ''1 NOT IN (1)''. |
so it would be false and not true. |
|
| Back to top |
|
|
|
|
