SQL Query Examples

Content

Driver SQL Syntax

SELECT / INTO Syntax
SELECT from Temp Table Syntax
Multiple SQL Statments Syntax
Comments Syntax
UNION / UNIAN ALL Syntax
Stored Procedure Syntax
INSERT Syntax
UPDATE Syntax
DELETE Syntax
INSERT Bulk Syntax
UPDATE Bulk Syntax
DELETE Bulk Syntax

SQL Examples

1. INSERT
2. UPDATE
3. DELETE
4. UPSERT
5. Bulk INSERT (Read from MSSQL, ODBC) - SOURCE Clause
6. Bulk DELETE (Read from MSSQL, ODBC) - SOURCE Clause
7. Bulk UPDATE (Read from MSSQL, ODBC) - SOURCE Clause
8. Call generic API
9. Call generic API (file upload)
10. Custom Metadata / Override Mode
11. Using Connection Clause
12. Using Direct ConfigText in Connection Clause
13. Handling Large Dataset / Avoid OutOfMemory Error (Using Disk for Query Engine)
14. DateTime field Compare in WHERE / ORDER BY
15. WITH / CONNECTION Clause
16. Filter on Date, Group By, Order By, Limit Clause
17. Case Statement
18. SELECT INTO / Multiple Statements
19. UNION ALL / UNION Statement
20. Basic SQL queries

Driver SQL Syntax

On this page you will find various query examples for Api ODBC Driver / Data Gateway Connector. This driver offers highly flexible Client Size SQL Query engine which runs on client side to offer rich query language like a traditional RDBMS. Keep in mind that its not same as traditional database SQL so many features might not be supported also this engine is invoked on client side so there might be some performance issue if you have very large dataset. In below section you can see high level SQL Grammer / Syntax for writing SQL query for this driver.

SELECT Syntax / INTO

Below section shows use of SELECT statement. It can output data or store data into temporary table (must be named with prefix # (i.e. #temp_table) ).


SELECT 
 [DISTINCT] [* | [ expression [[AS] column_name_alias] [, ...] ]
 [INTO #temp_table_name]
 [FROM table_name]
 [WHERE condition [, ...] ]
 [GROUP BY expression [, ...] ]
 [HAVING condition [, ...] ]	
 [ORDER BY expression [ASC | DESC] [, ...] ]
 [LIMIT row_count] 
 [WITH (option_name=option_value] [, ...]) ] [;]
 
[CONNECTION (option_name=option_value] [, ...]) ] [;]

option_name:=IDENTIFIER
option_value:= NUMBER | STRING

Select using Temp Table

This driver supports storing data into temporary table and then using it later in the same script. You can issue multiple SQL statements in the same script seperated by semicolon (;). For output only last Non-SELECT INTO statement is used. When you query #temp_table you get support for INNER / OUTER JOIN statements.


 SELECT [DISTINCT] [* | [ expression [[AS] column_name_alias] [, ...] ] 
 FROM #temp_table_name
 [[INNER | OUTER | CROSS] JOIN #temp_table_name [alias] ON condition 
   [join_statement] 
   [join_statement] 
   ... 
 ]
 [WHERE condition [, ...] ]
 [GROUP BY expression [, ...] ]
 [HAVING condition [, ...] ]	
 [ORDER BY expression [ASC | DESC] [, ...] ]
 [LIMIT row_count]

Multiple SQL Statments

This driver supports executing multiple statements in a single command. You can issue multiple SQL statements in the same script seperated by semicolon (;). For output only last Non-SELECT INTO statement is used. When you query #temp_table you get support for INNER / OUTER JOIN statements.


select_into_temp; 
[select_into_temp;] 
[select_into_temp;]
...
select_from_temp_table;

Comment Syntax

ZappySys Driver supports single line comment and block comments anywhere in SQL


--some comment in single line 
|
/* some block comment */

UNION / UNION ALL Syntax

You can use UNION or UNION ALL key word to output multiple temp tables with similar structure in a single resultset. UNION ALL keyword doesnt apply DISTINCT operation to eliminate duplicate rows so its faster.


select_from_temp_table_1
   UNION | UNION ALL
select_from_temp_table_2
  [UNION | UNION ALL]
select_from_temp_table_3	
...
...

Stored Procedure Syntax

This driver supports writing Stored Procedure. This is useful when writing reusable parameterized SQL for ease of use. Rather than calling long SQL from application you can call short name with parameters to invoke same logic. Stored procedure support multi-statements using TEMP tables (i.e. SELECT ... INTO #mytemptable clause). To invoke Stored Proc use EXEC keyword before its name. See below Syntax.
CREATE PROCEDURE


CREATE PROCEDURE proc_name 
  [@parameter_name [= 'Some Default Value']  ] 
  [@parameter_name [= 'Some Default Value']  ] 
  ...
  ...
AS 
  sql-statement; 
 [sql-statement | sql-temp-table-usage] ;
 [sql-statement | sql-temp-table-usage] ;
 ...
 ...

Executing PROCEDURE

EXEC proc_name [value1] [,value2] ... [,valueN]

Deleting PROCEDURE

DROP PROCEDURE proc_name

INSERT Syntax


INSERT INTO table_name ( column_name_1 [, column_name_2] ... ) 
VALUES (value1 [, value2] ... )[;]
[WITH (option_name=option_value] [, ...]) ] [;]
[CONNECTION (option_name=option_value] [, ...]) ] [;]

INSERT Bulk Syntax


INSERT INTO table_name ( column_name_1 [, column_name_2] ... ) 
[WITH (option_name=option_value] [, ...]) ] [;]
[CONNECTION (option_name=option_value] [, ...]) ] [;]
[SOURCE ('MSSQL' | 'ODBC' | 'OLEDB'
     ,'driver_specific_connection_string'
     ,'driver_specific_sql_query'
] [;]

UPDATE Syntax


UPDATE table_name 
SET column_name_1 = some_value 
  [,column_name_2 = some_value]
  ....
[WITH (option_name=option_value] [, ...]) ] [;]
[CONNECTION (option_name=option_value] [, ...]) ] [;]

UPDATE Bulk Syntax


UPDATE table_name 
[WITH (option_name=option_value] [, ...]) ] [;]
[CONNECTION (option_name=option_value] [, ...]) ] [;]
[SOURCE ('MSSQL' | 'ODBC' | 'OLEDB'
     ,'driver_specific_connection_string'
     ,'driver_specific_sql_query'
] [;]

DELETE Syntax


DELETE FROM table_name [WITH (option_name=option_value] [, ...]) ] [;]
[CONNECTION (option_name=option_value] [, ...]) ] [;]

DELETE Bulk Syntax


DELETE FROM table_name 
[WITH (option_name=option_value] [, ...]) ] [;]
[CONNECTION (option_name=option_value] [, ...]) ] [;]
[SOURCE ('MSSQL' | 'ODBC' | 'OLEDB'
     ,'driver_specific_connection_string'
     ,'driver_specific_sql_query'
] [;]

SQL Examples

INSERT

This example shows how to call INSERT row action for a specified table. Optionally you can supply Parameters via WITH clause.

INSERT INTO MyTable(Col1,Col2) Values('something', 1234)

--When you use below clause then make sure to remove static column names / values from INSERT/UPDATE/UPSERT (i.e. SET C1=.., VALUES(..)) clause because it will be supplied by SOURCE SQL
--SOURCE( 'MSSQL'--OR 'ODBC'
--        , 'Data Source=localhost;Initial Catalog=MyDB;Integrated Security=true'
--        , 'select SrcCol1 as TgtCol1,SrcCol2 as TgtCol2,''USA'' [$$MyParam2] FROM dbo.SomeTable')

--WITH(Param1='something', Param2='something')

--CONNECTION( UserName='user1'
--        , Password='mypassword123'
--        , Provider='Custom'
--        , ConfigFile='c:\etl\myapi-connector.xml'
--        , ServiceUrl='https://myapi.com/v1'
--        , Parameters='[{Name:'MyConnParam1',Value:'something'},{Name:'MyConnParam2',Value:'something'}]'
--        , AuthName='Http')

UPDATE

This example shows how to call UPDATE on a table. Optionally you can supply Parameters via WITH clause.

UPDATE MyTable SET Col1='something', Col2=1234

--When you use below clause then make sure to remove static column names / values from INSERT/UPDATE/UPSERT (i.e. SET C1=.., VALUES(..)) clause because it will be supplied by SOURCE SQL
--SOURCE( 'MSSQL'--OR 'ODBC'
--        , 'Data Source=localhost;Initial Catalog=MyDB;Integrated Security=true'
--        , 'select SrcCol1 as TgtCol1,SrcCol2 as TgtCol2,''USA'' [$$MyParam2] FROM dbo.SomeTable')

--WITH(Param1='something', Param2='something')

--CONNECTION( UserName='user1'
--        , Password='mypassword123'
--        , Provider='Custom'
--        , ConfigFile='c:\etl\myapi-connector.xml'
--        , ServiceUrl='https://myapi.com/v1'
--        , Parameters='[{Name:'MyConnParam1',Value:'something'},{Name:'MyConnParam2',Value:'something'}]'
--        , AuthName='Http')

DELETE

This example shows how to call DELETE row action for a specified table. Optionally you can supply Parameters via WITH clause.

DELETE FROM MyTable

--When you use below clause then make sure to remove static column names / values from INSERT/UPDATE/UPSERT (i.e. SET C1=.., VALUES(..)) clause because it will be supplied by SOURCE SQL
--SOURCE( 'MSSQL'--OR 'ODBC'
--        , 'Data Source=localhost;Initial Catalog=MyDB;Integrated Security=true'
--        , 'select SrcCol1 as TgtCol1,SrcCol2 as TgtCol2,''USA'' [$$MyParam2] FROM dbo.SomeTable')

--WITH(Param1='something', Param2='something')

--CONNECTION( UserName='user1'
--        , Password='mypassword123'
--        , Provider='Custom'
--        , ConfigFile='c:\etl\myapi-connector.xml'
--        , ServiceUrl='https://myapi.com/v1'
--        , Parameters='[{Name:'MyConnParam1',Value:'something'},{Name:'MyConnParam2',Value:'something'}]'
--        , AuthName='Http')

UPSERT

This example shows how to call UPSERT action for a specified table. Optionally you can supply Parameters via WITH clause.

UPSERT INTO MyTable(Col1,Col2) Values('something', 1234)

--When you use below clause then make sure to remove static column names / values from INSERT/UPDATE/UPSERT (i.e. SET C1=.., VALUES(..)) clause because it will be supplied by SOURCE SQL
--SOURCE( 'MSSQL'--OR 'ODBC'
--        , 'Data Source=localhost;Initial Catalog=MyDB;Integrated Security=true'
--        , 'select SrcCol1 as TgtCol1,SrcCol2 as TgtCol2,''USA'' [$$MyParam2] FROM dbo.SomeTable')

--WITH(Param1='something', Param2='something')

--CONNECTION( UserName='user1'
--        , Password='mypassword123'
--        , Provider='Custom'
--        , ConfigFile='c:\etl\myapi-connector.xml'
--        , ServiceUrl='https://myapi.com/v1'
--        , Parameters='[{Name:'MyConnParam1',Value:'something'},{Name:'MyConnParam2',Value:'something'}]'
--        , AuthName='Http')

Bulk INSERT (Read from MSSQL, ODBC) - SOURCE Clause

This example shows how to use SOURCE clause to perform Bulk INSERT operation. In this example we are reading many rows from external system (e.g. MSSQL) and supplying to INSERT command. Make sure you external SQL outputs exact same column names expected by Target table used in Insert. Notice how we have aliased columns in External SQL to match with InputColumn (i.e. TargetCol1) and Parameter (i.e. $$MyParam2). Whenever possible supply parameters via WITH clause (i.e. same values for all rows) but if you have use case to supply different parameter value for each row then alias source column with $$ prefix (i.e. $$MyParam2 ). See help file to know exact name for parameter you like to map.
Syntax : SOURCE('MSSQL|OLEDB|ODBC', 'driver-connection-string', 'your-sql')

INSERT INTO MyTable
--Column list / Values omitted when you use SOURCE clause
SOURCE('MSSQL', 'Data Source=localhost;Initial Catalog=SalesDB;Integrated Security=true','select Id as [TargetId],''USA'' as [$$MyParam2] FROM dbo.SomeTable')

--For bulk input, map columns in External Query (Must use alias column name to match INSERT command Input Column names - see help file)
--If parameter value not same for all input rows then you can prefix some column with $$ to map as parameter (i.e. $$MyParam1)
--SOURCE('ODBC', 'Driver={ZappySys CSV Driver};DataPath=C:\AccountsToInsert.csv','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM $')
--SOURCE('ODBC', 'DSN=MyDSN','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')
--SOURCE('OLEDB', 'Provider=SQLNCLI11;Server=localhost,1433;Database=tempdb;Trusted_Connection=yes;','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')

WITH(MyParam1='something', MyParam2='something')

Bulk DELETE (Read from MSSQL, ODBC) - SOURCE Clause

This example shows how to use SOURCE clause to perform Bulk DELETE operation. In this example we are reading many rows from external system (e.g. MSSQL) and supplying to DELETE command. Make sure you external SQL outputs exact same column names expected by Target table used in Delete. Notice how we have aliased columns in External SQL to match with InputColumn (i.e. TargetCol1) and Parameter (i.e. $$MyParam2). Whenever possible supply parameters via WITH clause (i.e. same values for all rows) but if you have use case to supply different parameter value for each row then alias source column with $$ prefix (i.e. $$MyParam2 ). See help file to know exact name for parameter you like to map.
Syntax : SOURCE('MSSQL|OLEDB|ODBC', 'driver-connection-string', 'your-sql')

DELETE FROM MyTable
SOURCE('MSSQL', 'Data Source=localhost;Initial Catalog=SalesDB;Integrated Security=true','select Id as [TargetId],''USA'' as [$$MyParam2] FROM dbo.SomeTable')

--For bulk input, map columns in External Query (Must use alias column name to match DELETE command Input Column names - see help file)
--If parameter value not same for all input rows then you can prefix some column with $$ to map as parameter (i.e. $$MyParam1)
--SOURCE('ODBC', 'Driver={ZappySys CSV Driver};DataPath=C:\AccountsToDelete.csv','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM $')
--SOURCE('ODBC', 'DSN=MyDSN','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')
--SOURCE('OLEDB', 'Provider=SQLNCLI11;Server=localhost,1433;Database=tempdb;Trusted_Connection=yes;','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')

WITH(MyParam1='something', MyParam2='something')

Bulk UPDATE (Read from MSSQL, ODBC) - SOURCE Clause

This example shows how to use SOURCE clause to perform Bulk UPDATE operation. In this example we are reading many rows from external system (e.g. MSSQL) and supplying to UPDATE command. Make sure you external SQL outputs exact same column names expected by Target table used in Update. Notice how we have aliased columns in External SQL to match with InputColumn (i.e. TargetCol1) and Parameter (i.e. $$MyParam2). Whenever possible supply parameters via WITH clause (i.e. same values for all rows) but if you have use case to supply different parameter value for each row then alias source column with $$ prefix (i.e. $$MyParam2 ). See help file to know exact name for parameter you like to map.
Syntax : SOURCE('MSSQL|OLEDB|ODBC', 'driver-connection-string', 'your-sql')

UPDATE MyTable
--SET clause is omitted when you use SOURCE clause
SOURCE('MSSQL', 'Data Source=localhost;Initial Catalog=SalesDB;Integrated Security=true','select Id as [TargetId],''USA'' as [$$MyParam2] FROM dbo.SomeTable')

--For bulk input, map columns in External Query (Must use alias column name to match UPDATE command Input Column names - see help file)
--If parameter value not same for all input rows then you can prefix some column with $$ to map as parameter (i.e. $$MyParam1)
--SOURCE('ODBC', 'Driver={ZappySys CSV Driver};DataPath=C:\AccountsToUpdate.csv','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM $')
--SOURCE('ODBC', 'DSN=MyDSN','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')
--SOURCE('OLEDB', 'Provider=SQLNCLI11;Server=localhost,1433;Database=tempdb;Trusted_Connection=yes;','SELECT SrcCol1 as [TargetCol1],SrcCol2 as [TargetCol2],''USA'' as [$$MyParam2] FROM SomeTable')

WITH(MyParam1='something', MyParam2='something')

Call generic API

This example shows how to call any API URL for the connector

SELECT * FROM generic_request 
WITH (
    URL='/customers?para=123'
    --OR use full URL
    --URL='https://mycompany.com/api/customers?para=123'
  , RequestMethod='POST'
  , Filter='$.value[*]' --optional if you like to read from array inside document
  , Headers='Content-Type: application/json || x-header2: abcd'
  , Body='{para1: 1, para2: "ABC"}'

  --Meta data is optional but very useful for POST calls to avoid double requests
  --column names are attribute under specified filter. You can use underscore for nested attribute e.g.  info_address_zip: int (when JSON looks like this { info: { address: { zip: 1234, city: "aaa" } } } )
  --,Meta='col1:int;col2:string;col3:bool;col4:double;col5:datetime' --userdefined types
  --,Meta='col1;col2;col3;col4;col5' --default types
)

Call generic API (file upload)

This example shows how to call any API URL for the connector

SELECT * FROM generic_request 
WITH (
    URL='/files/upload?para=123'
    --OR use full URL
    --URL='https://mycompany.com/api/files/upload?para=123'
  , RequestMethod='POST'
  , Filter='$.value[*]' --optional if you like to read from array inside document
  , Headers='Content-Type: application/octet-stream || x-header2: abcd'
  , Body='@c:\data\myfile.zip'
/*
 Use below if File Upload needs additional metadata along with binary data - One Key/Value per line. File Path must start with @ symbol
  , Body='myfile=@c:\data\myfile.zip
myfilename=myfile.zip
field1=aaaa
field2=bbbb'
*/
  , IsMultiPart=1 

  --Meta data is optional but very useful for POST calls to avoid double requests
  --column names are attribute under specified filter. You can use underscore for nested attribute e.g.  info_address_zip: int (when JSON looks like this { info: { address: { zip: 1234, city: "aaa" } } } )
  --,Meta='col1:int;col2:string;col3:bool;col4:double;col5:datetime' --userdefined types
  --,Meta='col1;col2;col3;col4;col5' --default types
)

Custom Metadata / Override Mode

SELECT id,name,checknumber FROM MyTable
WITH
(
--//Custom Metadata (Direct Mode)<<<
--//Add all possible columns here and it will be part of metadata (this approach ignores any metadata comes from table itself). This approach usually helpful when Table has dynamic data (scan 300 rows to detect types) which may cause issue if zero records found.
META = 'id:int;checknumber:string(10);name:string(10);some_extra_column:datetime'

--//Custom Metadata with Override  (Direct Mode)<<<
--You can use @OverrideMode:1 in the column list to override meta for specific column(s) only and use all other columns from reported metadata for table - In below example we are changing default datatype/length for id and checknumber, all other columns are dynamic so dont have to supply in META clause
--META = '@OverrideMode:1;id:int;checknumber:string(10)'

--//Metadata from File (supply metadata stored in file)
--META = 'c:\mytable_meta.txt'
)

Using Connection Clause

This examples shows how to supply connection information via CONNECTION clause in the sql query. CONNECTION clause takes precedence over the default values configured for data source using UI

SELECT * FROM Customers
--WITH(Param1 = 'something', Param2 = 'something')
CONNECTION(

    , Provider = 'OData'
    , ServiceUrl = 'https://services.odata.org/V3/Northwind/Northwind.svc'
--, Provider = 'Custom'
--, ConfigFile = 'C:\connectors\odata-connector.xml'
--, AuthName = 'BasicAuth'
--, UserName = 'user1'
--, Password = 'pass1'
--, Parameters = '[{Name:"ConnPram1",Value:"some1"}, {Name:"ConnPram2",Value:"some2"}]'
)

Using Direct ConfigText in Connection Clause

This examples shows how to supply direct ConfigText (connector xml) in the optional CONNECTION clause of SQL query. CONNECTION clause takes precedence over the default values configured for data source using UI

SELECT * FROM Customers
--WITH(Param1='something', Param2='something')
CONNECTION(
--	,Provider='OData'
	,ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc'
	,Provider='Custom'
	,UseEmbeddedConfig=1
    ,ConfigText='<ApiConfig>
  <ServiceUrls>
    <ServiceUrl Url="https://services.odata.org/V3/Northwind/Northwind.svc" />
  </ServiceUrls>
  <EndPoints>
    <EndPoint Name="Customers" Url="/Customers?$format=json" Method="GET" Filter="$.value[*]" Body="" />
  </EndPoints>
</ApiConfig>'	
--	,ConfigFile='C:\connectors\odata-connector.xml'  --to use file mode remove UseEmbeddedConfig, ConfigText and set Provider='Custom'
--	,AuthName='BasicAuth'	
--  ,UserName='user1'
--  ,Password='pass1'
--	,Parameters='[{Name:"ConnPram1",Value:"some1"}, {Name:"ConnPram2",Value:"some2"}]'	
)

Handling Large Dataset / Avoid OutOfMemory Error (Using Disk for Query Engine)

Advanced Query can invokes ClientSide query Engine which means entire table is fetched in memory for processing causing OutOfMemory Exception (OOM). To avoid this error you can specify Disk based Temporary storage for query engine. Default is In memory storage.

SELECT * MyReallyLargeTable
WITH(
  TempStorageMode='Disk' --or 'Memory'
--OR
--TempStorageMode=1 --//Memory=0, Disk=1
)

DateTime field Compare in WHERE / ORDER BY

This example shows how to use date time field in WHERE or / ORDER BY. Date Time field needs special handling. You have to use DATETIME function else it will do string compare rather true date time compare. Foe example WHERE OrderDate<=DATETIME('1997-12-31 00:00:00') -- OR WHERE OrderDate<=DATE('1997-12-31'). You must use DATETIME either left side or right side. Also must use 'yyyy-MM-dd' or 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd HH:mm:ss.fff' format (where fff is milliseconds)

SELECT * FROM Orders 
WHERE OrderDate<=DATETIME('1997-12-31 00:00:00') -- OR use DATE('1997-12-31') DateTime column can be queried this way. You must wrap DATETIME function around it. Also must use 'yyyy-MM-dd' or 'yyyy-MM-dd HH:mm:ss'  or 'yyyy-MM-dd HH:mm:ss.fff' format (where fff is milliseconds)
ORDER BY DATETIME(OrderDate)
--WITH (MyParam1='something1', MyParam2='something2')
CONNECTION(Provider='OData', ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc');

WITH / CONNECTION Clause

This example shows how to use WITH clause along with Connection clause. Use WITH clause to supply request parameters (Click on [Query Builder] to build request correctly). You can use CONNECTION clause to override any connection level parameters.

SELECT * FROM Customers
LIMIT 3
WITH($filter='Country eq ''UK''')
CONNECTION(Provider='OData', ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc')

Filter on Date, Group By, Order By, Limit Clause

This example shows how to use WHERE clause and search by datetime, use GROUP BY / HAVING, ORDER BY and LIMIT clauses. You can use OData Connector with Northwind Service URL to run this example.

SELECT 
  Country AS Invoice_Country
, SUM(UnitPrice * Quantity) AS Invoice_Total
FROM Invoices 
WHERE Discount > 0
AND OrderDate<=DATETIME('1997-12-31 00:00:00') 
--or use OrderDate<=DATE('1997-12-31') 
--Valid formats are yyyy-MM-dd, yyyy-MM-dd HH:mm:ss or yyyy-MM-dd HH:mm:ss.fff (where fff is milliseconds)
GROUP BY Country 
HAVING SUM(UnitPrice * Quantity) > 1000
ORDER BY 
  Invoice_Total DESC
--,DATETIME(OrderDate) 
LIMIT 3
CONNECTION(Provider='OData', ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc');

Case Statement

This example shows how to write CASE statement to select / output different value based on multiple conditions.

SELECT 
  CustomerID
, CompanyName 
, Country
, Region
--Pattern#1 - Match by clause
, CASE 
   WHEN Country IN('UK','Germany','France') OR Region='BC' THEN 'EU' 
   WHEN Country IN('USA','Maxico','Canada') THEN 'NA' 
   WHEN Country IN('Brazil','Argentina','Venezuela') THEN 'SA'
   ELSE 'Unknown'
 END Pattern1

--Pattern#2 - Match by static value or simple expression
, CASE Country 
   WHEN 'U'+'K' THEN 'EU' 
   WHEN 'USA' THEN 'EU' 
   WHEN 'Brazil' THEN 'SA'
   WHEN 'Argentina' THEN 'SA'
   WHEN 'Venezuela' THEN 'SA' 
   ELSE 'Unknown'
 END Pattern2 

FROM Customers
CONNECTION(Provider='OData', ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc')

SELECT INTO / Multiple Statements

This example shows how to use SELECT INTO statement and use of multiple statements in the same batch. Output from only last SELECT is returned if you have multiple SELECT * FROM in the same batch. Each statement must be separated by semi-colon ( ; ). Also notice use or DateTime column. To compare date in WHERE clause or use Order BY clause you must use DATETIME function else it will be treated as string value (e.g. use WHERE o.OrderDate<=DATETIME('1997-12-31 23:12:59'). Also must use 'yyyy-MM-dd' or 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd HH:mm:ss.fff' format (where fff is milliseconds)

SELECT * INTO #tmpCustomers FROM Customers
--WITH (MyParam1='something1', MyParam2='something2')
CONNECTION(Provider='OData', ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc');

SELECT * INTO #tmpOrders FROM Orders
--WITH (MyParam1='something1', MyParam2='something2')
CONNECTION(Provider='OData', ServiceUrl='https://services.odata.org/V3/Northwind/Northwind.svc');

--JOIN 2 temporary tables
select c.CompanyName,Count(*) TotalOrders 
FROM #tmpCustomers c 
INNER JOIN #tmpOrders o ON o.CustomerID=c.CustomerID
WHERE o.OrderDate<=DATETIME('1997-12-31 23:12:59') --date format must be yyyy-MM-dd or yyyy-MM-dd HH:mm:ss[.fff]
GROUP BY  c.CompanyName;

UNION ALL / UNION Statement

This example shows how to write UNION or UNION ALL statement to combine results from multiple SQL queries. To remove duplicate rows from result use UNION rather than UNION ALL.

SELECT * into #tmpCustomersUK FROM Customers Where Country='UK'
--WITH (MyParam1='something1', MyParam2='something2');

SELECT * into #tmpCustomersUSA FROM Customers WHERE Country='USA'
--WITH (MyParam1='something1', MyParam2='something2');

select * from #tmpCustomersUK
UNION ALL
select * from #tmpCustomersUSA;

Basic SQL queries

Basic query examples.


--Whenever Possible try to write Simple Query. When you use WHERE / GROUP BY / ORDER BY Expressions. Driver may invoke Client side processing if server side doesnt support that construct.
--For comment use line comment with two dash (e.g. --my comment) or use block comment for multi line comment (e.g. /*my comment*/ )
--Any query must contain at least two required parts
--data source (or table) names and a list of column names
--column names come first (preceded by SELECT keyword)
--table names follow (preceded by FROM keyword)

--Here is the query that returns all rows with all columns (* stands for all columns) from 'Accounts' table
SELECT * FROM Accounts

--Use quoted identifiers when you have space or other character in names (e.g. Column or Table name contains space)
SELECT ID, "Account Name" FROM "Closed Accounts"
SELECT ID, [Account Name] FROM [Closed Accounts]

--returns all rows from 'Accounts' table, each row will have Id and Amount columns
SELECT Id, Amount FROM Accounts

--returns only those rows of 'Accounts' table that meet next condition: Amount is greater or equal to 1000.0
SELECT Id, Amount FROM Accounts WHERE Amount >= 1000.0

--returns only those rows of 'Accounts' table that meet next condition: Amount is greater than 1000 or DepartamentID is equal to 123 
SELECT Id, Amount FROM Accounts WHERE (Amount > 1000.0 OR DepartamentID = 123)

--returns only those rows of 'Accounts' table that meet next condition: DepartamentID is equal to 1, 101 or 202 
SELECT Id, Amount FROM Accounts WHERE DepartamentID IN (1, 101, 202)

--returns only those rows of 'Accounts' table that meet next condition: DepartamentID is equal to 321 and Amount is less than 100 
SELECT Id, Amount FROM Accounts WHERE DepartamentID = 321 AND Amount < 100

--returns only those rows of 'Persons' table that meet next condition: Name column value starts with 'John'
SELECT * FROM Person WHERE Name LIKE 'John%'

--case statement
SELECT 
 FirstName,
 LastName,
 CASE Country 
  WHEN 'US' THEN 'NA' 
  WHEN 'Canada' THEN 'NA'
  WHEN 'India' THEN 'Asia'
  WHEN 'China' THEN 'Asia'
  ELSE 'Unknown' 
 END ColRegion 
 FROM Person