PipeSQL |>

A lightweight tool and a language to design SQL queries, in bottom-up approach, using pipes and filters

Documentation

This page gives a good introduction to PipeSQL and its syntax.
Each sections contains PipeSQL examples and generated SQL for Oracle database.

Table Statement

variable := [schema.] table

This is used to assign a table/view to a variable. We call this variable as relation variable. Later, this variable will used to access the columns of the table during the "Select statement", "Where statement", "Pipe statement" ..etc.
This statement is similar to SQL FROM clause and variable is similar to TABLE ALIAS.

c := Customers;
select  *
from Customers c 

Following example uses a table/view with schema

c := sales.Customers ;-- accessing a table with schema
select  *
from sales.Customers c 

Following example uses a table/view, which contains space, with schema

c := sales.[External Customers]; 
-- accessing a table and table name contains space
select  *
from sales.[External Customers] c

Where Statement

variable := table predicate [, predicate]

This is used to filter records(rows) from table through predicates. Multiple predicates are included using comma (,) and they are replaced by AND during the SQL generation.
It's possible to include compound predicates with OR operator by including them in parenthesis.
This statement is similar to SQL WHERE clause.

Following built-in functions could be used while building the predicates:
like() : converts to SQL LIKE predicate
in() : converts to SQL IN predicate
eval() : converts the eval() contents to raw SQL expression
: - used to specify a BIND variable

c := Customers 
    c.area = 'Oslo', 
    c.first_name=like('%Ole%'), 
    (c.last_name!='Martin' or c.last_name!='Sundby'),
    c.type!=in('External', 'VIP'),
    2 = eval(5+5 - (2*5)) ;
select  *
from Customers c 
where c.area = 'Oslo'
and c.first_name like '%Ole%'
and ( c.last_name != 'Martin' or c.last_name != 'Sundby' )
and c.type not in ( 'External', 'VIP' )
and 2 = (5+5 - (2*5))

Select Statement

column field [alias] [, field [alias]]

This is used to select the fields from the table and keyword column is used.
This statement is similar to SQL SELECT clause.

c := Customers c.area = 'Oslo'
    column c.customer_id, c.first_name FirstName,
    c.last_name LastName,
    eval(c.first_name || ' ' || c.last_name ) FullName,
    eval(select url_link from Barcode where cust_id = c.customer_id) BarcodeLink ;
select 
  c.customer_id,
  c.first_name FirstName,
  c.last_name LastName,
  (c.first_name || ' ' || c.last_name ) FullName,
  (select url_link from Barcode where cust_id = c.customer_id) BarcodeLink
from Customers c 
where c.area = 'Oslo'

Case Statement

{predicate -> result [, predicate -> result] [default -> result]}

This statement is used to add if...then...else logic, this is similar to SQL CASE clause.
keyword default is used similar to SQL ELSE clause in the CASE.

c := Customers c.area = 'Oslo' ,
    1 = {:FIRST_NAME = null -> 1, 
         upper(:FIRST_NAME) = upper(c.first_name) -> 1,
         default -> 0}
    column c.customer_id, 
        {c.email = null -> 'No Email', default -> email} Email ;
select 
  c.customer_id,
  (case when c.email is null then 'No Email' else email end) Email
from Customers c 
where c.area = 'Oslo'
and 1 = (case 
			when :FIRST_NAME is null then 1 
			when upper(:FIRST_NAME) = upper(c.first_name) 
			then 1 else 0 end)

Join Statement

join [left] table relation_variable on predicate [, predicate]

This is used to join two tables using predicates.
This statement is similar to SQL JOIN clause, but only includes LEFT JOIN and doesn't include RIGHT JOIN.

c := Customers c.area='Oslo'
     join Payments p on 
        p.customer_id = c.customer_id, 
        trunc(p.payment_date, 'YEAR') = '2014'
    column c.customer_id, c.first_name, c.last_name ;
select 
  c.customer_id,
  c.first_name,
  c.last_name
from Customers c 
join Payments p on 
(
p.customer_id = c.customer_id
and trunc(p.payment_date, 'YEAR') = '2014'
)
where c.area = 'Oslo'

Summary Statement

summary summary_function [, summary_function] [by field [,field]]

This is used to summarize the rows in a relation, while projecting, depending on the field combinations.
This statement is similar to SQL GROUP BY clause and all available SQL aggregate built-in functions are available.

Each summary_function will get an automatic alias summaryN. N is a sequence starting from 1 to number of summary functions that have been used.

projection fields, column if exists, will be overriden by summary field list.

p := Payments trunc(p.payment_date, 'YEAR') = '2014'
    summary max(amounts), min(amounts) by p.customer_id ;
select 
  p.customer_id,
  max(amounts) summary1,
  min(amounts) summary2
from Payments p 
where trunc(p.payment_date, 'YEAR') = '2014'
group by
p.customer_id

Sort Statement

sort field [-] [, field [-]]

This is used to sort the rows, in a table, while projecting.
This statement is similar to SQL ORDER BY clause and by default sorting is done on ascending order. To sort on decending order just add - after the field.

c := Customers
    column first_name, last_name, area
    sort first_name, last_name -, area - ;
select 
  first_name,
  last_name,
  area
from Customers c 
order by first_name, last_name desc, area desc

Top Statement

top minimun_records [maximum_records]

This is used to limit the rows, in a table, while projecting. And also used project range of rows.
Remember to sort the table before using top to get meaningful results.

top 30 : This will return the first 30 rows.
top 30 to 50 : This will limit the rows to 50 and then return the rows from 30 - 50.

c := Customers
    column first_name, last_name, area
    sort first_name, last_name -, area -
    top 30 to 50 ; -- this will give range first 30-50 records
    -- top 30 -- this will give first 30 records 
select * 
from (
  select toplist.*, rownum rnum
  from (
  select 
    first_name,
    last_name,
    area
  from Customers c 
  order by first_name, last_name desc, area desc
  ) toplist 
  where rownum <= 50
) toprange
where rnum > 30

Pipe Statement

a := table |> b := table [!]filter [exists] predicate [, predicate]

This statement, |> and filter ,helps to build queries in a bottom-up approach by starting from individual datasets and merge them to produce the final dataset.
Merging is carried in two ways and they are:

  • Join and Left Join
  • Exists and Not Exists predicate
Lets look some example.

c := Customers c.area='Oslo'
|>
p := Payments
    filter p.customer_id = c.customer_id;

Line filter p.customer_id = c.customer_id specifies that to filter Payments records based on fetched Customer records and filter predicate. This is similar to SQL IN cluase used with subquery.

select  *
from Payments p 
join (
  select  *
  from Customers c 
  where c.area = 'Oslo'
) c on 
(
p.customer_id = c.customer_id
)

Now, we look at the example of using NOT ! predicate while filtering.

c := Customers c.area='Oslo'
|>
p := Payments c.customer_id = null
    !filter p.customer_id = c.customer_id
    column distinct p.id, p.customer_id, p.amount ;

Line !filter p.customer_id = c.customer_id specifies that to filter all Payments records which are not based on fetched Customer records and filter predicate. This is similar to SQL NOT IN cluase used with subquery. Remember c.customer_id = null and distinct.

select  distinct p.id, p.customer_id, p.amount
from Payments p 
left join (
  select  *
  from Customers c 
  where c.area = 'Oslo'
) c on 
(
p.customer_id = c.customer_id
)
where c.customer_id is null

Above example using keyword exists to generate with SQL EXISTS clause.

c := Customers c.area='Oslo'
|>
p := Payments
    filter exists p.customer_id = c.customer_id ;
select  *
from Payments p 
where exists (
  select  *
  from Customers c 
  where c.area = 'Oslo'
  and p.customer_id = c.customer_id
)

Above example using keyword exists to generate with SQL NOT EXISTS clause.

c := Customers c.area='Oslo'
|>
p := Payments
    !filter exists p.customer_id = c.customer_id ;
select  *
from Payments p 
where NOT exists (
  select  *
  from Customers c 
  where c.area = 'Oslo'
  and p.customer_id = c.customer_id
)

Union Statement

a := table union [all] b:= table

This is used to append two tables, similar to SQL UNION or SQL UNION ALL clause.

c := Customers c.area='Oslo'
    column c.customer_id, c.first_name
union
c1 := Customers c.area='Trondheim'
    column c1.customer_id, c1.first_name ;
select 
  c1.customer_id,
  c1.first_name
from Customers c1 
where c.area = 'Trondheim'
union
select 
  c.customer_id,
  c.first_name
from Customers c 
where c.area = 'Oslo'

Inject Statement

t := <- relation_variable

Sometimes we need to merge 2 or more pipe statements result into one intermediate result and continue with next operation by operating on the intermediate result.
Inject syntax <- is used to re-assign the result to a new relation variable.

cust := Customers cust.city = 'Berlin'
|>
ord := Orders
    filter ord.CustomerID = cust.CustomerID
    column distinct ord.EmployeeID;

static_orders := Orders ShipCountry = 'Germany'
    column distinct EmployeeID;

re1 := <- ord
union
re2 := <- static_orders;

re3 := <- re2
|>
emp := Employees
    filter re3.EmployeeID = emp.EmployeeId
    column emp.EmployeeID, emp.LastName, emp.FirstName, emp.ReportsTo
|>
mgr := Employees
    filter mgr.EmployeeID = emp.ReportsTo
    column distinct mgr.EmployeeID, mgr.LastName, mgr.FirstName, mgr.ReportsTo;

Here re1 will refer the result of ord and re2 will refer the result of static_orders. Relation variable re3 will refer the result of re2

select distinct
  mgr.EmployeeID,
  mgr.LastName,
  mgr.FirstName,
  mgr.ReportsTo
from Employees mgr 
join (
  select 
    emp.EmployeeID,
    emp.LastName,
    emp.FirstName,
    emp.ReportsTo
  from Employees emp 
  join (
      select  *
      from (
    select  *
    from (
select distinct
  EmployeeID
from Orders static_orders 
where ShipCountry = 'Germany'
) re2 
    union
    select  *
    from (
select distinct
  ord.EmployeeID
from Orders ord 
join (
  select  *
  from Customers cust 
  where cust.city = 'Berlin'
) cust on 
(
ord.CustomerID = cust.CustomerID
)
) re1 
) re3 
) re3 on 
  (
  re3.EmployeeID = emp.EmployeeId
  )
) emp on 
(
mgr.EmployeeID = emp.ReportsTo
)
 

Copyright © 2014 pipesql.com . All Right Reserved