Write a PostgreSQL query

Contents

• Query editor
• Placeholders
• Test query

Prerequisites

You should already have created a connection to your PostgreSQL database in FactBranch. If not, please first create a PostgreSQL connection.

Query editor

This is the FactBranch SQL editor:

Use the big dark area in the center to write your query and the pane on the right to test it. The editor has automatic code-highlighting, so your SQL code always looks structured and clear.

Placeholders

First, we'll look at how to use a simple email address placeholder in your query.

Whenever you want to search for an email address, use this placeholder in your SQL code: %(email)s

So for example if the table customer contains the field email and you'd like to search by email address, the WHERE-part of your query should look like this:

WHERE customer.email=%(email)s

You can also search by phone number or by a custom field. These are the placeholders you can currently use:

• %(email)s
• %(phone_number)s
  The phone number will have the format +1... for US-numbers and for example +49... for a German number.
• %(custom_field_3600001234567)s
  In this example the custom field has the ID 3600001234567. Look up the number of the custom field you'd like to use in your Zendesk installation > Admin > Manage > Ticket Fields.

Test query

To test your query, look at the test query area on the right, enter an email address that yields a result and click Search. FactBranch will display the results it gets from the database—or an error message pointing you into the right direction.

Make sure that each field name is unique. So if you join multiple tables that contain fields with the same name, remember to give them different names in your SELECT-statement.

If you want to search by phone number, use the placeholder %(phone_number)s in your query and select Phone number from the Input from Zendesk dropdown in the test query area.

To search by custom field, select Custom field from the same dropdown and enter the custom field ID you'd like to use. In your query for example use %(custom_field_3600001234567)s as the placeholder.

Once you are happy with the query and get at least one result, click Continue. The designer in the next step will use these results to give you a preview of the outputs in the Zendesk app.

Next step: Design the output