MQSeries.net :: View topic

bindu · Posted: Tue Oct 26, 2004 5:36 pm Post subject:

Hi,
I got the most recent date by using select clause and max function. But i am unable to get the text for that particular date here i am posting code and also request message so advice me if i did any wrong or suggest some ..

request:

<Test><Field1><Value>2004-06-25</Value><Text>ma</Text></Field1><Field2><Value>2000-07-25</Value><Text>dh</Text></Field2><Field3><Value>2004-08-30</Value><Text>u</Text></Field3></Test>

Code:

DECLARE C INTEGER;
SET C = CARDINALITY(InputRoot.*[]);
DECLARE I INTEGER;
SET I = 1;
WHILE I < C DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I=I+1;
END WHILE;
-- Enter SQL below this line. SQL above this line might be regenerated, causing any modifications to be lost.

DECLARE DT DATE;
DECLARE DT1 CHARACTER;
DECLARE DT2 CHARACTER;
DECLARE MSG CHARACTER;
DECLARE i INTEGER;
SET DT = (SELECT MAX(myRef."Value") FROM InputRoot.XML.Test.*[] AS myRef);

SET OutputRoot.XML."xml"."DATE"=DT;

SET i=1;
WHILE(i<= CARDINALITY(InputRoot.XML."Test"."Field"[])) DO
SET DT1 = CAST(InputRoot.XML."Test"."Field"[i]."Value" AS CHARACTER);

SET DT2= CAST(DT AS CHARACTER);
IF(DT1 = DT2) THEN
SET MSG=InputRoot.XML."Test"."Field"[i]."Text";
SET OutputRoot.XML."xml"."VALUE"=MSG;
END IF;
SET i=i+1;
END WHILE;

Any suggestions,

Thanx,
Bindu