ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum IndexWebSphere Message Broker (ACE) SupportSQL

Post new topicReply to topic
SQL View previous topic :: View next topic
Author Message
Meow
PostPosted: Fri Dec 12, 2003 11:20 am Post subject: SQL Reply with quote

Voyager

Joined: 25 Jun 2003
Posts: 95
Hi

i get something like this.. and i have to generate a where statement out of this dynamically

<query>
<search-string>( [Employee Number] = "" AND [CNumber] = "" AND [DateOfService] = "" AND [CStatus] = "" AND [CType] = "" AND PNumber = “” AND PName = “”)</search-string>
<search-spec> <node node-type="Binary Operator">
AND
<node node-type="Binary Operator">
=
<node node-type="Identifier">Employee Number</node>
<node value-type="TEXT" node-type="Constant"></node> </node>
<node node-type="Binary Operator">
AND
<node node-type="Binary Operator">
=
<node node-type="Identifier">CNumber</node>
<node value-type="TEXT" node-type="Constant"></node>
</node>
<node node-type="Binary Operator">
AND
<node node-type="Binary Operator">
=
<node node-type="Identifier">DateOfService</node>
<node value-type="DATE" node-type="Constant"></node>
</node>
<node node-type="Binary Operator">
AND
<node node-type="Binary Operator">
=
<node node-type="Identifier">PNumber</node>
<node value-type="TEXT" node-type="Constant"></node>
</node>
<node node-type="Binary Operator">
AND
<node node-type="Binary Operator">
=
<node node-type="Identifier">PName</node>
<node value-type="TEXT" node-type="Constant"></node>
</node>
<node node-type="Binary Operator">
AND
<node node-type="Binary Operator">
=
<node node-type="Identifier">CStatus</node>
<node value-type="TEXT" node-type="Constant"></node></node>
<node node-type="Binary Operator"> =
<node node-type="Identifier">CType</node> <node value-type="TEXT" node-type="Constant"></node> </node>
</node>
</node>
</node>
</node>
</node>
</node>
</search-spec>
</query>

i have to generate a where statement out of this. how do i achieve this.

Thanks in advance
Paruvan
Back to top
View user's profile Send private message
jefflowrey
PostPosted: Fri Dec 12, 2003 12:27 pm Post subject: Reply with quote

Grand Poobah

Joined: 16 Oct 2002
Posts: 19981
You could certainly use EVAL or PASSTHROUGH to execute a complete SQL statement that had been assembled as a string.

You might be able to use the {} with a where clause to also do something similar.
_________________
I am *not* the model of the modern major general.
Back to top
View user's profile Send private message
Meow
PostPosted: Fri Dec 12, 2003 5:14 pm Post subject: Reply with quote

Voyager

Joined: 25 Jun 2003
Posts: 95
Hi Guys,

i am using the following ESQL code to achieve the above. But due to some logical error its going to infinite loop.

can you guys help me with this....

IF ( (myInputRef."search-string" IS NOT NULL) OR (myInputRef."search-spec" IS NOT NULL) ) THEN
DECLARE myStartRef REFERENCE TO myInputRef."search-spec";
IF ((myStartRef.node = 'AND') OR (myStartRef.node = 'OR')) THEN
WHILE (myStartRef IS NOT NULL) DO
SET Operator1 = myStartRef.node;

DECLARE myTempRef REFERENCE TO myStartRef.node;
IF (myTempRef.node[1] = '=') THEN
DECLARE mySearchRef REFERENCE TO myTempRef.node[1];
SET Operand1 = mySearchRef.node[1];
SET Operand2 = mySearchRef.node[2];
SET whereStmt = whereStmt||Operand1 ||' = '||Operand2||' ';
END IF;
IF (myTempRef.node[2] = '=') THEN
SET whereStmt = whereStmt||Operator2||' ';
DECLARE mySearchRef REFERENCE TO myTempRef.node[2];
SET Operand1 = mySearchRef.node[1];
SET Operand2 = mySearchRef.node[2];
SET whereStmt = whereStmt||Operand1 ||' = '||Operand2||' ';
SET myStartRef = NULL;
ELSEIF ((myTempRef.node[2] = 'AND') OR (myTempRef.node[2] = 'OR')) THEN
MOVE myStartRef TO myTempRef.node[2];
SET Operator2 = myTempRef.node[2];
SET whereStmt = whereStmt||Operator1||' ';
END IF;
END WHILE;
END IF;
END IF;

SET OutputRoot.XML.WhereStatement = '('||whereStmt||')';


i knew its something to do with the Reference and the Move functions.
Please help me out.

Thanks in advance,
Paruvan
Back to top
View user's profile Send private message
Meow
PostPosted: Sat Dec 13, 2003 7:37 am Post subject: Reply with quote

Voyager

Joined: 25 Jun 2003
Posts: 95
I found a small bug in the above code but still cant figure that out....

DECLARE myInputRef REFERENCE TO InputRoot.XML.query;
IF ( (myInputRef."search-string" IS NOT NULL) OR (myInputRef."search-spec" IS NOT NULL) ) THEN
DECLARE myStartRef REFERENCE TO myInputRef."search-spec";
IF ((myStartRef.node = 'AND') OR (myStartRef.node = 'OR')) THEN
WHILE (myStartRef IS NOT NULL) DO
SET Operator1 = myStartRef.node;

DECLARE myTempRef REFERENCE TO myStartRef.node;
-- CHANGED myTempRef.node[1] to myTempRef.node
IF (myTempRef.node = '=') THEN
DECLARE mySearchRef REFERENCE TO myTempRef.node;
SET Operand1 = mySearchRef.node[1];
SET Operand2 = mySearchRef.node[2];
SET whereStmt = whereStmt||Operand1 ||' = '||Operand2||' ';
END IF;
-- ADDED THE BELOW LINE AND WAS ABLE TO PROCESS TILL HERE
MOVE myTempRef NEXTSIBLING;
IF (myTempRef.node[2] = '=') THEN
SET whereStmt = whereStmt||Operator2||' ';
DECLARE mySearchRef REFERENCE TO myTempRef.node;
SET Operand1 = mySearchRef.node[1];
SET Operand2 = mySearchRef.node[2];
SET whereStmt = whereStmt||Operand1 ||' = '||Operand2||' ';
SET myStartRef = NULL;
ELSEIF ((myTempRef.node[2] = 'AND') OR (myTempRef.node[2] = 'OR')) THEN
MOVE myStartRef TO myTempRef.node[2];
SET Operator2 = myTempRef.node[2];
SET whereStmt = whereStmt||Operator1||' ';
END IF;
END WHILE;
END IF;
END IF;

SET OutputRoot.XML.WhereStatement = '('||whereStmt||')';


help me out..

thanks in advance
paruvan
Back to top
View user's profile Send private message
jefflowrey
PostPosted: Sat Dec 13, 2003 8:05 am Post subject: Reply with quote

Grand Poobah

Joined: 16 Oct 2002
Posts: 19981
Use LASTMOVE to test if your loop is complete, not "IS NULL".
Code:
while lastmove(myStartRef) do

_________________
I am *not* the model of the modern major general.
Back to top
View user's profile Send private message
Meow
PostPosted: Sat Dec 13, 2003 12:01 pm Post subject: Reply with quote

Voyager

Joined: 25 Jun 2003
Posts: 95
this is the modified code ... please help me.. i tired lastmove but it did not work.so i tried with the counter.

DECLARE myInputRef REFERENCE TO InputRoot.XML.query";

SET whereStmt = '';
SET Operator1 = '';
SET Operator2 = '';
SET Counter = 1;



-- CHECKING FOR SOME VALIDATIONS
IF ( (myInputRef."search-string" IS NOT NULL) AND (myInputRef."search-spec" IS NOT NULL) ) THEN
DECLARE myStartRef REFERENCE TO myInputRef."search-spec";
IF (myStartRef.node = '=') THEN
DECLARE myTempRef REFERENCE TO myStartRef.node;
SET Operand1 = myTempRef.node[1];
SET Operand2 = myTempRef.node[2];
SET whereStmt = whereStmt||Operand1 ||'='|| Operand2||' ';
ELSEIF ((myStartRef.node = 'AND') OR (myStartRef.node = 'OR') ) THEN
WHILE (Counter < 99) DO
SET Operator1 = myStartRef.node;
DECLARE myTempRef REFERENCE TO myStartRef.node;
IF (myTempRef.node[1] = '=') THEN
DECLARE myChildTempRef REFERENCE TO myTempRef.node[1];
SET Operand1 = myChildTempRef.node[1];
SET Operand2 = myChildTempRef.node[2];
SET whereStmt = whereStmt||Operand1 ||'='|| Operand2||' ';
END IF;
SET whereStmt = whereStmt||Operator1||' ';
IF ((myTempRef.node[2] = 'AND') OR (myTempRef.node[2] = 'OR') ) THEN
SET whereStmt = whereStmt||'SO far success';
-- the loop should come here
MOVE myStartRef.node TO myTempRef.node[2];
SET whereStmt =whereStmt ||Operator1||' ';
SET Counter = Counter + 1;
ELSEIF(myTempRef.node[2] = '=') THEN
DECLARE myChildTempRef REFERENCE TO myTempRef.node[2];
SET Operand1 = myChildTempRef.node[1];
SET Operand2 = myChildTempRef.node[2];
SET whereStmt = whereStmt||Operand1||' = '||Operand2||' ';
SET Counter = 99;
END IF;
END WHILE;
END IF;

END IF;


SET OutputRoot.XML.WhereStatement = '('||whereStmt||')';



Thanks in advance,

Paruvan
Back to top
View user's profile Send private message
Display posts from previous:
Post new topicReply to topic Page 1 of 1

MQSeries.net Forum IndexWebSphere Message Broker (ACE) SupportSQL
Jump to:



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
Protected by Anti-Spam ACP


Theme by Dustin Baccetti
Powered by phpBB 2001, 2002 phpBB Group

Copyright MQSeries.net. All rights reserved.