SELECT Statements

Skyvia supports most SQL features for SELECT statements.

Simple SELECT statements.
 
The following example selects account names and their types from Salesforce:

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

SELECT statements with *.
 
The following example selects all the account information from Salesforce:

SELECT * FROM "Account"

SELECT statements with quoted identifiers, aliases.
 
The following example selects account names and sites from Zoho CRM:

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

Skyvia supports WHERE clauses and LIKE operator.
 
The following query selects all Salesforce users with the name, containing John.

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

Skyvia allows performing various operations with dates, using date and strftime SQL functions.
 
The following query demonstrates using 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:

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 are supported too.
 
The following query takes all campaigns TOP 10 opportunities based on the estimated revenue from Dynamics CRM:

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:

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 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:

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 with GROUP BY and HAVING clauses is supported.
 
The following query returns the number of opportunities per account where account is of type 'Customer':

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 are supported.
 
The following query gets total revenue from Salesforce opportunities per account:

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, various SQL functions are supported.
 
The following query takes all campaigns for the current fiscal year from Dynamics CRM and compares the budgeted amount versus the actual costs recorded to run the campaign:

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.

note Note

Skyvia does not support subqueries.