OQL Select Statement corresponds to the select statement of SQL-92.
l Single table select (all the fields):
|
var qryOrder : ISelectQuery; begin qryOrder := TSelectQuery.Create; qryOrder .Select(NW.Order) .From(NW.Order) .Where(OQL .Criteria(NW.Order.EmployeeID).EQ(5) .And_(NW.Order.ShipVia).EQ(1) ); end; |
|
== Equal to the following SQL [MS SQL Server] == |
|
SELECT [Orders].* FROM [Orders] WHERE
[Orders].[EmployeeID] = 5 AND
[Orders].[ShipVia] = 1 |
|
== Equal to the following SQL [Oracle] == |
|
SELECT "Orders".* FROM "Orders" WHERE "Orders"."EmployeeID" =
5 AND "Orders"."ShipVia" =
1 |
l Single table select (some fields):
|
var qryOrder : ISelectQuery; begin qryOrder := TSelectQuery.Create; qryOrder .Select(NW.Order.OrderID)._(NW.Order.OrderDate) ._(NW.Order.RequiredDate) .From(NW.Order) .Where( OQL .Criteria(NW.Order.EmployeeID).EQ(5) .And_(NW.Order.ShipVia).EQ(1) ); end; |
|
== Equal to the following SQL [MS SQL Server] == |
|
SELECT
[Orders].[OrderID] , [Orders].[OrderDate] , [Orders].[RequiredDate] FROM [Orders] WHERE
[Orders].[EmployeeID] = 5 AND
[Orders].[ShipVia] = 1 |
l Multi-table select:
|
var query : ISelectQuery; begin query := TSelectQuery.Create; query .Select(NW.Order.OrderID)._(NW.Order.OrderDate) ._(NW.Order.RequiredDate)._(NW.OrderDetail) .From(NW.Order)._(NW.OrderDetail) .Where( OQL .Criteria(NW.Order.EmployeeID).EQ(5) .And_(NW.Order.ShipVia).EQ(1) .And_(NW.Order.OrderID).EQ(NW.OrderDetail.OrderID) ); end; |
|
== Equal to the following SQL [MS SQL Server] == |
|
SELECT
[Orders].[OrderID] , [Orders].[OrderDate] , [Orders].[RequiredDate] , [Order Details].* FROM
[Orders] , [Order Details] WHERE
[Orders].[EmployeeID] = 5 AND
[Orders].[ShipVia] = 1 AND
[Orders].[OrderID] = [Order Details].[OrderID] |
l Multi-table's Join query:
|
var query : ISelectQuery; begin query := TSelectQuery.Create; query .Select(NW.Order.OrderID)._(NW.Order.OrderDate) ._(NW.Order.RequiredDate)._(NW.OrderDetail) .From(NW.Order) .InnerJoin(NW.OrderDetail) .On_( OQL.Criteria(NW.Order.OrderID).EQ(NW.OrderDetail.OrderID) ) .Where( OQL .Criteria(NW.Order.EmployeeID).EQ(5) .And_(NW.Order.ShipVia).EQ(1) ); end; |
|
== Equal to the following SQL [MS SQL Server] == |
|
SELECT
[Orders].[OrderID] , [Orders].[OrderDate] , [Orders].[RequiredDate] ,
[Order Details].* FROM [Orders]
INNER JOIN
[Order Details]
ON
[Orders].[OrderID] = [Order Details].[OrderID] WHERE
[Orders].[EmployeeID] = 5 AND
[Orders].[ShipVia] = 1 |
l Sub-query:
|
var query : ISelectQuery; subQuery : ISelectQuery; begin subQuery := TSelectQuery.Create; subQuery .Select(NW.Customer.CustomerID) .From(NW.Customer) .Where( OQL.Criteria(NW.Customer.ContactTitle).EQ('Owner') ); query := TSelectQuery.Create; query .Select(NW.Order.OrderID)._(NW.Order.OrderDate) ._(NW.Order.RequiredDate) .From(NW.Order) .Where( OQL.Criteria(NW.Order.CustomerID).In_( subQuery ) ); end; |
|
== Equal to the following SQL [MS SQL Server] == |
|
SELECT
[Orders].[OrderID] , [Orders].[OrderDate] , [Orders].[RequiredDate] FROM [Orders] WHERE
[Orders].[CustomerID] IN
(
SELECT [Customers].[CustomerID]
FROM [Customers]
WHERE
[Customers].[ContactTitle] = 'Owner'
) |
Related Topics