ESQL COMPUTE NODE CODING & STANDARDS

1.Declaration:

DECLARE i INTEGER 1;

DECLARE A CHARACTER 'software systems';

DECLARE i INTEGER CARDINALITY(someinput); // Gives total number of records in the input

DECLARE i INTEGER SUM(someinput);

2.Assigning a input reference to a variable or an assignment operation:

DECLARE inRef REFERENCE TO InputRoot.XMLNSC.DETAILS;

SET outRef.EMP[i].ENAME = inRef.EMP[i].ENAME;

3.Defining a while loop:

WHILE i <=  count  DO

  SET outRef.EMP[i].ENAME = inRef.EMP[i].ENAME;

  SET outRef.EMP[i].LOCATION = inRef.EMP[i].LOCATION;

  SET outRef.EMP[i].BATCH = inRef.EMP[i].BATCH;

  SET i = i + 1;

END WHILE;

4.Creating a xml Field:

CREATE FIELD OutputRoot.XMLNSC.DETAILS.EMP;

-Its always a best practise to use reference to the field created

DECLARE forOutRef REFERENCE TO OutputRoot.XMLNSC.DETAILS.EMP;

5.Creating a next sibling to a xml Field:

CREATE NEXTSIBLING OF forOutRef AS forOutRef

CREATE NEXTSIBLING OF OutputRoot.XMLNSC.Order.Summary.CustomerDetails

NAME 'Address';

6.Deleting a lastchild in a xml:

DELETE LASTCHILD OF OutputRoot.XMLNSC.DETAILS;

7.Creating a Procedure:

CREATE PROCEDURE  mapping (IN inputRef REFERENCE, INOUT inoutRef  REFERENCE )

BEGIN

  

  SET inoutRef.Designation = 'Senior Employee';

  SET inoutRef.Nationality = 'Indian';

  SET inoutRef.Company     = inputRef.COMPANY;

    

END;

8.Defining a for loop:

FOR forRef AS inRef.EMP[]  DO

  SET forOutRef.ENAME    = forRef.ENAME;

  SET forOutRef.LOCATION = forRef.LOCATION;

  SET forOutRef.BATCH    = forRef.BATCH;

        

-- Try to create procedures for reusable of  code.

  CALL mapping(forRef,forOutRef);

  CREATE NEXTSIBLING OF forOutRef AS forOutRef REPEAT;          

END FOR;

9.Defining a Row and Select Statement to access a row in xml:

DECLARE Record ROW;

SET Record.val[] =  SELECT A.BATCH  FROM  OutputRoot.XMLNSC.DETAILS.EMP[]AS A;

10.Casting:

CAST(A.BATCH AS INTEGER)

Cast(InputRoot.XMLNSC.Order.Items.Item[i].Price AS DECIMAL

CCSID InputRoot.MQMD.CodedCharSetId

11.Get the FieldNames and FieldValues from xml:

FIELDNAME(InputRoot.XMLNSC.DETAILS.EMP.ENAME);

FIELDVALUE(InputRoot.XMLNSC.DETAILS.EMP.ENAME);

12.Current Date:

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

13.Switch Case:

-- Calculating the Tax based on state name --

CASE state

WHEN 'NJ' THEN

SET tax=(sum*7)/100;

WHEN 'NY' THEN

SET tax=(sum*9)/100;

WHEN 'CY' THEN

SET tax=(sum*8)/100;

WHEN 'TX' THEN

SET tax=0;

ELSE

SET tax=NULL;

END CASE;

14.Create CHILD With a name

CREATE FIRSTCHILD OF OutputRoot.XMLNSC.Order NAME 'Summary';

15.Create Attributes for Tag of XML

DECLARE ref2 REFERENCE TO OutputRoot.XMLNSC.Order.Summary.Address;

SET ref2.(XMLNSC.Attribute)ccode =ref3.CustomerID;

SET ref2.(XMLNSC.Attribute)State =state;

15.Functions:

LENGTH(A)

LCASE(A)

UCASE(A)

LEFT(A,2)

RIGHT(A,2)

TRIM(A)

SUBSTRING(A FROM 2 FOR 3)

POSITION('i' IN A)

LTRIM(A)

RTRIM(A)

OVERLAY(A PLACING 'ss' FROM 2 FOR 2)

REPLACE(A,'s','ssss')

REPLICATE(A,3)

TRANSLATE(A,'Bhanu','Address')

16.Sending the same data to multiple queues

   

SET OutputLocalEnvironment.Destination.MQ.DestinationData[1].queueName = 'Q3';    

SET OutputLocalEnvironment.Destination.MQ.DestinationData[2.queueName = 'Q4;    

17.Sending the data to another terminal without deleting the original data

   

PROPAGATE TO TERMINAL 'out1' DELETE NONE;

18.Calling External Java Class in ESQL :

CALL ADD() INTO A; -- Function call

CREATE PROCEDURE ADD ( IN A INTEGER,IN B INTEGER) RETURNS INTEGER
LANGUAGE JAVA
EXTERNAL NAME "com.training.add.Addition.Add";; -- CallMe is a methodName in JavaClass