SELECT Statements

Skyvia supports most SQL features for SELECT statements.

• 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"
  

• SELECT statements with 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
  

• 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%'
  

• 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 are supported too.

  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 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 with GROUP BY and HAVING clauses is supported.

  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 are supported.

  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, various SQL functions are supported.

  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.