Contents

# normal case
- alf: |-
    project(suppliers, [:sid])
  sql: |-
    SELECT t1.sid
      FROM suppliers AS t1
# allbut case
- alf: |-
    project(suppliers, [:sid], allbut: true)
  sql: |-
    SELECT t1.name, t1.status, t1.city
      FROM suppliers AS t1
# unique, non primary key
- alf: |-
    project(suppliers, [:name])
  sql: |-
    SELECT t1.name
      FROM suppliers AS t1
# requiring distinct
- alf: |-
    project(suppliers, [:status])
  sql: |-
    SELECT DISTINCT t1.status
      FROM suppliers AS t1
# a special case where attributes are taken in another order
- alf: |-
    project(suppliers, [:status, :city, :name])
  sql: |-
    SELECT t1.name, t1.status, t1.city
      FROM suppliers AS t1
  comment: |-
    The projection applies to the heading, that keeps its own order. The
    ordering of fields therefore differs from what could be expected. It's
    not a semantics issue, though.
# empty projection
- alf: |-
    project(suppliers, [])
  sql: |-
    SELECT TRUE AS is_table_dee
    WHERE EXISTS(SELECT * FROM suppliers AS t1)
# empty projection or empty projection
- alf: |-
    project(project(suppliers, []), [])
  sql: |-
    SELECT TRUE AS is_table_dee
    WHERE EXISTS(
      SELECT TRUE AS is_table_dee
        WHERE EXISTS(SELECT * FROM suppliers AS t1))
# projection of minus
- alf: |-
    project(minus(suppliers, suppliers_in_london), [:sid])
  sql: |-
    WITH t3 AS (
      (SELECT t1.sid, t1.name, t1.status, t1.city FROM suppliers AS t1)
      EXCEPT
      (SELECT t2.sid, t2.name, t2.status, t2.city FROM suppliers AS t2 WHERE t2.city = 'London')
    ) SELECT t3.sid FROM t3 AS t3
# empty projection of of minus
- alf: |-
    project(minus(suppliers, suppliers_in_london), [])
  sql: |-
    WITH t3 AS (
      (SELECT t1.sid, t1.name, t1.status, t1.city FROM suppliers AS t1)
      EXCEPT
      (SELECT t2.sid, t2.name, t2.status, t2.city FROM suppliers AS t2 WHERE t2.city = 'London')
    ) SELECT TRUE AS is_table_dee WHERE EXISTS(SELECT * FROM t3 AS t3)

Version data entries

4 entries across 4 versions & 1 rubygems

Version	Path
alf-0.16.3	spec/sql/queries/11-project.yml
alf-0.16.2	spec/sql/queries/11-project.yml
alf-0.16.1	spec/sql/queries/11-project.yml
alf-0.16.0	spec/sql/queries/11-project.yml