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.