Skip to main content

sql_where

Definition​

sql_where is a property used in Veezoo's configuration to specify the SQL equivalent of a function when it appears in the WHERE clause of an SQL query. This property is used for CB_One2Maybe functions and allows users to leverage custom filtering conditions without needing to write SQL.

Usage​

sql_where is a string that contains the SQL code to apply the filter, with placeholders ($1, $2, $3, etc.) representing the arguments that the function accepts. These placeholders correspond to the first, second, third, etc., arguments and will be replaced with the actual values when the function is called.

Example​

Consider the Leap_Year function from our examples here. The sql_where property is used to define the SQL operation that checks whether a date belongs to a leap year:

function Leap_Year {
   name: "Leap Year"
   // Rest of function definition...
   sql_where: "(EXTRACT(DOY FROM DATE (EXTRACT(YEAR FROM $1)::text || '-12-31')) = 366)"
}

In this example, (EXTRACT(DOY FROM DATE (EXTRACT(YEAR FROM $1)::text || '-12-31')) = 366) is the SQL code that checks if a date is in a leap year. The $1 is a placeholder for the first argument, which in this case would be a date. If the function is called with a date argument like birthdate, this SQL code will be inserted in the WHERE clause of the SQL query, filtering for rows where the birthdate is in a leap year.

Guidelines​

When defining sql_where:

  • Use valid SQL syntax corresponding to your database engine.
  • Make sure to correctly map the function's arguments to the placeholders.
  • The resulting SQL function should return a boolean (true or false), as it will be used for filtering.

Remember, sql_where allows you to apply sophisticated filter conditions directly within Veezoo, enhancing the flexibility and precision of data analysis.