SQL Standard

This section discusses the SQL syntax for the Lyftrondata driver. Our driver adheres to ANSI standards, and all of our Python drivers adhere to the Sql Alchemy framework.

Select Statements

The Lyftrondata driver for Act!Crm provides ANSI SQL standard support. A SELECT statement can consist of the following basic clauses.

• SELECT

• INTO

• FROM

• JOIN

• WHERE

• GROUP BY

• HAVING

• UNION

• ORDER BY

• LIMIT

• PAGESIZE LIMIT

SELECT Syntax

The following syntax diagram outlines the syntax supported by the SQL engine of the provider:

SELECT {  [ TOP <numeric_literal> | DISTINCT ]  {    *| {<expression> [ [ AS ] <column_reference> ]        | { <table_name> | <correlation_name> } .*      } [ , ... ]}  [ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]  {    FROM <table_reference> [[ AS ] <identifier> ]  } [ , ... ]  [ [       INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }    ] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]  ] [ ... ]  [ WHERE <search_condition> ]  [ GROUP BY <column_reference> [ , ... ]  [ HAVING <search_condition> ]  [ UNION [ ALL ] <select_statement> ]  [    ORDER BY    <column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]  ]  [    LIMIT <expression>    [      { OFFSET | , }      <expression>    ]  ]} | SCOPE_IDENTITY() <expression> ::=  | <column_reference>  | @ <parameter>  | ?  | COUNT( * | { [ DISTINCT ] <expression> } )  | { AVG | MAX | MIN | SUM | COUNT } ( <expression> )  | NULLIF ( <expression> , <expression> )  | COALESCE ( <expression> , ... )  | CASE <expression>      WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]    [ ELSE { <expression> | NULL } ]    END  | <literal>  | <sql_function> <search_condition> ::=  {<expression> {{= | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]  }} [ {{ AND | OR }} ... ]

Examples

1. Return all columns:

   SELECT * FROM employee;
2. Rename a column:

   SELECT [age] AS MY_Age FROM address;
3. Cast a column's data as a different data type:

   SELECT CAST(mobile AS VARCHAR) AS Str_Mobile FROM customer;
4. Search data:

   SELECT * FROM customer WHERE company_name = 'lyftrondata';
5. Return the number of items matching the query criteria:

   SELECT COUNT(*) AS TotolRows FROM customer;
6. Return the number of unique items matching the query criteria:

   SELECT COUNT(DISTINCT name) FROM customer;
7. Return the unique items matching the query criteria:

   SELECT DISTINCT name FROM customer;
8. Summarize data:

   SELECT priority, MAX(subscriptions) FROM customer GROUP BY subscriptions;
9. Retrieve data from multiple tables.

   SELECT c.name, a.street FROM customer c INNER JOIN address a ON c.customer_id = a.customer_id
10. Sort a result set in ascending order:

    SELECT customer_id, subscriptions FROM customer ORDER BY subscriptions ASC
11. Restrict a result set to the specified number of rows:

    SELECT customer_id, subscriptions FROM customer LIMIT 10
12. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.

    SELECT * FROM incident WHERE category = @param

13. Restrict a result set to the specified number of pages

SELECT * FROM sales_invoice lyftstartpage 1 lyftendpage 10

Quickstart Steps

Do you have questions about how to use the platform? Don't worry; we've got you covered. Simply follow the quickstart instructions here.

Questions?

We're always happy to answer any additional questions you may have! Set up a meeting with our data experts.