SQL +

Read(139) Label: sql+, convert, spl,

Description:

Based on SQL-92, SQL+ syntax adds a series of special-purpose identifiers to widen its data query skills.

A SQL+ query is like a familiar SQL statement. Yet while SQL statements handle database tables, SQL+ queries only manipulate bin files with btx extension, composite files with ctx extension and their memorized tables. They don’t support processing data from other sources.

There are two work modes for SQL+: Translate and Execute. The Translate mode translates SQL+ syntax into the scripting language SPL using the embedded tool on esProc main screen; the Execute mode runs a query to get the result set through JDBC or ODBC call.

 

General syntax:

first( F order by …)

Get the first of sorted records; can be used in GROUP BY as an aggregate function; get a record randomly if there isn’t ORDER BY.

last( F order by …)

Get the last of sorted records; can be used in GROUP BY as an aggregate function; get a record randomly if there isn’t ORDER BY.

median(F)

Calculate the median.

in

Conditional query.

exists

Check if a query returns data.

select

Can be used in a subquery.

where rownum=1

Get the first row.

where F=(select max(F) …)

Conditional query.

decode

Convert a query result to another data type.

/*+external*/ T

Identifier /*+external*/ indicates that file T can’t be wholly stored in the memory but it needs to be directly processed or via the cursor; without it T is a memory table.

/*+big*/ group by

Big data grouping which returns a cursor; doesn’t apply to a memory table.

group by

A common grouping operation without a special identifier.

/*+group*/ in

Perform grouping first when IN condition involves a join.

/*+group*/ exists

Perform grouping first when EXISTS condition involves a join.

/*+var(v)*/(…)

Define variable v.

(…)/*+var(v)*/

Ignore the content enclosed by the parentheses when using variable v; the variable can be defined once and then repeatedly used, or left unused.

[arg]

Compose an argument

 

Joins:

/*+primary*/ join

A join of homo-dimension tables or main table and sub table. Each table must be ordered by their associative field; otherwise the joining result set will be wrong. With a composite associative field, sort the table by the field order in the SQL statement. Use /*+ORDER*/ identifier to pre-sort data if necessary.

/*+foreign*/ join

A foreign-key-type join, where key values of the right table must be unique (The primary key constraint). The right table’s associative field must be ordered when it is an external table. With a composite associative field, sort the table by the field order in the SQL statement.

/*+foreign(fk)*/ join

A prejoin of two memory tables via foreigh key fk using A.join@k. A SQL+ statement references the joining result set directly. This prejoin can be automatically identified in OLAP mode.

/*+switch*/ join

A switch-type prejoin of two memory tables using A.switch. A SQL+ statement references the joining result set directly. This prejoin can be automatically identified in OLAP mode.

join  on

A common table join. There’s no requirement that data be ordered by associative fields when the right table in a full join connected by AND is a memory table. Both the left table and the right table must be ordered by the associative fields when the right table is an external table; otherwise the joining result set will be wrong. With a composite associative field, sort the table by the field order in the SQL statement. Use /*+ORDER*/ identifier to pre-sort data if necessary. There’s no restriction on order when tables are connected by OR or when it is a non-equi-join (You’d better not use these JOINs because they are very slow).

right join

A switch-over of positions of the two tables enables a left join.

 

External tables (BTX files & CTX files) and memory tables (table sequences):

1. Read a CTX file or a BTX file following FROM or JOIN into the memory when /*+EXTERNAL*/ identifier is absent. The identifier is useless when it works with a table sequence variable, which includes one generated from an intermediate calculation (such as GROUP BY) (as a table sequence result is preffered).

2. If there is /*+EXTERNAL*/ identifier in a subquery following FROM or JOIN, read a table file in the subquery into the memory. If there is such an identifier outside of the subquery, read the subquery’s cursor-type result set into the memory.

3. A GROUP BY operation returns a cursor if the to-be-grouped object is an external table and /*+BIG*/ identifier is present; a non-cursor result will be stored in the memory by default. The result set of a LIMIT operation that retrieves only part of the data is stored in the memory, too.

Without /*+ORDERED*/ identifier, tables in a merge operation (UNION, INTERSECT, MINUS) will all be imported in the memory. Any operation that minimizes the result set (such as whole-set aggregate and an operation where the filtering condition is equivalent to FALSE) will also be imported in the memory.

4. For a subquery in a SELECT statement or a WHERE clause and one following an EXISTS or IN condition: If the result is a constant, it can’t be stored in external memory. If the result (and the result of the main query) isn’t a constant, it can’t be stored in external memory when /*+PRIMARY*/ identifier is absent; but it can when the /*+PRIMARY*/ identifier is present.

5. A JOIN where one of the involved tables is an external table returns a cursor. One where all involved tables are memory tables returns a table sequence.

6. For a JOIN of composite tables (files with .ctx extension), it is the relationship of the primary key and the associated filed that determines whether we join them by the primary key or by the foreign key.

7. If the table in a FROM clause or tables in a JOIN are composite tables or bin files, you can mark /*+CTX*/ or /*+BTX*/ after the table with extension omitted; the path can also be omitted if the table is stored in the main directory.

8. If the table in a FROM clause or tables in a JOIN are composite tables or bin files, you can define name for a composite table or a bin file by adding meta.txt file under the main directory. Below is the structure of meta.txt:

Table: The field contains names defined for files under File field. You can use a Table name to access esProc JDBC/ODBC, such as select * from tt1.

File: The field contains data files for which names are defined. The files need to be represented by a full path relative to the main directory. Use different separators under different operating systems when referring to a subdirectory, such as ctx//table 1.ctx under Windows and ctx/table 1.txt under Linux.

Column field and Type field can be empty.

 

Parallel processing:

/*+parallel (n) */ T

This identifier enables a parallel retrieval of an external file (i.e. a multicursor), but does not apply to a table sequence. It applies to a bin file that is generated through @z.

You’d better not use the identifier with a JOIN.

If you really need to speed up a JOIN by using this identifier to retrieve an external file, first translate a SQL+ statement into an SPL query and then adjust SPL for your specific case. For example, joinx needs a synchronous segmentation if it uses a multicursor; and xjoinx forbids a parallel retrieval.

 

Subqueries that return non-constant results:

/*+group*/

By default, when the main query and the subquery are associated through a foreign key, values of the subquery’s associative field must be unique (Primary key constraint). But EXISTS condition and IN condition allow duplicates among associative field values (non-primary-key-constraint) theoretically, then you can use /*+GROUP*/ identifier to put primary key constraint on the subquery’s associative field values.

/*+var(v)*/

In a query where there are multiple same subqueries (whose names are v), this identifier enables the reuse of one subquery result in SPL to reduce the amount of computational work (The working range is within the main query). A subquery must be able to be wholly stored in the memory to be reusable. The actual order of executing subqueries could be different from the defined order but the result will be the same. The identifier applies to a subquery whose result is a constant.

ROWNUM = 1

About the keyword ROWNUM, SQL+ supports ROWNUM = 1 only. Make sure each record of a subquery makes same result (No distinct value in the result set) before using ROWNUM=1 in the subquery.

/*+PRIMARY*/

This identifier means that the main query and the subquery have the relationship of the main table and the sub table or two homo-dimension tables. Its SPL counterpart handles them with joinx(). It requires that both the main query and the subquery are ordered by associative fields when the subquery result can’t be wholly stored in the memory. It doesn’t require an order by the associative fields when the subquery can be completely stored in the memory. By default the main query and the subquery are associated through the foreign key and the SPL counterpart handles them with T.find(), with the requirement that the subquery should all be imported into the memory and that its associative field values must be unique (Primary key constraint).

 

Ordered data sets:

/*+ordered*/

This identifier enables handling data by order (positions) to considerably speed up computation. Do not confuse it with the sorting identifier /*+ORDER*/.

/*+ordered*/ group

Order-based grouping. It requires that to-be-handled data should be ordered by grouping expression (GROUP BY). When there are multiple grouping expressions, sort data by their order.

/*+ordered*/ distinct

Order-baed distinct. It requires that to-be-handled data should be ordered by query expression (SELECT expression). When there are multiple grouping expressions, sort data by their order.

count(/*+ordered*/ distinct)

Order-based distinct count. It requires that to-be-handled data should be ordered by the expression over which COUNT is performed.

/*+ordered*/  union

Order-based union. It requires that each of the same-structure tables to be handled should be ordered by the original field order. Can handle external tables.

/*+ordered*/  intersect

Order-based intersect. It requires that each of the same-structure tables to be handled should be ordered by the original field order. Can handle external tables.

/*+ordered*/  minus

Order-based minus. It requires that each of the same-structure tables to be handled should be ordered by the original field order. Can handle external tables.

 

Optimization:

where /*+cursor*/ (…) and …

Add the condition in parentheses when creating the cursor. The syntax only applies to an external table with ctx extension. It forces an advance execution of the conditional filtering defined in WHERE (conditions must be connected by AND; OR isn’t allowed). One /*+CURSOR*/ covers one filtering condition (a filtering condition works within an AND operator or until the end of a clause). Multiple conditions for one cursor need to be enclosed by parentheses.

where /*+index*/

Index-based filtering. The syntax only applies to an external table with ctx extension. Make sure an eligible index file has been there for the ctx file.

group by …,/*+redundant*/…

The syntax indicates that the grouping expression after /*+REDUNDANT*/ is redundant (which means you can get same result only with grouping field before it). In the actual execution, the redundant grouping expression is useless and the program gets the field value of a random record in the current group to list).

C/*+bit(k)*/(p1,…,pn)

Used in WHERE clause to judge whether the pith bit of a column value prefixed by C is made up all by 1. Each column value occupies k bits (default k is 32) and must be an integer.

d in /*+dims(D) */ (d1,…,dn)

Used in WHERE clause to judge whether sequence (d1,…,dn) contains any member of in-memory sequence D. The d field contains non-duplicated integers whose count is less than the number of members in sequence D.

 

Note:

1. Sometimes ORACLE treats string data and numerical data equally and allows an automatical switch-over between them (Such as string0and number 0). But they are strictly different in SQL+. Be careful with the SQL+ syntax when performing data check.

2. In ORACLE, an empty string represented by ‘’ is equivalent to NULL, but they are not in SQL+. Be careful with the SQL+ syntax when performing data check.

3. SQL+ supports translation of non-table data. Translated field names will be the ones that appear for the first time in the original data. Make sure the cases are correct and consistent.

4. SPL is case-sensitive. SQL+ Translate mode equalizes uppercase and lowercase in field names and table names, but it can’t equally treat string constants in uppercase and lowercase (except for LIKE). Make sure the case in string constants are correct and consistent.

Example:

  Note: The following are examples of a SQL+ statement and its corresponding SPL script

 

select GENDER,first(NAME order by SALARY) from emp.btx group by GENDER

 

 

A

1

=file("D:/emp.btx").cursor@b(GENDER,SALARY,NAME).fetch()

2

=A1.groups(GENDER:GENDER;top@1(1,SALARY,NAME):_2)

 

select last(NAME order by SALARY) from emp.ctx

 

 

A

1

=file("D:/emp.ctx").create().cursor(SALARY,NAME).fetch()

2

=A1.groups(;top@1(-1,SALARY,NAME):_1)

 

  select median(SALARY) from emp.btx where EID<10

 

 

A

1

=file("D:/emp.btx").cursor@b(SALARY,EID).fetch()

2

=A1.select(EID<10&&EID!=null)

3

=A2.groups(;median(,SALARY):_1)

 

select median(SALARY) from emp.ctx where EID<10

 

 

A

1

=file("D:/emp.ctx").create().cursor(SALARY,EID;EID<10&&EID!=null).fetch()

2

=A1.groups(;median(,SALARY):_1)

 

select e.NAME, e.GENDER, d.DEPT, d.MANAGER from emp.btx e join Dep.btx d on d.DEPT = e.DEPT and d.MANAGER =4

 

 

A

1

=file("D:/emp.btx").cursor@b(DEPT,NAME,GENDER).fetch()

2

=file("D:/Dep.btx").cursor@b(DEPT,MANAGER).fetch()

3

=join(A1:L,DEPT,4;A2:R,DEPT,MANAGER)

4

>$2=A3.new(L.DEPT:e_DEPT,L.NAME:e_NAME,L.GENDER:e_GENDER,R.DEPT:d_DEPT,R.MANAGER:d_MANAGER)

5

=$2.new(e_NAME:e_NAME,e_GENDER:e_GENDER,d_DEPT:d_DEPT,d_MANAGER:d_MANAGER)

 

select * from emp.btx emp where emp.DEPT in (select DEPT from Dep.btx dep where dep.manager = 5 and dep.DEPT = emp.DEPT)

 

 

A

1

=file("D:/emp.btx").cursor@b().fetch()

2

=file("D:/Dep.btx").cursor@b(DEPT,MANAGER).fetch()

3

=A2.select(MANAGER==5&&MANAGER!=null)

4

>$9=A3.new(DEPT:$7,DEPT:DEPT)

5

>$9=if(ift($9),$9,create($7,DEPT).insert@r(0:$9))

6

>$9=$9.keys(DEPT).index()

7

>$8=A1.derive([].insert(0,$9.find(~.DEPT).(#1)):$7)

8

=$8.select($7.contain(DEPT)&&DEPT!=null)

9

=A8.new(EID,NAME,SURNAME,GENDER,STATE,BIRTHDAY,HIREDATE,DEPT,SALARY)

 

  select T1.CustomerName, T2.CityName from Customer.ctx T1 join City.ctx T2 on T1.CityID=T2.CityID where exists (select 1 from VIPCustomer.ctx where ID = T1.ID)

 

 

A

 

1

=file("D:/Customer.ctx").create().cursor(ID,CustomerName,CityID).fetch()

 

2

>$9=file("D:/VIPCustomer.ctx").create().cursor(ID).fetch()

 

3

func

 

4

 

=$9

5

 

=B4.select(ID==A3.(#1)&&ID!=null)

6

 

=B5.new(1:_1)

7

 

return B6.(#1).ifn()

8

>$7=A1.derive(func(A3,~):$6)

 

9

=$7.select($6!=null)

 

10

=A9.new(CustomerName:CustomerName,ID:ID,CityID:CityID)

 

11

=file("D:/City.ctx").create().cursor(CityID,CityName).fetch()

 

12

=join(A10:L,CityID;A11:R,CityID)

 

13

>$10=A12.new(L.CustomerName:T1_CustomerName,L.ID:T1_ID,L.CityID:T1_CityID,R.CityID:T2_CityID,R.CityName:T2_CityName)

 

14

=$10.new(T1_CustomerName:T1_CustomerName,T2_CityName:T2_CityName)

 

 

  select d.DEPT, (select count(EID) from emp.btx e where e.DEPT= d.DEPT group by DEPT) con from Dep.btx d

 

 

A

1

=file("D:/Dep.btx").cursor@b(DEPT).fetch()

2

=file("D:/emp.btx").cursor@b(EID,DEPT).fetch()

3

=A2.groups(DEPT:group_1;count(EID):count_1)

4

>$13=A3.new(count_1:$11,group_1:DEPT)

5

>$13=if(ift($13),$13,create($11,DEPT).insert@r(0:$13))

6

>$13=$13.keys(DEPT).index()

7

>$12=A1.derive($13.find(~.DEPT).(#1):$11)

8

=$12.new(DEPT:DEPT,$11:con)

 

select DEPT , MANAGER from Dep.btx where rownum =1

 

 

A

1

=file("D:/Dep.btx").cursor@b(DEPT,MANAGER).fetch()

2

=A1.to(1)

 

select * from emp.btx where salary = (select max(salary) from emp.btx)

 

 

A

1

=file("D:/emp.btx").cursor@b().fetch()

2

=A1.groups(;top(-1;SALARY):top_1).#1

 

select NAME, decode(GENDER,'2','F','1','M') from st.btx

 

 

A

1

=file("D:/st.btx").cursor@b(NAME,GENDER).fetch()

2

=A1.new(NAME:NAME,case(GENDER,"2":"F","1":"M"):_2)

 

select DEPT,sum(SALARY) from emp.btx group by DEPT

 

 

A

1

=file("D:/emp.btx").cursor@b(DEPT,SALARY).fetch()

2

=A1.groups(DEPT:DEPT;sum(SALARY):_2)

 

select * from /*+parallel (4) */ Persons_z.btx

 

A

1

=file("D:/Persons_z.btx").cursor@b().fetch()

 

select * from /*+external*/ emp.btx

 

 

A

1

=file("D:/emp.btx").cursor@b()

 

select o.OrderNo ,p.City from Orders.btx o /*+primary*/ join Persons.btx p on p.Id_P = o.Id_P

 

 

A

1

=file("D:/Orders.btx").cursor@b(OrderNo,Id_P).fetch()

2

=file("D:/Persons.btx").cursor@b(Id_P,City).fetch()

3

=join@m(A1:L,Id_P;A2:R,Id_P)

4

>$15=A3.new(L.OrderNo:o_OrderNo,L.Id_P:o_Id_P,R.Id_P:p_Id_P,R.City:p_City)

5

=$15.new(o_OrderNo:o_OrderNo,p_City:p_City)

 

select o.OrderNo ,p.City from Orders.btx o /*+foreign*/ join Persons.btx p on p.Id_P = o.Id_P

 

 

A

1

=file("D:/Orders.btx").cursor@b(OrderNo,Id_P).fetch()

2

=file("D:/Persons.btx").cursor@b(Id_P,City).fetch()

3

=if(ift(A2),A2,create(Id_P,City).insert@r(0:A2))

4

=A3.keys(Id_P).index()

5

>$16=A1.new(OrderNo:o_OrderNo,Id_P:o_Id_P).join@i(o_Id_P,A4:Id_P,Id_P:p_Id_P,City:p_City)

6

=$16.new(o_OrderNo:o_OrderNo,p_City:p_City)

 

select o.OrderNo ,p.City from o /*+foreign(fk)*/ join p on p.Id_P = o.Id_P

 

 

A

B

 

1

=file("Orders.btx").cursor@b().fetch()

=file("Persons.btx").cursor@b().fetch()

Expressions in A1, B1, A2 are edited manually

2

>o=A1.join(Id_P,B1:Id_P,~:fk)

 

3

>$2=o.new(OrderNo:o_OrderNo,Id_P:o_Id_P,fk.Id_P:p_Id_P,fk.City:p_City)

 

Queries in A3 and A4 are converted from SQL+ statements

4

=$2.new(o_OrderNo:o_OrderNo,p_City:p_City)

 

 

select o.OrderNo ,p.City from o /*+switch*/ join p on p.Id_P = o.Id_P

 

 

A

B

 

1

=file("Orders.ctx").create().cursor().fetch()

=file("Persons.ctx").create().cursor().fetch()

Expressions in A1, B1, A2 are edited manually

2

>o=A1.switch(Id_P,B1:Id_P)

 

3

>$3=o.new(OrderNo:o_OrderNo,Id_P.Id_P:o_Id_P,Id_P.Id_P:p_Id_P,Id_P.City:p_City)

 

Queries in A3 and A4 are converted from SQL+ statements

4

=$3.new(o_OrderNo:o_OrderNo,p_City:p_City)

 

 

select * from /*+external*/ emp.ctx where /*+cursor*/ (EID<10)

 

 

A

1

=file("D:/emp.ctx").create().cursor(;(EID<10)&&EID!=null)

 

select * from D:/emp_index.ctx where /*+index*/ DEPT='HR'

 

 

A

 

1

=file("D:/emp_index.ctx").create().icursor(;DEPT=="HR").fetch()

emp_index.ctx is a composite table file having an index over DEPT field

 

select GENDER,DEPT,NAME,EID from emp.btx group by GENDER,/*+redundant*/ DEPT

 

 

A

1

=file("D:/emp.btx").cursor@b(GENDER,DEPT,NAME,EID).fetch()

2

=A1.groups(GENDER:GENDER;DEPT:DEPT,NAME:NAME,EID:EID)

 

select GENDER ,avg(SALARY) from /*+external*/ emp.btx /*+big*/ group by GENDER

 

 

A

1

=file("D:/emp.btx").cursor@b(GENDER,SALARY)

2

=A1.groupx(GENDER:GENDER;avg(SALARY):_2)

 

  select /*+ordered*/ distinct DEPT from emp.btx

 

 

A

1

=file("D:/emp.btx").cursor@b(DEPT).fetch()

2

=A1.groups@o(DEPT;)

 

select count(/*+ordered*/ distinct state) from emp.btx

 

 

A

1

=file("D:/emp.btx").cursor@b(STATE).fetch()

2

=A1.groups(;iterate(ranki(STATE)):_1)

 

  select * from emp.btx emp where emp.DEPT /*+group*/ in (select DEPT from Dep.btx dep where dep.manager = 5 and dep.DEPT = emp.DEPT)

 

 

A

1

=file("D:/emp.btx").cursor@b().fetch()

2

=file("D:/Dep.btx").cursor@b(DEPT,MANAGER).fetch()

3

>$22=A3.new(DEPT:$20,DEPT:DEPT)

4

>$22=if(ift($22),$22,create($20,DEPT).insert@r(0:$22))

5

>$22=$22.group(DEPT:DEPT;~.(#1):$20).keys(DEPT).index()

6

>$21=A1.derive($22.find(~.DEPT).(#2):$20)

7

=$21.select($20.contain(DEPT)&&DEPT!=null)

8

=A8.new(EID,NAME,SURNAME,GENDER,STATE,BIRTHDAY,HIREDATE,DEPT,SALARY)

9

>$22=A3.new(DEPT:$20,DEPT:DEPT)

 

  select T1.CustomerName, T2.CityName from Customer.ctx T1 join City.ctx T2 on T1.CityID=T2.CityID where /*+group*/ exists (select 1 from VIPCustomer.ctx where ID = T1.ID)

 

 

A

B

1

=file("D:/Customer.ctx").create().cursor(ID,CustomerName,CityID).fetch()

 

2

=file("D:/VIPCustomer.ctx").create().cursor(ID).fetch()

 

3

func

 

4

 

=$26

5

 

=B4.select(ID==A3.(#1)&&ID!=null)

6

 

=B5.new(1:_1)

7

 

return B6.(#1).ifn()

8

>$24=A1.derive(func(A3,~):$23)

 

9

=$24.select($23!=null)

 

10

=A9.new(CustomerName:CustomerName,ID:ID,CityID:CityID)

 

11

=file("D:/City.ctx").create().cursor(CityID,CityName).fetch()

 

12

=join(A10:L,CityID;A11:R,CityID)

 

13

>$27=A12.new(L.CustomerName:T1_CustomerName,L.ID:T1_ID,L.CityID:T1_CityID,R.CityID:T2_CityID,R.CityName:T2_CityName)

 

14

=$27.new(T1_CustomerName:T1_CustomerName,T2_CityName:T2_CityName)

 

 

select d.DEPT, /*+var(v)*/ (select count(EID) from emp.btx e where e.DEPT= d.DEPT group by DEPT) con from Dep.btx d

 

 

A

1

=file("D:/Dep.btx").cursor@b(DEPT).fetch()

2

=file("D:/emp.btx").cursor@b(EID,DEPT).fetch()

3

=A2.groups(DEPT:group_1;count(EID):count_1)

4

>v=A3.new(count_1:$28,group_1:DEPT)

5

>v=if(ift(v),v,create($28,DEPT).insert@r(0:v))

6

>v=v.keys(DEPT).index()

7

>$29=A1.derive(v.find(~.DEPT).(#1):$28)

8

=$29.new(DEPT:DEPT,$28:con)

 

select T1.ID CustomerName, /*+var(v)*/ (select VIPLevel from VIPCustomer.ctx where ID=T1.ID) Level from Customer.ctx T1 where exists (select VIPLevel from VIPCustomer.ctx where ID=T1.ID) /*+var(v)*/

 

 

A

B

1

=file("D:/Customer.ctx").create().cursor(ID).fetch()

 

2

=file("D:/VIPCustomer.ctx").create().cursor(VIPLevel,ID).fetch()

 

3

func

 

4

 

=v

5

 

=B4.select(ID==A3.(#1)&&ID!=null)

6

 

=B5.new(VIPLevel:VIPLevel)

7

 

return B6.(#1).ifn()

8

>$32=A1.derive(func(A3,~):$30)

 

9

>$34=$32.derive(func(A3,~):$31)

 

10

=$34.select($30!=null)

 

11

=A10.new(ID:CustomerName,$31:Level)

 

 

  SELECT * FROM /*+external*/ Emp_China.btx /*+ordered*/ union SELECT * FROM /*+external*/ Emp_USA.btx

 

 

A

1

=file("D:/Emp_China.btx").cursor@b()

2

=file("D:/Emp_USA.btx").cursor@b()

3

=[A1,A2].mergex@u(E_ID,E_Name)

 

  SELECT * FROM /*+external*/ Emp_China.btx /*+ordered*/ intersect SELECT * FROM /*+external*/ Emp_USA.btx

 

 

A

1

=file("D:/Emp_China.btx").cursor@b()

2

=file("D:/Emp_USA.btx").cursor@b()

3

=[A1,A2].mergex@i(E_ID,E_Name)

 

  SELECT * FROM /*+external*/ Emp_China.ctx /*+ordered*/ minus SELECT * FROM /*+external*/ Emp_USA.ctx

 

   

A

1

=file("D:/Emp_China.ctx").create().cursor()

2

=file("D:/Emp_USA.ctx").create().cursor()

3

=[A1,A2].mergex@d(E_ID,E_Name)

 

SELECT NAME FROM emp.ctx where SALARY = [arg1]

 

    

A

1

=file("D:/emp.ctx").create().cursor(NAME,SALARY;SALARY==arg1&&SALARY!=null).fetch()

2

=A1.new(NAME:NAME)

 

 

select  City  from Persons/*+BTX*/

 

   

A

1

=file("C:/Program Files/esProc/demo/Persons.btx").cursor@b(City).fetch()

 

select o.OrderNo ,p.City from Orders/*+CTX*/ o  join Persons/*+CTX*/ p on p.Id_P = o.Id_P

 

 

A

1

=file("C:/Program Files/esProc/demo/Orders.ctx").create().cursor(OrderNo,Id_P).fetch()

2

=file("C:/Program Files/esProc/demo/Persons.ctx").create().cursor(Id_P,City).fetch()

3

=join(A1:L,Id_P;A2:R,Id_P)

4

>$13=A3.new(L.OrderNo:Orders_OrderNo,L.Id_P:Orders_Id_P,R.Id_P:Persons_Id_P,R.City:Persons_City)

5

=$13.new(Orders_OrderNo:Orders_OrderNo,Persons_City:Persons_City)

 

In the above program, C:/Program Files/esProc/demo is the main directory.

 

select CUSTOMERID,ID1,ID2,ID3 from odm /*+ctx*/ where ID /*+bit*/ (1,40,77)

 

 

A

1

=file("D:/esProc/demo/odm.ctx").create().cursor(CUSTOMERID,ID1,ID2,ID3;and(ID1,1)==1&&and(ID2,128)==128&&and(ID3,4096)==4096).fetch()

 

select * from odm /*+ctx*/ where ID3 in /*+dims(D)*/ ('A','B')

Set global variable D’s value is ['A','B','C'] and translate the SQL+ statement into SPL as follows:

 

A

1

=file("D:/esProc/demo/odm.ctx").create().cursor(;if(ifv($_15),$_15,env@j($_15,D.(["A","B"].contain@b(~))))(ID3)&&ID3!=null).fetch()