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

 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.