COALESCE Function

As we know,it is used to provide default values.It returns the first not null value.

SET OutputRoot.XMLNSC.COALESCE = COALESCE(InputRoot.XMLNSC.input1,0);
--SET OutputRoot.XMLNSC.COALESCE1 =
COALESCE(InputRoot.XMLNSC.input12,0);
-- <input1>12</input1>
------------------------------------------SET OutputRoot.XMLNSC.employee.(XMLNSC.Attribute)id ='123';
-------UUIDASCHAR,UUIDASBLOB---------------------SET OutputRoot.XMLNSC.emp = UUIDASCHAR;
--SET OutputRoot.XMLNSC.emp1 = UUIDASBLOB;
--------------------------------------------------DECLARE arg1,arg2 INTEGER;
--SET arg1 = InputRoot.XMLNSC.args.arg1;
--SET arg2 = InputRoot.XMLNSC.args.arg2;
--SET OutputRoot.XMLNSC.res.res1 = NULLIF(arg1,arg2);
--CASE WHEN arg1=arg2 THEN NULL ELSE arg1 END
--<args><arg1>2</arg1><arg2>2</arg2></args>


------------------------------------------------------------------------------------------------------------

PASSTHRU Function

The main use of the PASSTHRU function is to issue complex SELECTs, not currently
supported by the broker, to databases like GROUP BY or HAVING clauses which are not supported by ESQL.If we need to use these filter conditions,we can go for Passthru Function.

Ex:- SET OutputRoot.XML.Data.SelectResult.Row[] =
PASSTHRU('SELECT R.* FROM Schema1.Table1 AS R WHERE R.Name = ? OR R.Name =
? ORDER BY Name' TO Database.DSN1 VALUES ('Name1', 'Name4'));

------------------------------------------------------------------------------------------------------------

 PASSTHRU Statement

The main use of the PASSTHRU statement is to issue administrative commands to
databases like Creating/Dropping a table which needs extra privileges.

Ex 1:- PASSTHRU 'CREATE TABLE customer (
                                    CustomerNumber INTEGER,
                                    FirstName VARCHAR(256),
                                    LastName VARCHAR(256),
                                   Street VARCHAR(256),
                                   City VARCHAR(256),
                                   Country VARCHAR(256)
                             )' TO Database.sample;

Ex 2:- PASSTHRU 'DROP TABLE passthru' TO Database.sample;
                     DECLARE myVar CHARACTER;
                     SET myVar = 'SELECT * FROM sample';
                     SET OutputRoot.XMLNSC.root.Data[] = PASSTHRU(myVar);