CData Excel Add-In for Office 365 2019 - Online Help
Questions / Feedback?

Writing Parameterized Queries

CData Excel Add-In for Office 365 2019 - Build 19.0.7354

You can create dynamic spreadsheets by referencing cells in your SQL statement. The add-in executes the statement as a parameterized query. As you change the values in the sheet the add-in executes a new query.

Creating Searchable Spreadsheets

Use a parameterized SELECT WHERE query to create a spreadsheet that dynamically filters the data as you edit cells. The following filter criteria references the @InputSheet!A2 parameter -- cell A2 of the sheet InputSheet.

SELECT Id, location_displayName FROM Events WHERE Id = @InputSheet!A2

Defining Optional and Required Parameters

When you prefix a parameter with @@, empty values in the referenced cell do not affect the query; that is, any condition that refers to a cell with an empty value is evaluated as TRUE.

In the following query, location_displayName is an optional search parameter, but Id must be specified.

SELECT * FROM Events WHERE Id = @InputSheet!A2 AND location_displayName = @@InputSheet!A3

Writing Other Search Conditions

By default, the add-in reports a match if the Office 365 column contains the cell value. For example if the referenced cell has the value 'Jq74mCczmFXk1tC10GB', then the equivalent SQL predicate is

LIKE '%Jq74mCczmFXk1tC10GB%'

You can execute starts-with and ends-with searches by adding a '%' to the cell value:

InputSheet!A2 Cell Query Equivalent Predicate
'Jq74mCczmFXk1tC10GB' SELECT * FROM Events WHERE Id LIKE @InputSheet!A2 LIKE '%Jq74mCczmFXk1tC10GB%'
'%Jq74mCczmFXk1tC10GB' SELECT * FROM Events WHERE Id LIKE @InputSheet!A2 LIKE '%Jq74mCczmFXk1tC10GB'
'Jq74mCczmFXk1tC10GB%' SELECT * FROM Events WHERE Id LIKE @InputSheet!A2 LIKE 'Jq74mCczmFXk1tC10GB%'

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 19.0.7354.0