Multi-path cascaded deletes & updates

EffiProz 1.6 beta allows you to manually control if a cascaded delete of a row that is being cascaded update  by the same operation  generate an error (default behavior).
Use SET DATABASE SQL TDC DELETE statement to control this behavior as show in the following example,

create table Tbl1(col1 int primary key, col2 int);
insert into Tbl1 values(1,100);
insert into Tbl1 values(2,200);

create table Tbl2(col1 int foreign key references Tbl1(col1) on delete cascade, col2 int unique);
insert into Tbl2 values(1,300);
insert into Tbl2 values(2,400);

create table Tbl3(col1 int primary key foreign key references Tbl1(col1) on delete cascade, col2 int foreign key references Tbl2(col2) on delete SET NULL, col3 varchar(100) );
insert into Tbl3 values(1,300,'car');
insert into Tbl3 values(2,400,'bus');

/*ERROR*/delete from Tbl1 where col1=1;

SET DATABASE SQL TDC DELETE FALSE;
delete from Tbl1 where col1=1;

SET DATABASE SQL TDC DELETE TRUE;
/*ERROR*/delete from Tbl1 where col1=2;

Unique NULLs and Concat NULLs

Two new configuration options, SQL UNIQUE NULLS and SQL CONCAT NULLS, in EffiProz 1.6 can be used to control how EffiProz treat NULL values in unique constrain evaluations and in string concatenation operation.
Following example shows how SQL UNIQUE NULLS works,

create table uqtest(col1 int, col2 int, unique(col1,col2));

SET DATABASE SQL UNIQUE NULLS TRUE;
insert into uqtest values(1,null);
insert into uqtest values(1,null);
delete from uqtest;

SET DATABASE SQL UNIQUE NULLS FALSE;

insert into uqtest values(1,null);
/*ERROR*/insert into uqtest values(1,null);

Following example shows how SQL CONCAT NULLS works,

SET DATABASE SQL CONCAT NULLS TRUE;
/*result NULL*/select 'efz' ||convert( null,varchar(100));

SET DATABASE SQL CONCAT NULLS FALSE;
/*result efz*/select 'efz' || convert( null,varchar(100));

Grouping JOIN Clauses

EffiProz 1.6 will fully support Parenthesis Grouped Join Clauses.
Download EffiProz 1.6 beta from http://www.effiproz.com/downloads/EffiProz_1_6_4087_16609_beta.zip

A sample query with Parenthesis Grouped Join Clause on Northwind database,

SELECT "Invoices2" AS SELECT "Orders"."ShipName", "Orders"."ShipAddress", "Orders"."ShipCity", "Orders"."ShipRegion", "Orders"."ShipPostalCode", 
 "Orders"."ShipCountry", "Orders"."CustomerID", "Customers"."CompanyName" AS "CustomerName", "Customers"."Address", "Customers"."City", 
 "Customers"."Region", "Customers"."PostalCode", "Customers"."Country", 
 ("FirstName" + ' ' + "LastName") AS "Salesperson", 
 "Orders"."OrderID", "Orders"."OrderDate", "Orders"."RequiredDate", "Orders"."ShippedDate", "Shippers"."CompanyName" As "ShipperName", 
 "Order Details"."ProductID", "Products"."ProductName", "Order Details"."UnitPrice", "Order Details"."Quantity", 
 "Order Details"."Discount", 
 (CONVERT(("Order Details"."UnitPrice"*"Quantity"*(1-"Discount")/100),decimal)*100) AS "ExtendedPrice", "Orders"."Freight"
 FROM  "Shippers" INNER JOIN 
 ("Products" INNER JOIN 
 (
 ("Employees" INNER JOIN 
 ("Customers" INNER JOIN "Orders" ON "Customers"."CustomerID" = "Orders"."CustomerID") 
 ON "Employees"."EmployeeID" = "Orders"."EmployeeID") 
 INNER JOIN "Order Details" ON "Orders"."OrderID" = "Order Details"."OrderID") 
 ON "Products"."ProductID" = "Order Details"."ProductID") 
 ON "Shippers"."ShipperID" = "Orders"."ShipVia";
 

Which is equivalent to the following query,

SELECT "Invoices" AS
 SELECT "Orders"."ShipName", "Orders"."ShipAddress", "Orders"."ShipCity", "Orders"."ShipRegion", "Orders"."ShipPostalCode", 
 "Orders"."ShipCountry", "Orders"."CustomerID", "Customers"."CompanyName" AS "CustomerName", "Customers"."Address", "Customers"."City", 
 "Customers"."Region", "Customers"."PostalCode", "Customers"."Country", 
 ("FirstName" + ' ' + "LastName") AS "Salesperson", 
 "Orders"."OrderID", "Orders"."OrderDate", "Orders"."RequiredDate", "Orders"."ShippedDate", "Shippers"."CompanyName" As "ShipperName", 
 "Order Details"."ProductID", "Products"."ProductName", "Order Details"."UnitPrice", "Order Details"."Quantity", 
 "Order Details"."Discount", 
 (CONVERT(("Order Details"."UnitPrice"*"Quantity"*(1-"Discount")/100),decimal)*100) AS "ExtendedPrice", "Orders"."Freight"
 FROM "Shippers" ,"Products","Employees","Customers","Orders","Order Details"
 WHERE
 "Shippers"."ShipperID" = "Orders"."ShipVia" 
 AND "Products"."ProductID" = "Order Details"."ProductID"
 AND "Orders"."OrderID" = "Order Details"."OrderID"
 AND "Customers"."CustomerID" = "Orders"."CustomerID"
 AND "Employees"."EmployeeID" = "Orders"."EmployeeID";

ROWNUM pseudo-column

EffiProz 1.6 is to support  ROWNUM pseudo-column.
Download EffiProz 1.6 beta from http://www.effiproz.com/downloads/EffiProz_1_6_4087_16609_beta.zip

select name, price  from items where rownum < 6;