SQL Queries Resulted from SCQL-SQL Mapping Process

Each SCQL query, named or unnamed, is mapped to a SQL query within a SCM data server for data retrieving purposes. The SCQL-SQL mapping demo tool can be used to see the SQL queries resulted from SCQL queries defined in SCM knowledge base. For each SCQL query of the application example the tool outputs SCQL query name, formal notation generated from its structure definition, natural and formal descriptions defined by users explicitly in the knowledge base, and a resulted SQL query.

The used SCQL notation is based on the notation presented at [Ov04-3, Ov04-4] with adding some features: calculated role concepts, grouping, etc. (see SCM knowledge base definition for details about SCQL query definition). Role changing is reduced to a transformation and transformation is denoted with the construction “SELECT … FROM” instead of “.(…)” as compared with [Ov04-3, Ov04-4]. Each subquery of a resulted SQL query has a signature corresponding to a signature of an appropriate SCQL subquery taking into consideration that complex concepts are coded with several SQL fields. Within a SQL query, each subquery field acquires a unique name since there can be several table fields with the same name. More convenient way of SQL query representation for people is the subject of future work. Some queries below can be implemented in more effective ways. The current ways are selected to illustrate as many SCQL features as possible.

The output of SCQL-SQL mapping process

Query: Production Queries.Material Unit.Material units thicker 0,5

Natural texts:

Material units having thickness more than 0.5

Formal texts:

(Material Unit-(Thickness>0.5))

SCQL query statement:

(

(General.Physical Object.Geometry.Thickness, Production.Material Unit.Material Unit),

(General.Physical Object.Geometry.Thickness>0.5)

)

SQL query statement:

SELECT t1."GEOMETRY.THICKNESS" "GEOMETRY.THICKNESS0", t1.MATUNIT_ID MATUNIT_ID1

FROM

(

SELECT MATUNIT_ID, GEOMETRY.THICKNESS "GEOMETRY.THICKNESS" FROM MES.MATUNITS

) t1

WHERE t1."GEOMETRY.THICKNESS">0.5

Query: Production Queries.Material Unit.Slabs thicker/equial to 100 ordered by thickness

Natural texts:

Slabs having thickness more or equal to 100

Formal texts:

(Slab-Material Unit-(Thickness>100 OR Thickness=100)) ORDER BY Thickness

SCQL query statement:

(

(Production.Material Unit.Material Unit, Production.Indiscrete.Metallurgic.Slab),

(General.Physical Object.Geometry.Thickness, Production.Material Unit.Material Unit),

(General.Physical Object.Geometry.Thickness>100 OR General.Physical Object.Geometry.Thickness=100)

)

ORDER BY General.Physical Object.Geometry.Thickness ASC

SQL query statement:

SELECT t1."GEOMETRY.THICKNESS" "GEOMETRY.THICKNESS0", t1.MATUNIT_ID MATUNIT_ID1, t1.MATUNIT_ID MATUNIT_ID2

FROM

(

SELECT MATUNIT_ID, GEOMETRY.THICKNESS "GEOMETRY.THICKNESS" FROM MES.MATUNITS

) t1,

(

SELECT SLAB_ID FROM MES.SLABS

) t2

WHERE t2.SLAB_ID=t1.MATUNIT_ID

AND (t1."GEOMETRY.THICKNESS">100 OR t1."GEOMETRY.THICKNESS"=100)

ORDER BY t1."GEOMETRY.THICKNESS"

Query: Production Queries.Material Unit.Outcoming material unit mass

Natural texts:

A material unit outcoming mass is a sum of its outcoming transitions' masses

Formal texts:

(Material Unit-Consumed Material Unit-Mass Transition-Mass).(Material Unit, SUM(Mass) ~Mass)

SCQL query statement:

SELECT (SUM(General.Physical Object.Gravity.Mass) -> General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit) FROM

(

(Production.Material Unit.Material Unit, Production.Indiscrete.Consumed Material Unit),

(Production.Indiscrete.Consumed Material Unit, Production.Indiscrete.Mass Transition),

(General.Physical Object.Gravity.Mass, Production.Indiscrete.Mass Transition)

)

SQL query statement:

SELECT (SUM(t1.MASS0)) MASS00, t1.CONSUMED_MATUNIT_ID1 CONSUMED_MATUNIT_ID11

FROM

(

SELECT t1.MASS MASS0, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID1, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID2, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID3, t1.PRODUCED_MATUNIT_ID PRODUCED_MATUNIT_ID4

FROM

(

SELECT CONSUMED_MATUNIT_ID, PRODUCED_MATUNIT_ID, MASS FROM MES.MASSTRANSES

) t1

) t1

GROUP BY t1.CONSUMED_MATUNIT_ID1

Query: Production Queries.Material Unit.The rest material unit mass

Natural texts:

The rest mass of a material unit is:

a) mass of the material unit - if it is in a store;

b) mass of the material unit minus masses of all outcoming transitions -

if it is in a production unit

c) 0 - if it is consumed;

Formal texts:

((Material Unit State=1)-Material Unit-Mass).(Material Unit, Mass)

union ((((Material Unit State=2)-Material Unit), query[Outcoming material unit mass]).(Material Unit, Mass))

union ((Material Unit State=3)-Material Unit).(Material Unit, 0 ~Mass)

SCQL query statement:

(

(

SELECT (General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit) FROM

(

(Production.Material Unit.Material Unit State=1),

(Production.Material Unit.Material Unit, Production.Material Unit.Material Unit State),

(General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit)

)

UNION

SELECT (General.Physical Object.Gravity.Mass(Initial)-General.Physical Object.Gravity.Mass -> General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit) FROM

(

(Production.Material Unit.Material Unit State=2),

(Production.Material Unit.Material Unit, Production.Material Unit.Material Unit State),

(General.Physical Object.Gravity.Mass(Initial), Production.Material Unit.Material Unit),

SELECT (SUM(General.Physical Object.Gravity.Mass) -> General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit) FROM

(

(Production.Material Unit.Material Unit, Production.Indiscrete.Consumed Material Unit),

(Production.Indiscrete.Consumed Material Unit, Production.Indiscrete.Mass Transition),

(General.Physical Object.Gravity.Mass, Production.Indiscrete.Mass Transition)

)

)

)

UNION

SELECT (0 -> General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit) FROM

(

(Production.Material Unit.Material Unit State=3),

(Production.Material Unit.Material Unit, Production.Material Unit.Material Unit State)

)

)

SQL query statement:

SELECT t1.MASS0 MASS00, t1.MATUNIT_ID1 MATUNIT_ID11

FROM

(

SELECT t1.MASS MASS0, t1.MATUNIT_ID MATUNIT_ID1, t1.STATE STATE2

FROM

(

SELECT MATUNIT_ID, STATE, MASS FROM MES.MATUNITS

) t1

WHERE t1.STATE=1

) t1

UNION

SELECT (t1.MASS1-t1.MASS000) f0, t1.CONSUMED_MATUNIT_ID112 CONSUMED_MATUNIT_ID1121

FROM

(

SELECT t1.MASS00 MASS000, t2.MASS MASS1, t1.CONSUMED_MATUNIT_ID11 CONSUMED_MATUNIT_ID112, t2.STATE STATE3

FROM

(

SELECT (SUM(t1.MASS0)) MASS00, t1.CONSUMED_MATUNIT_ID1 CONSUMED_MATUNIT_ID11

FROM

(

SELECT t1.MASS MASS0, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID1, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID2, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID3, t1.PRODUCED_MATUNIT_ID PRODUCED_MATUNIT_ID4

FROM

(

SELECT CONSUMED_MATUNIT_ID, PRODUCED_MATUNIT_ID, MASS FROM MES.MASSTRANSES

) t1

) t1

GROUP BY t1.CONSUMED_MATUNIT_ID1

) t1,

(

SELECT MATUNIT_ID, STATE, MASS FROM MES.MATUNITS

) t2

WHERE t2.MATUNIT_ID=t1.CONSUMED_MATUNIT_ID11

AND (t2.STATE=2)

) t1

UNION

SELECT (0) f0, t1.MATUNIT_ID0 MATUNIT_ID01

FROM

(

SELECT t1.MATUNIT_ID MATUNIT_ID0, t1.STATE STATE1

FROM

(

SELECT MATUNIT_ID, STATE FROM MES.MATUNITS

) t1

WHERE t1.STATE=3

) t1

Query: Production Queries.Material Unit.Not slabs

Natural texts:

All material units not being slabs

Formal texts:

(Material Unit-Thickness).(Material Unit) minus (Material Unit-Slab).(Material Unit)

SCQL query statement:

(

SELECT (Production.Material Unit.Material Unit) FROM

(General.Physical Object.Geometry.Thickness, Production.Material Unit.Material Unit)

MINUS

SELECT (Production.Material Unit.Material Unit) FROM

(Production.Material Unit.Material Unit, Production.Indiscrete.Metallurgic.Slab)

)

SQL query statement:

SELECT t1.MATUNIT_ID MATUNIT_ID0

FROM

(

SELECT MATUNIT_ID, GEOMETRY.THICKNESS "GEOMETRY.THICKNESS" FROM MES.MATUNITS

) t1

MINUS

SELECT t1.SLAB_ID SLAB_ID0

FROM

(

SELECT SLAB_ID FROM MES.SLABS

) t1

Query: Production Queries.Material Unit.All material units with perhaps commercial orders

Natural texts:

All material units extended with commercial orders when they are assigned to

Formal texts:

(Material Unit-Thickness), (Material Unit-Commercial Order)+

SCQL query statement:

(

(General.Physical Object.Geometry.Thickness, Production.Material Unit.Material Unit),

(Production.Commercial Order.Commercial Order, Production.Material Unit.Material Unit)+

)

SQL query statement:

SELECT t1."GEOMETRY.THICKNESS" "GEOMETRY.THICKNESS0", t2.COMORDER_ID COMORDER_ID1, t1.MATUNIT_ID MATUNIT_ID2

FROM

(

SELECT MATUNIT_ID, GEOMETRY.THICKNESS "GEOMETRY.THICKNESS" FROM MES.MATUNITS

) t1,

(

SELECT MATUNIT_ID, COMORDER_ID FROM MES.MATUNITS

) t2

WHERE t1.MATUNIT_ID=t2.MATUNIT_ID(+)

Query: Production Queries.Commercial Order.Commercial order mass in production

Natural texts:

Sum of the rest material unit masses of all material units assigned to the commercial order

Formal texts:

((Commercial Order-Material Unit), query[The rest material unit mass]).(Commercial Order, SUM(Mass) ~Mass)

SCQL query statement:

SELECT (SUM(General.Physical Object.Gravity.Mass) -> General.Physical Object.Gravity.Mass, Production.Commercial Order.Commercial Order) FROM

(

(Production.Commercial Order.Commercial Order, Production.Material Unit.Material Unit),

(

(

SELECT (General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit) FROM

(

(Production.Material Unit.Material Unit State=1),

(Production.Material Unit.Material Unit, Production.Material Unit.Material Unit State),

(General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit)

)

UNION

SELECT (General.Physical Object.Gravity.Mass(Initial)-General.Physical Object.Gravity.Mass -> General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit) FROM

(

(Production.Material Unit.Material Unit State=2),

(Production.Material Unit.Material Unit, Production.Material Unit.Material Unit State),

(General.Physical Object.Gravity.Mass(Initial), Production.Material Unit.Material Unit),

SELECT (SUM(General.Physical Object.Gravity.Mass) -> General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit) FROM

(

(Production.Material Unit.Material Unit, Production.Indiscrete.Consumed Material Unit),

(Production.Indiscrete.Consumed Material Unit, Production.Indiscrete.Mass Transition),

(General.Physical Object.Gravity.Mass, Production.Indiscrete.Mass Transition)

)

)

)

UNION

SELECT (0 -> General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit) FROM

(

(Production.Material Unit.Material Unit State=3),

(Production.Material Unit.Material Unit, Production.Material Unit.Material Unit State)

)

)

)

SQL query statement:

SELECT (SUM(t1.MASS00000)) MASS000000, t1.COMORDER_ID1 COMORDER_ID11

FROM

(

SELECT t1.MASS0000 MASS00000, t2.COMORDER_ID COMORDER_ID1, t1.MATUNIT_ID1111 MATUNIT_ID11112

FROM

(

SELECT t1.MASS0 MASS00, t1.MATUNIT_ID1 MATUNIT_ID11

FROM

(

SELECT t1.MASS MASS0, t1.MATUNIT_ID MATUNIT_ID1, t1.STATE STATE2

FROM

(

SELECT MATUNIT_ID, STATE, MASS FROM MES.MATUNITS

) t1

WHERE t1.STATE=1

) t1

UNION

SELECT (t1.MASS1-t1.MASS000) f0, t1.CONSUMED_MATUNIT_ID112 CONSUMED_MATUNIT_ID1121

FROM

(

SELECT t1.MASS00 MASS000, t2.MASS MASS1, t1.CONSUMED_MATUNIT_ID11 CONSUMED_MATUNIT_ID112, t2.STATE STATE3

FROM

(

SELECT (SUM(t1.MASS0)) MASS00, t1.CONSUMED_MATUNIT_ID1 CONSUMED_MATUNIT_ID11

FROM

(

SELECT t1.MASS MASS0, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID1, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID2, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID3, t1.PRODUCED_MATUNIT_ID PRODUCED_MATUNIT_ID4

FROM

(

SELECT CONSUMED_MATUNIT_ID, PRODUCED_MATUNIT_ID, MASS FROM MES.MASSTRANSES

) t1

) t1

GROUP BY t1.CONSUMED_MATUNIT_ID1

) t1,

(

SELECT MATUNIT_ID, STATE, MASS FROM MES.MATUNITS

) t2

WHERE t2.MATUNIT_ID=t1.CONSUMED_MATUNIT_ID11

AND (t2.STATE=2)

) t1

UNION

SELECT (0) f0, t1.MATUNIT_ID0 MATUNIT_ID01

FROM

(

SELECT t1.MATUNIT_ID MATUNIT_ID0, t1.STATE STATE1

FROM

(

SELECT MATUNIT_ID, STATE FROM MES.MATUNITS

) t1

WHERE t1.STATE=3

) t1

) t1,

(

SELECT MATUNIT_ID, COMORDER_ID FROM MES.MATUNITS

) t2

WHERE t2.MATUNIT_ID=t1.MATUNIT_ID1111

) t1

GROUP BY t1.COMORDER_ID1

Query: Production Queries.Commercial Order.The rest of commercial order mass

Natural texts:

The rest of commercial order mass is its optimal mass minus mass in production

Formal texts:

((Commercial Order-Optimal Mass-Mass(Optimal)), query[Commercial order mass in production].(Commercial Order, Mass ~Mass(In production))).(Commercial Order, Mass(Optimal)-Mass(In production) ~Mass)

SCQL query statement:

SELECT (General.Physical Object.Gravity.Mass(Optimal)-General.Physical Object.Gravity.Mass(In production) -> General.Physical Object.Gravity.Mass, Production.Commercial Order.Commercial Order) FROM

(

(Production.Commercial Order.Commercial Order, Production.Commercial Order.Optimal Mass),

(General.Physical Object.Gravity.Mass(Optimal), Production.Commercial Order.Optimal Mass),

SELECT (General.Physical Object.Gravity.Mass -> General.Physical Object.Gravity.Mass(In production), Production.Commercial Order.Commercial Order) FROM

SELECT (SUM(General.Physical Object.Gravity.Mass) -> General.Physical Object.Gravity.Mass, Production.Commercial Order.Commercial Order) FROM

(

(Production.Commercial Order.Commercial Order, Production.Material Unit.Material Unit),

(

(

SELECT (General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit) FROM

(

(Production.Material Unit.Material Unit State=1),

(Production.Material Unit.Material Unit, Production.Material Unit.Material Unit State),

(General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit)

)

UNION

SELECT (General.Physical Object.Gravity.Mass(Initial)-General.Physical Object.Gravity.Mass -> General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit) FROM

(

(Production.Material Unit.Material Unit State=2),

(Production.Material Unit.Material Unit, Production.Material Unit.Material Unit State),

(General.Physical Object.Gravity.Mass(Initial), Production.Material Unit.Material Unit),

SELECT (SUM(General.Physical Object.Gravity.Mass) -> General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit) FROM

(

(Production.Material Unit.Material Unit, Production.Indiscrete.Consumed Material Unit),

(Production.Indiscrete.Consumed Material Unit, Production.Indiscrete.Mass Transition),

(General.Physical Object.Gravity.Mass, Production.Indiscrete.Mass Transition)

)

)

)

UNION

SELECT (0 -> General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit) FROM

(

(Production.Material Unit.Material Unit State=3),

(Production.Material Unit.Material Unit, Production.Material Unit.Material Unit State)

)

)

)

)

SQL query statement:

SELECT (t1.OPT_MASS1-t1.MASS00000000) f0, t1.COMORDER_ID1112 COMORDER_ID11121

FROM

(

SELECT t1.MASS0000000 MASS00000000, t2.OPT_MASS OPT_MASS1, t1.COMORDER_ID111 COMORDER_ID1112, t2.OPT_MASS OPT_MASS3

FROM

(

SELECT (t1.MASS000000) MASS0000000, t1.COMORDER_ID11 COMORDER_ID111

FROM

(

SELECT (SUM(t1.MASS00000)) MASS000000, t1.COMORDER_ID1 COMORDER_ID11

FROM

(

SELECT t1.MASS0000 MASS00000, t2.COMORDER_ID COMORDER_ID1, t1.MATUNIT_ID1111 MATUNIT_ID11112

FROM

(

SELECT t1.MASS0 MASS00, t1.MATUNIT_ID1 MATUNIT_ID11

FROM

(

SELECT t1.MASS MASS0, t1.MATUNIT_ID MATUNIT_ID1, t1.STATE STATE2

FROM

(

SELECT MATUNIT_ID, STATE, MASS FROM MES.MATUNITS

) t1

WHERE t1.STATE=1

) t1

UNION

SELECT (t1.MASS1-t1.MASS000) f0, t1.CONSUMED_MATUNIT_ID112 CONSUMED_MATUNIT_ID1121

FROM

(

SELECT t1.MASS00 MASS000, t2.MASS MASS1, t1.CONSUMED_MATUNIT_ID11 CONSUMED_MATUNIT_ID112, t2.STATE STATE3

FROM

(

SELECT (SUM(t1.MASS0)) MASS00, t1.CONSUMED_MATUNIT_ID1 CONSUMED_MATUNIT_ID11

FROM

(

SELECT t1.MASS MASS0, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID1, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID2, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID3, t1.PRODUCED_MATUNIT_ID PRODUCED_MATUNIT_ID4

FROM

(

SELECT CONSUMED_MATUNIT_ID, PRODUCED_MATUNIT_ID, MASS FROM MES.MASSTRANSES

) t1

) t1

GROUP BY t1.CONSUMED_MATUNIT_ID1

) t1,

(

SELECT MATUNIT_ID, STATE, MASS FROM MES.MATUNITS

) t2

WHERE t2.MATUNIT_ID=t1.CONSUMED_MATUNIT_ID11

AND (t2.STATE=2)

) t1

UNION

SELECT (0) f0, t1.MATUNIT_ID0 MATUNIT_ID01

FROM

(

SELECT t1.MATUNIT_ID MATUNIT_ID0, t1.STATE STATE1

FROM

(

SELECT MATUNIT_ID, STATE FROM MES.MATUNITS

) t1

WHERE t1.STATE=3

) t1

) t1,

(

SELECT MATUNIT_ID, COMORDER_ID FROM MES.MATUNITS

) t2

WHERE t2.MATUNIT_ID=t1.MATUNIT_ID1111

) t1

GROUP BY t1.COMORDER_ID1

) t1

) t1,

(

SELECT COMORDER_ID, OPT_MASS FROM MES.COMORDERS

) t2

WHERE t2.COMORDER_ID=t1.COMORDER_ID111

) t1

Query: Production Queries.Testing.All material units with commercial orders ordered by sizes

Natural texts:

All material units with thier parameters and commerical orders with parameters ordered by sizes

Formal texts:

((Material Unit-Thickness), (Material Unit-Width), (Material Unit-Mass(Material Unit)), (Material Unit-Commercial Order-Optimal Mass-Mass(Commercial Order)))

SCQL query statement:

(

(General.Physical Object.Geometry.Thickness, Production.Material Unit.Material Unit),

(General.Physical Object.Geometry.Width, Production.Material Unit.Material Unit),

(General.Physical Object.Gravity.Mass(Material Unit), Production.Material Unit.Material Unit),

(Production.Commercial Order.Commercial Order, Production.Material Unit.Material Unit),

(Production.Commercial Order.Commercial Order, Production.Commercial Order.Optimal Mass),

(General.Physical Object.Gravity.Mass(Commercial Order), Production.Commercial Order.Optimal Mass)

)

ORDER BY General.Physical Object.Geometry.Thickness DESC, General.Physical Object.Geometry.Width DESC, General.Physical Object.Gravity.Mass(Material Unit) ASC

SQL query statement:

SELECT t2."GEOMETRY.THICKNESS" "GEOMETRY.THICKNESS0", t2."GEOMETRY.WIDTH" "GEOMETRY.WIDTH1", t1.OPT_MASS OPT_MASS2, t2.MASS MASS3, t1.COMORDER_ID COMORDER_ID4, t1.OPT_MASS OPT_MASS5, t2.MATUNIT_ID MATUNIT_ID6

FROM

(

SELECT COMORDER_ID, OPT_MASS FROM MES.COMORDERS

) t1,

(

SELECT MATUNIT_ID, GEOMETRY.THICKNESS "GEOMETRY.THICKNESS", GEOMETRY.WIDTH "GEOMETRY.WIDTH", COMORDER_ID, MASS FROM MES.MATUNITS

) t2

WHERE t2.COMORDER_ID=t1.COMORDER_ID

ORDER BY t2."GEOMETRY.THICKNESS" DESC, t2."GEOMETRY.WIDTH" DESC, t2.MASS

Query: Production Queries.Testing.Higher divisions of production units (if exist)

Natural texts:

All higher divisions of production units only if they exist

Formal texts:

(Production Unit-Production Division(Subordinate)-Subordinate Production Division-Higher Production Division-Production Division(Higher))

SCQL query statement:

(

(Production.Production Division.Production Unit, Production.Production Division.Production Division(Subordinate)),

(Production.Production Division.Production Division(Subordinate), Production.Production Division.Subordinate Production Division),

(Production.Production Division.Subordinate Production Division, Production.Production Division.Higher Production Division),

(Production.Production Division.Production Division(Higher), Production.Production Division.Higher Production Division)

)

SQL query statement:

SELECT t1.PRODDIVISION_ID PRODDIVISION_ID0, t1.HIGHER_PRODDIVISION_ID HIGHER_PRODDIVISION_ID1, t1.PRODDIVISION_ID PRODDIVISION_ID2, t1.PRODDIVISION_ID PRODDIVISION_ID3, t1.HIGHER_PRODDIVISION_ID HIGHER_PRODDIVISION_ID4

FROM

(

SELECT PRODDIVISION_ID, HIGHER_PRODDIVISION_ID FROM MES.PRODDIVISIONS

) t1,

(

SELECT PRODUNIT_ID FROM MES.PRODUNITS

) t2

WHERE t2.PRODUNIT_ID=t1.PRODDIVISION_ID

Query: Production Queries.Testing.Mass tansitions with material units

Natural texts:

All mass transitions and their consumed and produced material units

Formal texts:

(Material Unit(Consumed)-Consumed Material Unit-Mass Transition-Produced Material Unit-Material Unit(Produced))

SCQL query statement:

(

(Production.Material Unit.Material Unit(Consumed), Production.Indiscrete.Consumed Material Unit),

(Production.Indiscrete.Consumed Material Unit, Production.Indiscrete.Mass Transition),

(Production.Indiscrete.Produced Material Unit, Production.Indiscrete.Mass Transition),

(Production.Material Unit.Material Unit(Produced), Production.Indiscrete.Produced Material Unit)

)

SQL query statement:

SELECT t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID0, t1.PRODUCED_MATUNIT_ID PRODUCED_MATUNIT_ID1, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID2, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID3, t1.PRODUCED_MATUNIT_ID PRODUCED_MATUNIT_ID4, t1.PRODUCED_MATUNIT_ID PRODUCED_MATUNIT_ID5

FROM

(

SELECT CONSUMED_MATUNIT_ID, PRODUCED_MATUNIT_ID FROM MES.MASSTRANSES

) t1

Query: Production Queries.Testing.Grandparent divisions of production units

Natural texts:

Divisions that are higher for higher divisions of production units

Formal texts:

(Production Unit-Production Division(0)-Subordinate Production Division(0)-Higher Production Division(0)-Production Division(1)-Subordinate Production Division(1)-Higher Production Division(1)-Peoduction Division(2)-Full Name)

SCQL query statement:

(

(Production.Production Division.Production Unit, Production.Production Division.Production Division(0)),

(Production.Production Division.Production Division(0), Production.Production Division.Subordinate Production Division(0)),

(Production.Production Division.Subordinate Production Division(0), Production.Production Division.Higher Production Division(0)),

(Production.Production Division.Production Division(1), Production.Production Division.Higher Production Division(0)),

(Production.Production Division.Production Division(1), Production.Production Division.Subordinate Production Division(1)),

(Production.Production Division.Subordinate Production Division(1), Production.Production Division.Higher Production Division(1)),

(Production.Production Division.Production Division(2), Production.Production Division.Higher Production Division(1)),

(Production.Production Division.Production Division(2), Production.Production Division.Full Name)

)

SQL query statement:

SELECT t3.PRODDIVISION_ID PRODDIVISION_ID0, t3.PRODDIVISION_ID PRODDIVISION_ID1, t2.PRODDIVISION_ID PRODDIVISION_ID2, t1.PRODDIVISION_ID PRODDIVISION_ID3, t3.PRODDIVISION_ID PRODDIVISION_ID4, t2.PRODDIVISION_ID PRODDIVISION_ID5, t2.PRODDIVISION_ID PRODDIVISION_ID6, t1.PRODDIVISION_ID PRODDIVISION_ID7, t1.FULLNAME FULLNAME8

FROM

(

SELECT PRODDIVISION_ID, FULLNAME FROM MES.PRODDIVISIONS

) t1,

(

SELECT PRODDIVISION_ID, HIGHER_PRODDIVISION_ID FROM MES.PRODDIVISIONS

) t2,

(

SELECT PRODDIVISION_ID, HIGHER_PRODDIVISION_ID FROM MES.PRODDIVISIONS

) t3,

(

SELECT PRODUNIT_ID FROM MES.PRODUNITS

) t4

WHERE t4.PRODUNIT_ID=t3.PRODDIVISION_ID AND t3.HIGHER_PRODDIVISION_ID=t2.PRODDIVISION_ID AND t2.HIGHER_PRODDIVISION_ID=t1.PRODDIVISION_ID

Query: Production Queries.Testing.Material units and commercial orders having the same mass

Natural texts:

All material units and commercial orders having the same mass and optimal mass

Formal texts:

(Material Unit-Mass-Optimal Mass-Commercial Order)

SCQL query statement:

(

(General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit),

(General.Physical Object.Gravity.Mass, Production.Commercial Order.Optimal Mass),

(Production.Commercial Order.Commercial Order, Production.Commercial Order.Optimal Mass)

)

SQL query statement:

SELECT t1.OPT_MASS OPT_MASS0, t1.COMORDER_ID COMORDER_ID1, t1.OPT_MASS OPT_MASS2, t2.MATUNIT_ID MATUNIT_ID3

FROM

(

SELECT COMORDER_ID, OPT_MASS FROM MES.COMORDERS

) t1,

(

SELECT MATUNIT_ID, MASS FROM MES.MATUNITS

) t2

WHERE t2.MASS=t1.OPT_MASS

Query: Production Queries.Testing.Material units used for production of other material units

Natural texts:

All material units that are consumed for producing other material units

Formal texts:

(Material Unit(Consumed)-Consumed Material Unit-Mass Transition-Produced Material Unit-Material Unit(Produced))

SCQL query statement:

(

(Production.Material Unit.Material Unit(Consumed), Production.Indiscrete.Consumed Material Unit),

(Production.Indiscrete.Consumed Material Unit, Production.Indiscrete.Mass Transition),

(Production.Indiscrete.Produced Material Unit, Production.Indiscrete.Mass Transition),

(Production.Material Unit.Material Unit(Produced), Production.Indiscrete.Produced Material Unit)

)

SQL query statement:

SELECT t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID0, t1.PRODUCED_MATUNIT_ID PRODUCED_MATUNIT_ID1, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID2, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID3, t1.PRODUCED_MATUNIT_ID PRODUCED_MATUNIT_ID4, t1.PRODUCED_MATUNIT_ID PRODUCED_MATUNIT_ID5

FROM

(

SELECT CONSUMED_MATUNIT_ID, PRODUCED_MATUNIT_ID FROM MES.MASSTRANSES

) t1

Query: Production Queries.Testing.Grandparent material units used for production of other material units

Natural texts:

All material units that are consumed for producing other material units

Formal texts:

(Material Unit(0)-Consumed Material Unit(0)-Mass Transition(0)-Produced Material Unit(0)-Material Unit(1)-Consumed Material Unit(1)-Mass Transition(1)-Produced Material Unit(1)-Material Unit(2)-Mass)

SCQL query statement:

(

(Production.Material Unit.Material Unit(0), Production.Indiscrete.Consumed Material Unit(0)),

(Production.Indiscrete.Consumed Material Unit(0), Production.Indiscrete.Mass Transition(0)),

(Production.Indiscrete.Produced Material Unit(0), Production.Indiscrete.Mass Transition(0)),

(Production.Material Unit.Material Unit(1), Production.Indiscrete.Produced Material Unit(0)),

(Production.Material Unit.Material Unit(1), Production.Indiscrete.Consumed Material Unit(1)),

(Production.Indiscrete.Consumed Material Unit(1), Production.Indiscrete.Mass Transition(1)),

(Production.Indiscrete.Produced Material Unit(1), Production.Indiscrete.Mass Transition(1)),

(Production.Material Unit.Material Unit(2), Production.Indiscrete.Produced Material Unit(1)),

(General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit(2))

)

SQL query statement:

SELECT t3.MASS MASS0, t2.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID1, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID2, t3.MATUNIT_ID MATUNIT_ID3, t2.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID4, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID5, t2.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID6, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID7, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID8, t3.MATUNIT_ID MATUNIT_ID9, t1.CONSUMED_MATUNIT_ID CONSUMED_MATUNIT_ID10, t3.MATUNIT_ID MATUNIT_ID11

FROM

(

SELECT CONSUMED_MATUNIT_ID, PRODUCED_MATUNIT_ID FROM MES.MASSTRANSES

) t1,

(

SELECT CONSUMED_MATUNIT_ID, PRODUCED_MATUNIT_ID FROM MES.MASSTRANSES

) t2,

(

SELECT MATUNIT_ID, MASS FROM MES.MATUNITS

) t3

WHERE t2.PRODUCED_MATUNIT_ID=t1.CONSUMED_MATUNIT_ID AND t2.PRODUCED_MATUNIT_ID=t1.CONSUMED_MATUNIT_ID AND t3.MATUNIT_ID=t1.PRODUCED_MATUNIT_ID

Query: Production Queries.Testing.Calculated length of material units

Natural texts:

Length of material units calculated from their thickness, width, and mass

Formal texts:

((Thickness-Material Units-Width), (Material Unit-Mass)).(Material Unit, Thickness*Width/(Mass*(8-0,2)) ~(Length))

SCQL query statement:

SELECT (General.Physical Object.Geometry.Thickness*General.Physical Object.Geometry.Width/(General.Physical Object.Gravity.Mass*(7.8-0.2)) -> (Length), Production.Material Unit.Material Unit) FROM

(

(General.Physical Object.Geometry.Thickness, Production.Material Unit.Material Unit),

(General.Physical Object.Geometry.Width, Production.Material Unit.Material Unit),

(General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit)

)

SQL query statement:

SELECT (t1."GEOMETRY.THICKNESS0"*t1."GEOMETRY.WIDTH1"/(t1.MASS2*(7.8-0.2))) f0, t1.MATUNIT_ID3 MATUNIT_ID31

FROM

(

SELECT t1."GEOMETRY.THICKNESS" "GEOMETRY.THICKNESS0", t1."GEOMETRY.WIDTH" "GEOMETRY.WIDTH1", t1.MASS MASS2, t1.MATUNIT_ID MATUNIT_ID3

FROM

(

SELECT MATUNIT_ID, GEOMETRY.THICKNESS "GEOMETRY.THICKNESS", GEOMETRY.WIDTH "GEOMETRY.WIDTH", MASS FROM MES.MATUNITS

) t1

) t1

Query: Production Queries.Testing.Material units with checking of outer composition with several nested outer connection selections

Natural texts:

Formal texts:

(Material Unit-Mass), (Material Unit-Width)+, (Mass-Optimal Mass-Commercial Order)+

SCQL query statement:

(

(General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit),

(General.Physical Object.Geometry.Width, Production.Material Unit.Material Unit)+,

(

(General.Physical Object.Gravity.Mass, Production.Commercial Order.Optimal Mass),

(Production.Commercial Order.Commercial Order, Production.Commercial Order.Optimal Mass)

)+

)

SQL query statement:

SELECT t3."GEOMETRY.WIDTH" "GEOMETRY.WIDTH0", t1.OPT_MASS0 OPT_MASS01, t1.COMORDER_ID1 COMORDER_ID12, t1.OPT_MASS2 OPT_MASS23, t2.MATUNIT_ID MATUNIT_ID4

FROM

(

SELECT t1.OPT_MASS OPT_MASS0, t1.COMORDER_ID COMORDER_ID1, t1.OPT_MASS OPT_MASS2

FROM

(

SELECT COMORDER_ID, OPT_MASS FROM MES.COMORDERS

) t1

) t1,

(

SELECT MATUNIT_ID, MASS FROM MES.MATUNITS

) t2,

(

SELECT MATUNIT_ID, GEOMETRY.WIDTH "GEOMETRY.WIDTH" FROM MES.MATUNITS

) t3

WHERE t2.MASS=t1.OPT_MASS0(+) AND t2.MATUNIT_ID=t3.MATUNIT_ID(+)

Query: Production Queries.Testing.Average mass of material units within each thickness/width pair

Natural texts:

Formal texts:

((Thickness-Material Unit-Width), (Material Unit-Mass)).(AVG(Mass) ~Mass, Thickness, Width)

SCQL query statement:

SELECT (General.Physical Object.Geometry.Thickness, General.Physical Object.Geometry.Width, AVG(General.Physical Object.Gravity.Mass) -> General.Physical Object.Gravity.Mass) FROM

(

(General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit),

(General.Physical Object.Geometry.Width, Production.Material Unit.Material Unit),

(General.Physical Object.Geometry.Thickness, Production.Material Unit.Material Unit)

)

SQL query statement:

SELECT (AVG(t1.MASS2)) MASS20, t1."GEOMETRY.THICKNESS0" "GEOMETRY.THICKNESS01", t1."GEOMETRY.WIDTH1" "GEOMETRY.WIDTH12"

FROM

(

SELECT t1."GEOMETRY.THICKNESS" "GEOMETRY.THICKNESS0", t1."GEOMETRY.WIDTH" "GEOMETRY.WIDTH1", t1.MASS MASS2, t1.MATUNIT_ID MATUNIT_ID3

FROM

(

SELECT MATUNIT_ID, GEOMETRY.THICKNESS "GEOMETRY.THICKNESS", GEOMETRY.WIDTH "GEOMETRY.WIDTH", MASS FROM MES.MATUNITS

) t1

) t1

GROUP BY t1."GEOMETRY.THICKNESS0", t1."GEOMETRY.WIDTH1"

Query: Production Queries.Testing.Material units having area between 1000 and 5000, and thickness less 0,5 or width less 1

Natural texts:

Formal texts:

((Thickness-Material Unit-Width), query[Calculated length of material units]).(Material Unit, (Width*(Length)) ~Area, Thickness, Width), (Thickness < 0,5 OR Width < 1), ((Area)>=1000 AND (Area) <= 5000)

SCQL query statement:

(

SELECT (General.Physical Object.Geometry.Width*(Length) -> (Area), General.Physical Object.Geometry.Thickness, General.Physical Object.Geometry.Width, Production.Material Unit.Material Unit) FROM

(

(General.Physical Object.Geometry.Thickness, Production.Material Unit.Material Unit),

(General.Physical Object.Geometry.Width, Production.Material Unit.Material Unit),

SELECT (General.Physical Object.Geometry.Thickness*General.Physical Object.Geometry.Width/(General.Physical Object.Gravity.Mass*(7.8-0.2)) -> (Length), Production.Material Unit.Material Unit) FROM

(

(General.Physical Object.Geometry.Thickness, Production.Material Unit.Material Unit),

(General.Physical Object.Geometry.Width, Production.Material Unit.Material Unit),

(General.Physical Object.Gravity.Mass, Production.Material Unit.Material Unit)

)

),

(General.Physical Object.Geometry.Thickness<0.5 OR General.Physical Object.Geometry.Width<1),

((Area)>=1000 AND (Area)<=5000)

)

SQL query statement:

SELECT t1.f0 f00, t1."GEOMETRY.THICKNESS11" "GEOMETRY.THICKNESS111", t1."GEOMETRY.WIDTH22" "GEOMETRY.WIDTH222", t1.MATUNIT_ID3133 MATUNIT_ID31333

FROM

(

SELECT (t1."GEOMETRY.WIDTH2"*t1.f00) f0, t1."GEOMETRY.THICKNESS1" "GEOMETRY.THICKNESS11", t1."GEOMETRY.WIDTH2" "GEOMETRY.WIDTH22", t1.MATUNIT_ID313 MATUNIT_ID3133

FROM

(

SELECT t1.f0 f00, t2."GEOMETRY.THICKNESS" "GEOMETRY.THICKNESS1", t2."GEOMETRY.WIDTH" "GEOMETRY.WIDTH2", t1.MATUNIT_ID31 MATUNIT_ID313

FROM

(

SELECT (t1."GEOMETRY.THICKNESS0"*t1."GEOMETRY.WIDTH1"/(t1.MASS2*(7.8-0.2))) f0, t1.MATUNIT_ID3 MATUNIT_ID31

FROM

(

SELECT t1."GEOMETRY.THICKNESS" "GEOMETRY.THICKNESS0", t1."GEOMETRY.WIDTH" "GEOMETRY.WIDTH1", t1.MASS MASS2, t1.MATUNIT_ID MATUNIT_ID3

FROM

(

SELECT MATUNIT_ID, GEOMETRY.THICKNESS "GEOMETRY.THICKNESS", GEOMETRY.WIDTH "GEOMETRY.WIDTH", MASS FROM MES.MATUNITS

) t1

) t1

) t1,

(

SELECT MATUNIT_ID, GEOMETRY.THICKNESS "GEOMETRY.THICKNESS", GEOMETRY.WIDTH "GEOMETRY.WIDTH" FROM MES.MATUNITS

) t2

WHERE t2.MATUNIT_ID=t1.MATUNIT_ID31

) t1

) t1

WHERE ((t1."GEOMETRY.THICKNESS11"<0.5 OR t1."GEOMETRY.WIDTH22"<1)) AND t1.f0>=1000 AND t1.f0<=5000