SQL Query Examples
PreviousNext

On this page you will find various query examples for Csv ODBC Driver.

SQL Syntax


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

Query CSV API Service

This example shows how to query CSV API service. In order to see table list you must populate required connection parameters on the parameter grid
SELECT * FROM $
WITH(
     SRC='https://zappysys.com/downloads/files/test/invoices.csv' 
	,ColumnDelimiter=','
	,HasColumnHeaderRow='True'
	--,DateFormatString='yyyy-MM-dd HH:mm:ss'
	--,FileCompressionType='GZip' --or-- Zip --or-- None	
	--,SkipRows='5'
	--,SkipHeaderCommentRows='5'
)

Query Single File

This example shows how to query single CSV file (For multiple file use wildcard (i.e. *.csv)). This example selects root level hierarchy (i.e $ as table name). Use table selector from toolbar to exact data from different hierarchy (i.e table). In order to see table list you must populate File/URL and other required connection parameters on the parameter grid.

SELECT * FROM $ WITH (SRC='C:\Data\cust-1.csv')

Query Multiple Files

This example shows how to query multiple CSV files using wildcard (i.e. *.csv). This example selects root level hierarchy (i.e $ as table name). Use table selector from toolbar to exact data from different hierarchy (i.e table).
SELECT * FROM $ 
WITH (
    SRC='C:\Data\cust*-?.csv'
    --,RECURSIVE='True' --Include files from sub folder
)

Query direct CSV string (embedded inside query)

This example shows how to query direct CSV string embedded inside query. This is useful for testing purpose. In real world this may not be the case because data may be coming from File or URL (API call). This example selects root level hierarchy (i.e $ as table name). Use table selector from toolbar to exact data from different hierarchy (i.e table). In order to see table list you must populate File/URL and other required connection parameters on the parameter grid.
SELECT * FROM $ 
WITH 
(DATA='Name|Age|Income|Hiredate
Bob|55|5000.00|2018-01-01
Sam|51|2000.00|2016-02-01
John|45|3000.00|2014-07-01
'
,ColumnDelimiter='|'
)

Query API using Basic Authentication (UserId/Password)

This example shows how to query CSV API service which requires basic authentication. In order to see table list you must populate required connection parameters on the parameter grid

--Credentials via Query
SELECT * FROM $ WITH (SRC='https://zappysys.com/downloads/files/test/invoices.csv'
    ,DataConnectionType='HTTP'
    ,CredentialType='Basic'
    ,UserName='user1'
    ,Password='pass123')

--Credentials inside connection string [Use Data source setup grid >> Select Connection = HTTP (Click Configure) >> Set Credential type=Basic, UserId=user1, Password=pass123]
--SELECT * FROM $ WITH (SRC='https://zappysys.com/downloads/files/test/invoices.csv')

Query API using NTLM Authentication (Windows Auth)

This example shows how to query CSV API service which requires windows authentication (NTLM).

--Credentials via Query
SELECT * FROM $ 
WITH (SRC='http://yourhost/some-service',DataConnectionType='HTTP',CredentialType='Ntlm')

--Credentials inside connection string [Use Data source setup grid >> Authentication type=Ntlm]
--SELECT * FROM $ WITH (SRC='http://yourhost/some-service')

Query Custom API using OAuth2

This example shows how to query CSV API service which requires OAuth2 authentication.

--Credentials via Query
SELECT * FROM $ WITH (
    SRC='http://yourhost/api/some-service'
    ,DataConnectionType='OAuth'
    ,UseCustomApp='True'
    ,OAuthVersion='OAuth2'
    ,AuthUrl='https://yourhost/api/authorize'
    ,TokenUrl='https://yourhost/api/token'
    ,ClientId='xxxxxxxxxxx'
    ,ClientSecret='xxxxxxxxxxxxx'
    ,AccessToken='xxxxxxxxxxx'
    ,RefreshToken='xxxxxxxxxxx'
)

--Credentials inside connection string [Use Data source setup grid >> Authentication type=OAuth >> Click OAuth Button to configure connection]
SELECT * FROM $ WITH (SRC='http://yourhost/api/some-service')

Query Google API using OAuth2

This example shows how to query Google API using OAuth2 authentication. For demo its using Google Analytics API but you can apply same concept for other OAuth2 compatible Google API.

--Credentials via Query
--Check this link to learn more about how to generate ClientId / ClientSecret >> https://zappysys.com/blog/register-google-oauth-application-get-clientid-clientsecret/
--To get Access Token and Refresh Token >> Goto properties grid >> Change Authentication type=OAuth >> Click OAuth Button to configure connection >> Select Provider='Google' >> Use Custom App >> Select Scope and click Generate Token >> Save tokens to file

SELECT * FROM $ WITH
(
 SRC='https://www.googleapis.com/analytics/v3/metadata/ga/columns'
,UseCustomApp='True'
,DataConnectionType='OAuth'
,ServiceProvider='Google'
,ClientId='3855xxxxxx-xxxxxxxxxxxxxxxxx.apps.googleusercontent.com'
,ClientSecret='qF7axxxxxxxxxxxWfs'
,AccessToken='ya29.GlsxxxxxxxxxIam'
,RefreshToken='1/pFOxxxxxxxxxxgU'
)


--Credentials inside connection string [Use Data source setup grid >> Authentication type=OAuth >> Click OAuth Button to configure connection]
SELECT * FROM $ WITH (SRC='https://www.googleapis.com/analytics/v3/metadata/ga/columns')

Query REST API (Dynamic Token Method)

This example shows how to call REST API Service using Dynamic Token approach. Learn how to authenticate using userid / password to obtain temporary token which can be passed to call API. Also learn how to pass Body, Headers using HTTP POST. For more information about Dynamic Token visit https://zappysys.com/links/?id=10091
SELECT * FROM $
WITH(
	 Src='https://zappysys.com/downloads/files/test/rest-getdata.aspx?format=csv'
	--,RequestMethod='POST'
	--,RequestData='sessionid=[$token$]"'
	,Header='Content-Type: text/plain || Accept: */* || Cache-Control: no-cache'	
	,DataConnectionType='HTTP'
	,CredentialType='TokenDynamic'
	,UserName='MyUser001'
    ,Password='P@$$w0rdAAc12'
	,AuthScheme='{none}'
	,TokenUrl='https://zappysys.com/downloads/files/test/rest-login.aspx'
	,TokenRequestData='{ user:"[$userid$]", password:"[$password$]" }'
	,TokenRequestMethod='POST'
	,TokenResponseContentFilter='$.sessionid'
	,TokenRequestHeaders='Content-Type:application/json | Accept:*/* | Cache-Control:no-cache'
	,TokenResponseContentType='Json'

)

API Pagination - Paginate based on URL Parameter (Loop until certain StatusCode)

This example shows how to paginate API calls where you need to pass page number via URL. Driver keeps incrementing page number and calls next URL until last page detected (401 error). There are few ways to indicate last page (e.g. By status code, By row count, By response size). If you don't specify end detection then it will use default (i.e. No records found).
SELECT * FROM $
WITH (
SRC='https://zappysys.com/downloads/files/test/page-csv.aspx?page=1&mode=DetectBasedOnResponseStatusCode'
,PagingMode='ByUrlParameter'
,PagingByUrlAttributeName='page'
,PagingByUrlEndStrategy='DetectBasedOnResponseStatusCode'
,PagingByUrlCheckResponseStatusCode=401
,IncrementBy=1
)

API Pagination - Paginate based on URL Path (Loop until no record)

This example shows how to paginate API calls where you need to pass page number via URL Path. Driver keeps incrementing page number and calls next URL until last page is detected. There are few ways to indicate last page (e.g. By status code, By row count, By response size). If you don't specify end detection then it will use default (i.e. No records found).
SELECT * FROM $
WITH (
SRC='https://zappysys.com/downloads/files/test/cust-<%page%>.csv'
,PagingMode='ByUrlPath'
,PagingByUrlAttributeName='<%page%>'
,PagingByUrlEndStrategy='DetectBasedOnRecordCount'
,IncrementBy=1
)

URL JOIN Pattern - Pass parent URL data to Child URL

This example shows how to perform URL JOIN. Using this pattern you can pass parent URL data to child API URL in one query. You can pass parent data in child URL or Body (for POST). Placeholder syntax is [$parent_alias.parent_col_name$]. Each level must have ALIAS attribute. For root level you dont have to Prefix JOINxxxxxxx but for any other level you must Prefix JOINxxxxx before each attribute name. For more information on API JOIN Pattern check https://zappysys.com/links/?id=10092
SELECT 
    c.custid,c.name,c.city
    ,o.orderid,o.orderdate,o.total
    /* ,i.itemid,i.qty,i.shipped */
FROM $
WITH(
    --1st level (root)--
	 src='https://zappysys.com/downloads/files/test/join/customers.csv'
    ,alias='c'
    ,WaitTimeMs='500'
    
    --2nd level--
    --You can pass parent column to child URL using placeholder [$parent_alias.parent_col_name$] syntax (e.g. [$c.custid$] )
    ,join1_src='https://zappysys.com/downloads/files/test/join/c[$c.custid$]/orders.csv'
    ,join1_alias='o'
    ,join1_WaitTimeMs='500'

    /*
    --3rd level-- 
    --You can pass parent column to child URL using placeholder [$parent_alias.parent_col_name$] syntax (e.g. [$c.custid$] and [$o.orderid$])
    ,join2_src='https://zappysys.com/downloads/files/test/join/c[$c.custid$]/o[$o.orderid$]/items.csv'
    ,join2_alias='i'
    ,join2_WaitTimeMs='500'

    --maximum 7 levels deep you can go. Any attributes allowed in WITH clause can be prefixed by by joinN_xxx
    -- join3_xxxxx, join4_xxxxxx .......... join7_xxxxx
    */
) 

URL JOIN Pattern - Download files to local disk

This example shows how to download files to local disk. It first gets records by calling first URL and then pass it to seconds URL. For more information on API JOIN Pattern check https://zappysys.com/links/?id=10092
select a.url,b.data,file_write_binary(base64_to_bytes(b.data) ,'c:\temp\' || name  ) as bytes_written 
from $
WITH ( 
  SRC='https://zappysys.com/downloads/files/test/imagelist.csv',
  ALIAS='a',
  
  JOIN1_SRC='[$a.url$]',
  JOIN1_ALIAS='b',
  JOIN1_SaveContentAsBinary='true'  
) 

URL JOIN Pattern - Read unstructured data and pass to API call

This example shows how to query unstructured data and parse using Regex, XPath or CSVPath)

Examples of XPaths: https://zappysys.com/blog/ssis-extract-single-xml-node-using-xpath-soap-response/
Examples of CsvPath: https://zappysys.com/blog/csvpath-examples-expression-cheetsheet/
Examples of Regular Expression: https://zappysys.com/blog/using-regular-expressions-in-ssis/

Also try following Query Examples to extract Raw data.

SELECT * FROM $
WITH(
Src='http://google.com'
,EnableRawOutputModeSingleRow='True'
)

SELECT * FROM $
WITH(
Src='http://google.com'
,EnableRawOutputModeSingleRow='True'
,RawOutputFilterExpr='([^<]*)<\/title>{{0,1}}||content="([^"]*)"{{0,1}}' --can be Regex, CsvPath or XPath. Use double pipe to split multiple columns<br /> ,RawOutputDataRowTemplate=@'{title:"[$1]", content:"[$2]" }' --must be Csv template<br /> ,RawOutputExtractMode='Regex' --can be Regex, Xml, Csv or None. -- Comment this and RawOutputFilterExpr, RawOutputDataRowTemplate to extract Raw data without column parsing (i.e. returns raw text of URL/Path)<br />)<br /><br />SELECT * FROM $<br />WITH(<br /> Src='http://httpbin.org/get'<br /> ,EnableRawOutputModeSingleRow='True'<br /> ,RawOutputFilterExpr='$.orgin||$.url' --can be Regex, CsvPath or XPath. Use double pipe to split multiple columns<br /> --,RawOutputDataRowTemplate=@'{title:"[$1]", content:"[$2]" }' --must be Csv template<br /> ,RawOutputExtractMode='Csv' --can be Regex, Xml, Csv or None. -- Comment this and RawOutputFilterExpr, RawOutputDataRowTemplate to extract Raw data without column parsing (i.e. returns raw text of URL/Path)<br />)<br /><br /></span> <pre class="codeblock"><code class="sql">SELECT c.title,o.url,o.data FROM $ WITH( Src='http://google.com' ,EnableRawOutputModeSingleRow='True' ,RawOutputFilterExpr='<title>([^<]*)<\/title>{{0,1}}||content="([^"]*)"{{0,1}}' --can be Regex, CsvPath or XPath. Use double pipe to split multiple columns ,RawOutputDataRowTemplate=@'{title:"[$1]", content:"[$2]" }' --must be Csv template. Comment this line to generate default columns e.g. col1, col2...If you comment this then use c.col1 and c.col2 instread of c.title and c.content in Select and join1_xxxxx attributes ,RawOutputExtractMode='Regex' --can be Regex, Xml, Csv or None. -- Comment this and RawOutputFilterExpr, RawOutputDataRowTemplate to extract Raw data without column parsing (i.e. returns raw text of URL/Path) ,Alias='c' ,join1_RequestMethod='POST' ,join1_RequestData='url,data http://abc.com,Row1-[$c.content$] http://xyz.com,Row2-[$c.content$]' ,join1_src='https://zappysys.com/downloads/files/test/rest-getdata.aspx?id=[$c.title$]&postsame=1' ,join1_RequestContentTypeCode = 'TextPlain' ,join1_alias='o' ,join1_EnableRawOutputModeSingleRow='False' )</code></pre> <br /> <h3>API POST (data from embedded string)</h3> <span>This example shows how to POST data to API service where data (i.e. request body) is coming from embedded string.</span> <pre class="codeblock"><code class="sql">SELECT * FROM $ limit 3 WITH (METHOD='POST', HEADER='Content-Type: text/plain || x-hdr1: AAA' ,SRC='http://httpbin.org/post' ,BODY=' { id:1, notes:"Line1\r\nLine2" }' ) </code></pre> <br /> <h3>API POST - Send/Upload file (data from local file)</h3> <span>This example shows how to POST data to API service where data (i.e. request body) is coming from local file.</span> <pre class="codeblock"><code class="sql">SELECT * FROM $ limit 3 WITH (METHOD='POST', HEADER='Content-Type: text/plain || x-hdr1: AAA' ,SRC='http://httpbin.org/post' ,BODY='@c:\files\dump.csv' ,IsMultiPart='True' ) </code></pre> <br /> <h3>Call stored proc (Ad-Hoc HTTP request).</h3> <span>This example shows how to fetch data from any http URL via stored procedure call. Using this method you can fetch any data from HTML pages not necessarily CSV.</span> <pre class="codeblock"><code class="sql">exec webRequest 'http://httpbin.org/post', 'POST', '*BODY*', 'x-hdr1: AA||x-hdr2: BB' --You can also arrange parameters in any order using named parameters and optional parameters --exec webRequest @verb='POST', @uri='https://httpbin.org/post', @body='*BODY*', @headers='Insecure-Requests: 0||User-Data: httpbin.org' --exec webRequest @verb='GET', @uri='https://httpbin.org/get' </code></pre> <br /> <h3>Functions - String Manipulation</h3> <span>This example shows how to use string functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation.</span> <pre class="codeblock"><code class="sql">SELECT substr('ZappySys', 1, 5) , printf('%s %s %.2f', 'v1', CompanyName, CompanyId) , ('Str1' || CompanyName || 'Str2') as ConcatExample , upper('ZappySys') , lower('ZappySys') , replace('ZappySys', 'Sys', 'XYZ') , instr('ZappySys', 'Sys') , trim(' Zappy Sys ') trim1 , trim('@@@ZappySys@@@', '@') trim2 , rtrim(' ZappySys ') , ltrim(' ZappySys ') , length( CompanyName ) FROM $ WITH ( DATA='CompanyId,CompanyName 1000,ZappySys 1001,Microsoft 1002,Amazon ')</code></pre> <br /> <h3>Functions - Control flow</h3> <span>This example shows how to use math functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation.</span> <pre class="codeblock"><code class="sql">SELECT COALESCE(10,20) coalesce1 --returns first non-null arg ,COALESCE(null,20) coalesce2 --returns first non-null arg ,COALESCE(null,20,30,40) coalesce3 --returns first non-null arg ,IFNULL(null,20) --returns first non-null arg. Same function as COALESCE but simple (just two arguments allowed) ,NULLIF(20,20) nullif1 --returns null if both argument same ,NULLIF(10,20) nullif2 --returns null if both argument same FROM $ WITH ( DATA='CompanyId,CompanyName 1000,ZappySys 1001,Microsoft 1002,Amazon ')</code></pre> <br /> <h3>Functions - Date/Time</h3> <span>This example shows how to use date/time functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation.<br /><br />CSV driver supports following 5 datetime functions. The date and time functions use a subset of IS0-8601 date and time formats. The date() function returns the date in this format: YYYY-MM-DD. The time() function returns the time as HH:MM:SS. The datetime() function returns YYYY-MM-DD HH:MM:SS.<br /><br />date(timestring, modifier, modifier, ...)<br />time(timestring, modifier, modifier, ...)<br />datetime(timestring, modifier, modifier, ...)<br />julianday(timestring, modifier, modifier, ...)<br />strftime(format, timestring, modifier, modifier, ...)<br /><br />=======================================<br />time string (input data)<br />=======================================<br />A time string (input) can be in any of the following formats. You can hard code or supply from column.<br /><br />YYYY-MM-DD<br />YYYY-MM-DD HH:MM<br />YYYY-MM-DD HH:MM:SS<br />YYYY-MM-DD HH:MM:SS.SSS<br />YYYY-MM-DDTHH:MM<br />YYYY-MM-DDTHH:MM:SS<br />YYYY-MM-DDTHH:MM:SS.SSS<br />HH:MM<br />HH:MM:SS<br />HH:MM:SS.SSS<br />now<br />DDDDDDDDDD<br /><br />=======================================<br />format specifier for strftime function<br />=======================================<br />Use below format specifiers to output date in desired format. For example to output date in MM-DD-YYYY use below select query <br /><br />select STRFTIME('%Y-%m-%d','now') formatted_date_time from mytable<br />select STRFTIME('%Y-%m-%d',[my column]) formatted_date_time from mytable<br /><br />%d day of month: 00<br />%f fractional seconds: SS.SSS<br />%H hour: 00-24<br />%j day of year: 001-366<br />%J Julian day number<br />%m month: 01-12<br />%M minute: 00-59<br />%s seconds since 1970-01-01<br />%S seconds: 00-59<br />%w day of week 0-6 with Sunday==0<br />%W week of year: 00-53<br />%Y year: 0000-9999<br />%% %<br /><br />=================<br />Modifiers<br />=================<br />The time string (input) can be followed by zero or more modifiers that alter date and/or time. Each modifier is a transformation that is applied to the time value to its left. Modifiers are applied from left to right; order is important. The available modifiers are as follows.<br /><br />For example to add 5 days in current date you call it as below<br /><br />select DATE('now', '+5 day') from mytable<br /><br />NNN days<br />NNN hours<br />NNN minutes<br />NNN.NNNN seconds<br />NNN months<br />NNN years<br />start of month<br />start of year<br />start of day<br />weekday N<br />unixepoch<br />localtime<br />utc<br /><br />The first six modifiers (1 through 6) simply add the specified amount of time to the date and time specified by the preceding timestring and modifiers. The 's' character at the end of the modifier names is optional. Note that '±NNN months' works by rendering the original date into the YYYY-MM-DD format, adding the ±NNN to the MM month value, then normalizing the result. Thus, for example, the data 2001-03-31 modified by '+1 month' initially yields 2001-04-31, but April only has 30 days so the date is normalized to 2001-05-01. A similar effect occurs when the original date is February 29 of a leapyear and the modifier is ±N years where N is not a multiple of four.<br /><br />The 'start of' modifiers (7 through 9) shift the date backwards to the beginning of the current month, year or day.<br /><br />The 'weekday' modifier advances the date forward to the next date where the weekday number is N. Sunday is 0, Monday is 1, and so forth.<br /></span> <pre class="codeblock"><code class="sql">SELECT DATE('now') date_now ,STRFTIME('%Y-%m-%dT%H:%M:%S.%f','now') formatted_Date_time ,DATETIME('now') datetime_now ,DATE('now','localtime') datetime_now_local ,TIME('now') time_now ,JULIANDAY('now') ,DATE('now', 'start of month', '+1 month' , '-1 day' ) dt_modify1 ,DATE('now', '+5 day') dt_modify2 FROM $ WITH ( DATA='CompanyId,CompanyName 1000,ZappySys 1001,Microsoft 1002,Amazon ')</code></pre> <br /> <h3>Functions - Math</h3> <span>This example shows how to use math functions. When you use function your entire dataset is first cached locally and then transformation is applied so you may notice some speed degradation.</span> <pre class="codeblock"><code class="sql">SELECT abs(-500) ,random() ,round(1000.236, 2) FROM $WITH ( DATA='CompanyId,CompanyName 1000,ZappySys 1001,Microsoft 1002,Amazon ')</code></pre> <br /> <h3>Functions - Regular Expression</h3> <span>This example shows how to regular expression functions.</span> <pre class="codeblock"><code class="sql">SELECT regex_ismatch(SSN, '\d{3}-\d{3}-\d{4}') IsValidSSN, --check pattern found regex_replace(SSN, '\d','*') MaskedSSN, --search/replace pattern regex_search(SSN, '\d{4}') SSNLast4Digits, --extract substring using pattern search regex_search(Email, '(?<name>\w+)@(?<domain>\w+.com)',0, 2) DomainOf1stEmailByIndex, --extract 1st email (zero based index) and get domain name (by group name). For 2nd argument you can use numeric index or named argument. Both arguments are zero based index. regex_search(Email, '(?<name>\w+)@(?<domain>\w+.com)',2, 2) DomainOf3rdEmailByIndex, --extract 3rd email (zero based index) and get 2nd part of match (i.e. domain name). Both arguments are zero based index. regex_search('a@x.com | b@y.com | c@z.com', '(?<name>\w+)@(?<domain>\w+.com)','-0', 2) DomainOfLastEmail, --extract last email (use negative sign in quote - zero based index) and get 2nd part of match (i.e. domain name). Both arguments are zero based index. regex_search( 'a@x.com | b@y.com | c@z.com' , '(?<name>\w+)@(?<domain>\w+.com)' , '*' --occurrence match index ( * , N or -N ) negative number will do reverse find (e.g. to get last match use -0 , to get second last use -1) , 2 --group index from match (default is full match or -1) --, 0 -- ignore case for pattern match (0=strict match, 1=ignore case, default is 0) --, 0 -- throw error if not matched 0=don't throw error, 1=throw error --, '#' -- separator for multi match ) GetAllDomains --extract domains from all emails and show as email (zero based index) and get 2nd part of match (i.e. domain name). Both arguments are zero based index. FROM $ WITH (DATA= 'SSN,Email 111-222-4444,a@x.com | b@y.com | c@z.com 111-X2Z-AB44,a@x.com | b@y.com | c@z.com ')</code></pre> <br /> <h3>Query API with Status Check Loop (Wait until value found)</h3> <span>This example shows how to use status check feature to keep waiting until desired value is not found in response. This pattern is very useful for Job style API where you have to wait until API returns Success or Failure status of Job status. You can adjust iteration wait time, max wait time for timeout and regex pattern to search for Success or Failure. If Success value found it returns data. If Failure value found or timeout occurs it will throw error. <br /><br />Examples of XPaths: https://zappysys.com/blog/ssis-extract-single-xml-node-using-xpath-soap-response/<br />Examples of CsvPath: https://zappysys.com/blog/csvpath-examples-expression-cheetsheet/<br />Examples of Regular Expression: https://zappysys.com/blog/using-regular-expressions-in-ssis/<br /><br /></span> <pre class="codeblock"><code class="sql">SELECT * FROM $ WITH ( SRC='https://zappysys.com/downloads/files/test/cust-1.csv', EnableStatusCheck='True', StatucCheckMaxWaitSeconds=7, StatucCheckIterationWaitSeconds=3, --StatusCheckForFailedValue = 'True', --StatusFailedValue = 'Failed|Cancelled', --StatusFieldFilterExpr='-START-(.*)-END-', --Use XPath, JSonPath or Regex to narrow down to single property / content under which you like to search --StatusFieldFilterType='Regex', --or use Csv, Xml, Regex, None StatusSuccessValue='C1' --Wait until C1 or C2 or C3 found in response (Use Regular expression syntax) ) /* --- See real world example of status check pattern here (Use of XML Driver)... https://zappysys.com/blog/import-bing-ads-data-sql-server-performance-reports/ SELECT c.* FROM $ WITH( --/////////////////////////////////// --Step-1: submit report request for XML format --/////////////////////////////////// alias='a' ,Src='https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc' ,Filter='$.s:Envelope.s:Body.SubmitGenerateReportResponse' ,RequestData='@C:\Requests\BingAds\sql_body1.xml' --Get Body from File for clean Look (Read blog post above to see actual body) ,IsMultiPart='True' ,RequestContentTypeCode='TextXml' ,Header='SOAPAction: "SubmitGenerateReport"' ,RequestMethod = 'POST' ,EnableBodyPlaceholderForMultiPart = 'True' ,Meta = '[{ "Name": "ReportRequestId","Type": "Int64"}]' --/////////////////////////////////// --///Step-2: keep checking status until report is ready - returns final url when done --/////////////////////////////////// ,join1_alias = 'b' ,join1_Filter = '$.s:Envelope.s:Body.PollGenerateReportResponse.ReportRequestStatus' ,join1_RequestData = '@C:\Requests\BingAds\sql_body2.xml' ,join1_IsMultiPart = 'True' ,join1_Src = 'https://reporting.api.bingads.microsoft.com/Api/Advertiser/Reporting/v13/ReportingService.svc' ,join1_RequestContentTypeCode = 'TextXml' ,join1_Header = 'SOAPAction: "PollGenerateReport"' ,join1_RequestMethod = 'POST' ,join1_EnableBodyPlaceholderForMultiPart = 'True' ,join1_EnableStatusCheck = 'True', ,join1_StatucCheckMaxWaitSeconds = 300, --wait max 5 mins ? ,join1_StatucCheckIterationWaitSeconds = 5, --check every 5 sec ? ,join1_StatusSuccessValue = 'Success', --look for success word in response --, join1_Meta = '[{ "Name": "ReportDownloadUrl","Type": "String",Length: "500"},{ "Name": "Status","Type": "String",Length: "20"}]' --/////////////////////////////////// --Step-3: Download report(Zip file) and Parse rows --/////////////////////////////////// ,join2_alias = 'c' ,join2_DataConnectionType = 'Default' ,join2_Src = '[$b.ReportDownloadUrl$]' ,join2_Filter = '$.Report.Table.Row[*]' ,join2_ElementsToTreatAsArray = 'Row' ,join2_RequestMethod = 'GET' ,join2_IsMultiPart = 'False' ,join2_FileCompressionType = 'Zip' ) */ </code></pre> <br /> <h3>Language - Group By / Limit / Order By</h3> <span>This example shows how to use GROUP BY / ORDER BY and LIMIT clauses.</span> <pre class="codeblock"><code class="sql">SELECT Country AS Invoice_Country , SUM(UnitPrice * Quantity) AS Invoice_Total FROM $ WHERE Discount > 0 GROUP BY Country HAVING SUM(UnitPrice * Quantity) > 1000 ORDER BY Invoice_Total DESC LIMIT 3 WITH (SRC='https://zappysys.com/downloads/files/test/invoices.csv') </code></pre> <br /> <h3>Language - Case Statement</h3> <span>This example shows how to write CASE statement to select / output different value based on multiple conditions.</span> <pre class="codeblock"><code class="sql">SELECT Name , CASE Age WHEN 55 THEN 55+1 WHEN 51 THEN 51+1 ELSE 0 END ThisIsCaseColumn FROM $ WITH (DATA='Name|Age|Income|Hiredate Bob|55|5000.00|2018-01-01 Sam|51|2000.00|2016-02-01 John|45|3000.00|2014-07-01 ' ,ColumnDelimiter='|' )</code></pre> <br /> <h3>Language - Basic SQL queries</h3> <span>Basic query examples.</span> <pre class="codeblock"><code class="sql"> --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. --Line that starts with two dashes is a comment and wont be executed --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 --query with compound column and table names SELECT "Account ID" FROM "Closed Accounts" SELECT [Account ID] 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 </code></pre> <br /> <!--{{FOOTER-CONTENT}}--> </p> <hr> <font face="Arial" color="#808080" size="1">Copyrights reserved. ZappySys LLC.</font></div> <script type="text/javascript"> var $zoho= $zoho || {salesiq:{values:{},ready:function(){}}};var d=document;s=d.createElement("script");s.type="text/javascript"; s.defer=true;s.src="https://salesiq.zoho.com/zappysys/float.ls?embedname=zappysys"; t=d.getElementsByTagName("script")[0];t.parentNode.insertBefore(s,t); $zoho.salesiq.ready=function(embedinfo){$zoho.salesiq.floatbutton.visible("hide");} </script> <script type='text/javascript' src='https://crm.zoho.com/crm/javascript/zcga.js'></script> </body> </html>