| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Using the IN operator with the WHERE clause of a SELECT. |
« View previous topic :: View next topic » |
| Author |
Message
|
| philip.baker |
 Posted: Mon Oct 18, 2010 10:44 am Post subject: Using the IN operator with the WHERE clause of a SELECT. |
 |
|
Voyager
Joined: 21 Mar 2002
Posts: 77
Location: Baker Systems Consulting, Inc. - Tampa
|
Greetings,
I'm back doing MB development.
I believe I'm having an issue understanding some implied activities related to how I would like to do the following processing. (Using MBv6.1.0.8 on WinXP SP3).
Sample input message:
<msg><field1>'1','3','5','A'</field1></msg>
Code fragment.....
DECLARE rField REFERENCE TO InputBody.msg.field1;
DECLARE AnswerB BOOLEAN;
DECLARE cValue CHAR 'A';
SET AnswerB = EXISTS(Select T.* from InputBody.msg[] AS T where cValue IN (rField));
IF AnswerB = TRUE THEN
SET OutputRoot.XMLNSC.msg.fieldvalue = 'Found';
ELSE
SET OutputRoot.XMLNSC.msg.fieldvalue = 'NOT Found';
END IF;
... End code fragment
If I hard-code the values IN ( '1','3','5','A'), AnswerB = TRUE, but it always evals FALSE if I try to use the rField reference.
Anyone know of a way to simpy make this work? (Without parsing the values and building the SELECT statement?)
Thanks,
Phil
_________________
Regards,
Phil |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Mon Oct 18, 2010 10:59 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
I'm not sure what you're trying to do with the field reference or the SELECT.
I'm not sure that the IN clause does what you want it to, either. I don't bleieve that XMLNSC will interpret the contents of field1 as a LIST, but will instead treat it as a single character string.
I would at least try
| Code: |
| SET AnswerB = EXISTS(Select T.* from InputBody.msg[] AS T where cValue IN (T.field1)); |
But I'm not really sure that will do what you want either. |
|
| Back to top |
|
|
| philip.baker |
| Posted: Mon Oct 18, 2010 11:47 am Post subject: |
|
|
Voyager
Joined: 21 Mar 2002
Posts: 77
Location: Baker Systems Consulting, Inc. - Tampa
|
Hi mqjeff,
Thanks for the reply. Obviously the code fragment has been simplified for the purposes of getting an answer on using the dynamically-populated WHERE clause with the IN operator.
There are Loops witin Loops on this one in the real code, but basically the logic needs to validate that an incoming data value within a message exists compared to a queried value for the same data field. The whole solution is data-driven. The 'queried' value (the data in the IN part of the statement) starts out as a field in a database table that has already been selected into Environment.Variables.
The 'queried' value is allowed to be a list of values. (Instead of just having one value (for example - 'A'), the design allows the value to be a list. ('1','2','5','A')). If the incoming message field value does not match the 'queried' value, processing for this record is complete, and the code loops to the next value in the XML input message.
The sample code, as presented, works if I hard-code the list values.
I need to dynamically populate the list. I expected the reference to work and it did not despite the value of the reference looking correct when the debugger displays the rField reference.
I have not tried your exact code and will do so within the fortnight and get back with the results.
Regards,
Phil
_________________
Regards,
Phil |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Oct 18, 2010 12:23 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hello Philip.
You can't do exactly what you are trying to do with the IN operator as the IN operator in 6.1.x requires that all items in the list are "known" in advance at compile time. This is why when you "hard code" the number of items it works. However, a couple of suggestions spring to mind.
| Code: |
DECLARE X INT 42;
--Example 1
DECLARE A INT 39;
DECLARE B INT 40;
DECLARE C INT 41;
DECLARE D INT 43;
SET OutputRoot.XMLNSC.Top.Out1 = X IN( A , B , C , D);
SET D = 42;
SET OutputRoot.XMLNSC.Top.Out2 = X IN( A , B , C , D);
--Example 2
SET Environment.Variables.Tests.Test[1] = 39;
SET Environment.Variables.Tests.Test[2] = 40;
SET Environment.Variables.Tests.Test[3] = 41;
SET Environment.Variables.Tests.Test[4] = 43;
SET OutputRoot.XMLNSC.Top.Out3 = X IN( Environment.Variables.Tests.Test[] );
SET Environment.Variables.Tests.Test[4] = 42;
SET OutputRoot.XMLNSC.Top.Out4 = X IN( Environment.Variables.Tests.Test[] );
|
In 6.1.x and earlier, you would have to use the Example 1 technique, which is to have at compile time a list of the maximum number of necessary variables in the IN operator list which you change to the current values each time you execute your IN. If some of the variables are not needed for a particular interation you can set their values to NULL, in which case the IN operator will return NULL if there is no match.
In 7.0.0.1 the IN operator was extended to allow it to work on LISTS and FieldReferences. This allows you to use a section of the tree to store a dynamic list over which it will iterate each time you call it. For the examples above, the following output message is returned:
| Code: |
<Top><Out1>false</Out1><Out2>true</Out2><Out3>false</Out3><Out4>true</Out4></Top>
|
I hope this helps,
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 |
|
|
| philip.baker |
| Posted: Mon Oct 18, 2010 2:35 pm Post subject: |
|
|
Voyager
Joined: 21 Mar 2002
Posts: 77
Location: Baker Systems Consulting, Inc. - Tampa
|
Thanks, mgk.
I had a feeling I'd have to do it the 'old fashioned way', but I had to give it a shot on this forum. You never know.
I've coded a substring parser for the 'list' of values and built the MySQL character string for each processing loop. I hated to do it because most of the time there will only be a single data value. (But, I won't know that in advance until I parse the string value.)
Thanks for your input.
Regards,
p
_________________
Regards,
Phil |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Oct 18, 2010 2:37 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Given what MGK has just said, it sounds like you are okay if you are at 7.0.0.1.
For 6.1.x and earlier, I might suggest flattening your list into a single string and then using CONTAINS. I do not otherwise comment on performance, merely that it may be successful. |
|
| Back to top |
|
|
| philip.baker |
| Posted: Mon Oct 18, 2010 3:02 pm Post subject: |
|
|
Voyager
Joined: 21 Mar 2002
Posts: 77
Location: Baker Systems Consulting, Inc. - Tampa
|
Hi mqjeff,
I'd love to go to v7.x, but I'm stuck using what the customer has. I'm just a lowly developer on this particular project. I couldn't influence this customer one way or the other.
I like the flattening list suggestion and have used something similiar in the past where I concatenated multiple fields into one before testing, but that won't work so good in this situation. For the sample data I put forward earlier, ( '1','3','5','A'), I'd get 135A. Additionally, I'd still have to parse to get the 135A. I don't want a match on '135', '13', or '35' , etc.
I've got the code working now; I just don't like it.
_________________
Regards,
Phil |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Oct 19, 2010 3:37 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
you'll only get "135A" if you flatten the list that way. You could, for example, get "1,3,5,a" instead. And that would never allow a match on "13", only on "1,3".
But that's more effort. Dunno if that effort is better or worse than your current code. |
|
| Back to top |
|
|
| philip.baker |
| Posted: Tue Oct 19, 2010 1:57 pm Post subject: |
|
|
Voyager
Joined: 21 Mar 2002
Posts: 77
Location: Baker Systems Consulting, Inc. - Tampa
|
Hi mqjeff,
I'm finally getting back to try out the method in your last reply, and I like it. I've transmuted your suggestion a bit and I think it might perform better than my 'substring' approach. I've included the generic code and test cases below proving it out. Thanks. (BTW, you'll notice in the tests that I choose to have the input message as a constant for the tests and changed the code. A bit unconventional, but it proves the case none the less.)
Test 1 Input message:
<msg><field1>1|3|5|A|</field1></msg>
Test 1 source code deployed:
CREATE COMPUTE MODULE SelectSubField_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
CALL CopyMessageHeaders();
DECLARE sInput REFERENCE TO InputBody.msg.field1;
DECLARE AnswerB BOOLEAN;
DECLARE cValue CHAR 'A|';
SET AnswerB = CONTAINS(sInput,cValue);
IF AnswerB = TRUE THEN
SET OutputRoot.XMLNSC.msg.fieldvalue = 'Found';
ELSE
SET OutputRoot.XMLNSC.msg.fieldvalue = 'NOT Found';
END IF;
RETURN TRUE;
END;
Test 1 Output message:
<msg><fieldvalue>Found</fieldvalue></msg>
Test 2 Input message:
<msg><field1>1|3|5|A|</field1></msg>
Test 2 source code deployed:
CREATE COMPUTE MODULE SelectSubField_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
CALL CopyMessageHeaders();
DECLARE sInput REFERENCE TO InputBody.msg.field1;
DECLARE AnswerB BOOLEAN;
DECLARE cValue CHAR 'B|';
SET AnswerB = CONTAINS(sInput,cValue);
IF AnswerB = TRUE THEN
SET OutputRoot.XMLNSC.msg.fieldvalue = 'Found';
ELSE
SET OutputRoot.XMLNSC.msg.fieldvalue = 'NOT Found';
END IF;
RETURN TRUE;
END;
Test 2 Output message:
<msg><fieldvalue>NOT Found</fieldvalue></msg>
_________________
Regards,
Phil |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
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
|
|
|
|
