SELECT Statements

Skyvia supports most SQL features for SELECT statements. Here you can see some of the supported features and examples of the queries.

Simple SELECT Statements

The following example selects account names and their types from Salesforce:

1

SELECT "Name", "Type" FROM "Account"

SELECT Statements with *

The following example selects all the account information from Salesforce:

1

SELECT * FROM "Account"

Quoted Identifiers, Aliases.

The following example selects account names and sites from Zoho CRM:

1
2

SELECT "t"."Account Name", "t"."Account Site" AS Site
    FROM "Accounts" AS t

WHERE Clause, LIKE Operator

Skyvia supports WHERE clauses and LIKE operator.

The following query selects all Salesforce users with the name, containing John.

1
2
3

SELECT "t".*
    FROM "User" AS t
    WHERE t."Name" LIKE '%John%'

Operations with Dates

Skyvia allows performing various operations with dates, using date and strftime SQL functions.

The following query demonstrates use of these functions. It queries Salesforce accounts, inactive for last 30 days, and returns the year of the last activity, the date of the first day of inactivity, and account name:

1
2
3
4
5
6
7

SELECT
    a."LastActivityDate" 'Last Activity',
    strftime('%Y', a."LastActivityDate") 'Last Activity Year',
    DATE(a."LastActivityDate", '+1 day') 'Next Day',
    a."Name" 'Account Name'
FROM "Account" a
WHERE DATE("LastActivityDate") < DATE('now', 'localtime', '-30 days')

ORDER BY and LIMIT Clauses

Skyvia supports ORDER BY and LIMIT clauses.

The following query takes all campaigns TOP 10 opportunities based on the estimated revenue from Dynamics 365:

1
2
3
4
5
6
7
8
9
10
11

SELECT
    o."name" 'Topic', o."estimatedvalue" 'Est. Revenue'
FROM 
    "opportunity" o
INNER JOIN 
    "owner" AS owner_o ON o."ownerid" = owner_o."ownerid"
WHERE 
    o."statecode"='Open' AND owner_o."name" = 'Devart Corp'
ORDER BY 
    o."estimatedvalue" DESC
LIMIT 10

SELECT statements with JOINs

Skyvia supports uniting more than two tables with JOINs. Skyvia supports INNER, OUTER, and CROSS JOINs.

The following query selects detailed information on the opportunities from Salesforce — which products at which quantity and which price are sold within each opportunity, the date when the opportunity is expected to close, and whether the opportunity is won:

1
2
3
4
5
6
7
8
9
10
11
12

SELECT
    Opportunity.Name,
    Opportunity.CloseDate,
    Opportunity.IsWon,
    Product2.Name AS Product,
    OpportunityLineItem.Quantity,
    OpportunityLineItem.UnitPrice
FROM OpportunityLineItem
INNER JOIN Opportunity
    ON OpportunityLineItem.OpportunityId = Opportunity.Id
INNER JOIN Product2
    ON OpportunityLineItem.Product2Id = Product2.Id

Complex WHERE Clauses

Skyvia supports complex WHERE clauses with numerous conditions united with logical operators are also supported.

The following query selects detailed information on the opportunities from Salesforce — which products at which quantity and which price are sold within each opportunity, the date when the opportunity is expected to close, filtered by pricebooks used, the date when the opportunity is expected to close, and opportunity names:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

SELECT
    Opportunity.Name,
    Opportunity.CloseDate,
    Product2.Name,
    OpportunityLineItem.Quantity,
    OpportunityLineItem.UnitPrice
FROM OpportunityLineItem
INNER JOIN Opportunity
    ON OpportunityLineItem.OpportunityId = Opportunity.Id
INNER JOIN Product2
    ON OpportunityLineItem.Product2Id = Product2.Id
INNER JOIN PricebookEntry
    ON OpportunityLineItem.PricebookEntryId = PricebookEntry.Id
INNER JOIN Pricebook2
    ON PricebookEntry.Pricebook2Id = Pricebook2.Id
WHERE OpportunityLineItem.PricebookEntryId IS NOT NULL
    AND (Pricebook2.Name = 'Standard'
    OR Pricebook2.Name = 'Discounted')
    AND Opportunity.CloseDate BETWEEN '2015-01-01' AND '2015-12-31'
    AND Opportunity.Name LIKE '%order%'

Aggregation

Skyvia supports aggregation in SELECT statements using GROUP BY and HAVING clauses. The following query returns the number of opportunities per account where account is of type ‘Customer’:

1
2
3
4
5
6
7
8
9
10

SELECT
    Account.Name,
    COUNT(Opportunity.Id) AS expr1,
    Account.Type
FROM Opportunity
INNER JOIN Account
    ON Opportunity.AccountId = Account.Id
GROUP BY Account.Name,
    Account.Type
HAVING Account.Type LIKE 'Customer%'

Expressions Including Several Columns

The following query gets total revenue from Salesforce opportunities per account:

1
2
3
4
5
6
7
8
9
10
11

SELECT
    Account.Name,
    Account.Type,
    SUM(OpportunityLineItem.UnitPrice * OpportunityLineItem.Quantity) AS Total
FROM Opportunity
INNER JOIN Account
    ON Opportunity.AccountId = Account.Id
INNER JOIN OpportunityLineItem
    ON OpportunityLineItem.OpportunityId = Opportunity.Id
GROUP BY Account.Name,
    Account.Type

CASE, IN Expressions, SQL Functions

The following query takes all campaigns for the current fiscal year from Dynamics 365 and compares the budgeted amount versus the actual costs recorded to run the campaign:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SELECT 
    (CASE WHEN (strftime('%m', c."actualend") IN ('01','02','03')) THEN 'Q1' 
         WHEN (strftime('%m', c."actualend") IN ('04','05','06')) THEN 'Q2' 
         WHEN (strftime('%m', c."actualend") IN ('07','08','09')) THEN 'Q3' 
         WHEN (strftime('%m', c."actualend") in ('10','11','12')) THEN 'Q4'
         ELSE 'blank' END ) "Quarter" ,
    SUM(c."budgetedcost") "Budget Allocated",
    SUM(c."totalactualcost") "Total Cost of Campaign"
FROM campaign c
WHERE ( strftime('%Y', c."actualstart") = strftime('%Y','now')
    OR strftime('%Y', c."actualend") = strftime('%Y','now')) 
    AND c."statuscode" not in ('Canceled', 'Inactive', 'Suspended')
GROUP BY 1
ORDER BY 1

You can find more query examples in our Gallery.

On This Page