SQL

SQL Step is the core transform you can use to filter, transform and aggregate your data.

What it does

Query & Transform your data with SQL. All SQL steps use PCOLLECTION as the table name, it stands for the input data that connects to the SQL step

Settings

• SQL

  the SQL query

Basic syntax

This page describes the SQL dialect supported by Desktop application.

SQL Reference Guide

SQL is the most powerful Query language in earth. Here are a few things you can do in SQL to get started.

Filtering Column

WHERE Day_of_Week = 1

Aggregating Column

SELECT count(*), min(size), max(size), average(size)

Renaming column

SELECT Id as CustomerId

Some maths on columns

SELECT size - 20, size * 20, weigh / size as ratio

IF logic on columns

SELECT case when gender='Male' then 'M' else 'F' END 

Tips

• Sometimes columns contains spaces and SQL columns is expected to be space-free. Use quote in that case

 select `Sale rep name`

• the FROM PCOLLECTION is the default clause. You do not need to change it.