1 Introduction

This manual describes how to create query’s using the CCP QueryBuilder, and how to make exports using those query’s.

Queries can bu used for defining exports.

2 QueryBuilder

QueryBuilder is a tool within CCP that allows you to create query’s.

Within this tool you can use various CCP commands and variables that allows you to create project independent query’s.

The queries are evaluated ‘Just in time’. So, all the latest values will be used to execute the query.

You can find QueryBuilder in “Exports->Query Overview” in the menu. A screen like below will appear.

From this screen you can:

Edit a query
Test a query
Delete a query
Create a new query

It’s also possible to search for a query. When you enter a part of the query name in the “Filter by name” textbox and then press Apply, then only the query’s containing that text in the name are displayed.

2.1 Creating a new query

To create a new query, click the “New query” button in the “Query overview” screen.

The following page will appear:

A query in CCP consist of the following components:

Connection (for testing and building)
Campaign (for testing and building)
Name
Parameters
Startup code
Fields
Base table
Joins
Condition
Group by
Order

2.1.1 Connection (for testing and building)

First of all, we need to specify a pre-defined connection. This connection is necessary to connect to the correct database.

The first option is ‘Use campaign settings’. This option will use the connection defined in the campaign settings.

After choosing a connection, all the tables and views can be used as “Base table” or in a join further on.

The connection will be used as a template. When you create an export, then you define the definitive connection.

2.1.2 Campaign (for testing and building)

If you have chosen “Use campaign settings”, then you need to specify a campaign.

Using campaign settings allows you to use the system tables as variable in the query. This allows you to create a query for more than one campaign.

Note that all the used data fields and tables must be present in the definitive connection.

2.1.3 Parameters

Like a stored procedure on SQL, a query in CCP can use parameters.

These parameters will show up in the export screen itself to ask the user for input.

You can for example define a start and end date, but also other kind of things.

To add a parameter, just click the button “Add parameter”.

Now a new parameter appears. In the given example, I added 2 parameters and already filled the basic information.

This basic information consists of:

Order.

You can change the value to define the position of a parameter.

When you change the order value of parameter 2 to 0, then parameter 2 will be placed before parameter 1 and will become one. The order values will be renumbered.

Display Name.

This text will be displayed in the export screen as description for this parameter. This is free text to supply the user with a more friendly label.

Name.

This is the SQL name for the parameter. This “Name” will act as variable.

As you can see in the example, the Name must start with an @ sign.

This is required by SQL server itself to identify variables.

It’s also not allowed to use special characters in a “Name”. This will result in an error in the query.

Data type.

You can define a basic datatype. The default is text which suit most situations.

This datatype is a CCP datatype.

At the moment, you can choose between number and text.

Query builder threads numbers different then text in for example comptonizations where a ‘real’ number is required.

You also see two buttons per parameter.

The last one is labeled “Delete”. By pressing this button, the parameter will be removed (without warning).

The other one is labeled “Details”. With this button, you can edit extra properties of the parameter. By clicking Details, the parameter will expand :

We can now choose the control type. This defines how the export screen will ask the user for input.

We can choose between Text and Dropdown. Both have a different set of additional property’s.

Controltype tekst.

For tekst we can define :

Required, when yes, the user is not allowed to leave this parameter empty
Read only, when yes, the user can’t change the parameter in the export screen
Maximum length, here you can define the maximum input length. 0 is undefined (creates varchar(max))
Validation, can contain a regex string for input validation
Default value, determines a default value, CCP commands and variables can be used (See section 5 and 6)
Error message, this text will be shown if the input doesn’t comply with the regex, when left empty on a required parameter.

Control type Dropdown.

The dropdown control comes in two flavors:

Static dropdown, the values are fixed.
Dynamic dropdown, the values are the result of a query.

Creating a static dropdown:

In a static dropdown, we can define:

Required, the field may not be left blank.
Dynamic, this is set to NO to define a static dropdown
List items, this field contains pairs of Display/Value items.
Default value, selects a default list item. The value is used to pre-select this item.
Error message, contains a message to show when the parameter is left empty in the export screen.

In the above example, we see a static dropdown which may not be left blank.

The first option is Empty. You can create an empty entry just by entering 2 commas’.

The value pairs come in Display/value pairs, all separated by a comma.

The first text (Display) is shown to the user, the second (value) is used in the query.

Note that the values MUST be unique! This is an HTML limitation.

Defining a dynamic dropdown.

Sometimes its necessary to define a dynamic dropdown.

In a dynamic dropdown, we can define:

Required, the field may not be left blank.
Dynamic, must be set to Yes to create a dynamic dropdown
Query, contains a query which will be used to populate the dropdown control in the export page. This query will be executed with in the same connection as used in the CCP query!
Display field, Contains the field name which will be used as value to display. The field MUST exist in the Query.
Value field, Contains the field name which will be used as value to use in the query itself. The field MUST exist in the Query for this parameter.
Default value, selects a default list item. The value is used to pre-select this item. You can also use CCP building commands to define dates like Yesterday and start of month.
Error message, contains a message to show when the parameter is left empty in the export screen.

For example :

Here we have defined a dynamic dropdown. The query in this parameter select 2 fields from a table. Note that the used fields MUST exist in the result set.

The values MUST also be unique. This is why there is a “distinct” in the query to ensure there no duplicates.

In this case CampagneId and CampagneOid are used as display/value.

The result is a list-item per record from the result set.

2.1.4 Startup code

The startup code contains TSQL which will be executed before the actual query.

This is useful to prepare (extra) variables.

Example, we want to use a start and end date in the query. So the where can look like “..where a.date between @StartDate and @EndDate”. This will work correct as long as both dates are filled by the user (or default).

But now we want to make it easier for the user. So, we make only @StartDate required and prefilled. @EndDate may now be left empty.

Normally we have to change the “where” in the query. But we can also use the following startup code:

if isnull(@EndDate,'') = ''

begin

set @EndDate = @StartDate

end

We will now prefill the @EndDate just before executing the query and ONLY when the user left this parameter empty.

It’s also possible to use CCP variables and commands in the startup code. This makes it more dynamic. For an overview see section 5 and 6 of this manual.

2.1.5 Base table

For exports, you normally query one or more tables. One table is always the base table.

Example:

Select

a.Date,

c.Name

from

Database..Clients as c

Join

Database..Appels as a

On a.indice = c.indice

In this case we can say that “Clients” is the base table.

To define this in Query builder we can say :

As you can see we can define:

Database, QueryBuilder requires a database name. #Campagnedb# is a variable to the campaign database.
Table, here you can define the table itself. Tables between hashtags (#) are variables. These will be replaced by the table used in the campaign itself. The variable tables will vary according to the campaign type. See section 8 for an overview.
Name, it is required that you give a table a unique name. This name is used in the “fields” section.
Table hint. Use this with care and read the MS-SQL manual before use! This option allows you to read over ‘locks’ but can also give ‘dirty reads’ (old value instead of new for example).

In the example, we set the base table to the client-file of the campaign. Since we use variables, we can use this query on every campaign which contains the same fields as used in the “fields” section.

2.1.6 Joins

We use this section to define extra tables to our query.

The definition of a join depends on:

Database, QueryBuilder requires a database name. #Campagnedb# is a variable to the campaign database.
Table, here you can define the table itself. Tables between hashtags (#) are variables. These will be replaced by the table used in the campaign itself. The variable tables wil very according to the campaign type. See section 8 for an overview.
Join type, you can define a join, inner join and left join.
Join on, this specifies which columns define the join binding.
Name, its required that you give a table a unique name. This name is used in the “fields” section.
Use this with care and read the MS-SQL manual before use! These option allows you to read over ‘locks’ but can also give ‘dirty reads’ (old value instead of new for example).

Here we defined a join to the call file of an outbound campaign. The Database and campaign will be determined when the query is executed.

2.1.7 Fields

After you defined the base table and joins, you can add the required fields for your query.

To add a field, simply press the “Add” button under “Export fields”.

In this screenshot I added 5 fields. To add 5 fields at once, just put “5” in the textbox before the “add” button.

An export field consist of:

Order, this works the same as with the parameters. Just put the position minus 1 to put the field in the desired position
Table, here you can select the base table or a join. The name you gave will be used in de dropdown.
Field, after you selected a table, you can select a field from that table to use. Note that table and field are not required. But “transcode” has to be filled when these are empty.
Name, this is the name used when exporting this field. The name is put between brackets, note you can’t use [ and ]
Transcode, here you can define custom transformations using standard TSQL. More explanation in 2.1.8.
Advanced, when clicking advanced, you can enter everything manual. More explanation 2.1.9.
Copy, this will copy the complete field definition to a new one. The new field will be added at the bottom.
Remove, this will remove the field. No confirmation will be asked.

2.1.8 Transcode

As already mentioned, transcode will let you transform your data using TSQL. It’s also possible to use CCP functions and variables to make your query more dynamic.

By clicking on the text “Transcode” you can enter a bigger edit box:

On the right, you have some dropdowns for variables, SQL functions and CCP functions.

To insert one of these, click the button “…” to append it to the end in the edit screen.

The variables will contain all you’re SQL parameters, the table names and some system variables. The system variables are explained in chapter 6.

“Field” is a special variable which will represent the current field.

The SQL functions consist of some standard SQL functions. A template will be insert, you can of course edit this to your needs.

The CCP functions consist of all available functions you can use in filenames, export definitions and query’s. A template will be insert, you can edit this to your needs.

More information per function can be found in chapter 5.

2.1.9 Advanced

Sometimes an SQL editor is too limited. This is where the advanced option comes in.

By activating advanced the field display will change to:

In advanced it’s possible to add everything manual, so multiple fields are possible. You can still use all CCP variables and functions to keep your procedure dynamic.

Note that the rendering of the actual query is field based. So, a comma (,) will be generated if not the last row. But you can eliminate this by using – at the end without an “enter”.

It’s also possible to do some other tricks. The text is first concatenated and then evaluated. This makes it possible to do some nice ‘tricks’…

2.1.10 Condition

The condition contains the “where” clause of the query. You can enter this as standard TSQL without “where”. All CCP variables and function will be evaluated before executing.

2.1.11 Group by

Here you can define your “group by” clause. You can enter this as standard TSQL without “group by”

All CCP variables and function will be evaluated before executing.

2.1.12 Order

Here you can define your “order” clause. You can enter this as standard TSQL without “Order”

All CCP variables and function will be evaluated before executing.

2.2 Editing a query

From the “Query Overview” page, you can edit a query by just clicking its name or the edit link after the Query name.

The editing is exactly the same as creating a query.

2.2.1 Testing a query

To test a query, you can press the button “Test” in the “Query builder”.

You can also click the link “test” after the query name in the “query overview” screen.

Your query will be “compiled” using the template connection used to create the query. The parameters will be filled with the default values!

You can use the dropdown “Export type” to change the exported file type, but there are some limitations.

This export will be running inside the web-application instead of the export module. For this reason :

Query’s that run to long can give a timeout
Excel format 2.1 is used as lightweight export format to create “on the fly”. The limits are 254 columns en a maximum of 656636 rows

After you press “Execute” the query will be executed. Any errors will be displayed.

*Note that the actual query is only shown, any editing here will not be used.

2.3 Advanced mode

On the right in the toolbar on the QueryBuilder screen you see an option with Procedure and Advanced.

When you turn on “advanced” you will be able to enter the SQL code directly, but you can still use all the CCP functions and variables, so you will still be able to write the queries dynamically.

The Base table, fields, joins, where and order sections will be gone. Instead you will get a big text area to enter the SQL code.

In this mode, you can use every TSQL statement. But careful with the hash (#) sign, there is a variable to use the # to create temporarily tables (#ht#).

Note the GO is not a TSQL instruction but an implementation in Sql Management Studio to separate batches.

2.4 Removing a query

Removing a query is done by pressing “Delete” after the query name from the “Query overview” screen.