Database subscriber WHERE conditions

[gerg.borsodi]

Hi there!
I have a question regarding the Database subscriber (Incoming config).
Lets assume, that I have an MSSQL server, with a table named “PersonalData”, and a column inside named “EmailAddress”.

Now, what I need is to get all rows that have EmailAddress defined (so <> “” and <> NULL), but I don’t know how the Where conditions work:

What should go into the textarea next to the key name in the below mentioned situation? (Or rather, how to “convert” an sql where string like “[…] where EmailAddress <> “” and EmailAddress <> NULL” to that textarea?)

Please note, that the “Use raw query” is not an option in my case, since I need this functionality, that every row is processed in a separate task (or a separate route task).

Thank you for your answer in advance!

[m.arief]

Hi @gerg.borsodi,

You can use following operator for where conditions :

1. eq
2. neq
3. gt
4. gte
5. lt
6. lte
7. inq
8. nin
9. like
10. nlike
11. regexp

You can read full schema in here

{
  "type": "object",
  "additionalProperties": {
    "oneOf": [
      {
        "type": "object",
        "title":"Search criteria",
        "properties": {
          "eq": {
            "$ref": "#/definitions/value"
          },
          "neq": {
            "$ref": "#/definitions/value"
          },
          "gt": {
            "$ref": "#/definitions/value"
          },
          "gte": {
            "$ref": "#/definitions/value"
          },
          "lt": {
            "$ref": "#/definitions/value"
          },
          "lte": {
            "$ref": "#/definitions/value"
          },
          "inq": {
            "type": "array",
            "items": {
              "$ref": "#/definitions/value"
            }
          },
          "nin": {
            "type": "array",
            "items": {
              "$ref": "#/definitions/value"
            }
          },
          "like": {
            "$ref": "#/definitions/value"
          },
          "nlike": {
            "$ref": "#/definitions/value"
          },
          "regexp": {
            "$ref": "#/definitions/value"
          }
        }
      },
      {
        "title": "Value",
        "$ref": "#/definitions/value"
      }
    ]
  },
  "ui": {
    "ui:subTitle": "A set of conditions all results must match"
  },
  "definitions": {
    "value": {
      "type": [
        "string",
        "number",
        "boolean",
        "array",
        "null"
      ]
    }
  }
}

For example :

However, we recommend to use RAW Query instead, because it much easier to use for complex query. You can then divide the rows into separated tasks by using transformer “Get branches from a pattern”

These article might help you

Database Transformer

Table of contents General description The different parts of the Database Transformer General description This Database Transformer can be used to interact with databases via a pre-configured or inline-configured connection. By using this Transformer, it is possible to directly interact with a database to either retrieve or store data. All queries can be executed from generating tables to removing records. However, this can be limited based on the rights of the used account to connect to the d…

Get branches from a pattern

Table Of Contents Use case Video guide Use case It is a common practice for the users to opt for multiple individual processing over batch-processing since the former offers better maintenance and governance over the integrations. While it sounds cool, accomplishing it on-the-ground is not that easy since a single interaction with a foreign system can result in the retrieval of multiple data entities. Alumio comes armed with one of the finest transformers, namely Get branches from a patte…

[gerg.borsodi]

Thank you for your reply!