User Data Rules
Entered user data obeys the following relaxed rules:
- anything that starts by @ is considered a parameter name, in a parameterized query.
- Null, with any other case insensitive combination (such as null, NULL, nULL etc), is reserved for the NULL SQL value.
- numbers can be integers (like 0, 12345, -14) or floats (like 0.0, 1.0, 1.2345, -34.567). They cannot be entered in other format, such as exponential or percent.
- datetime values must have the format ‘yyyy-MM-dd hh:mm:ss.ttt’, or just ‘yyyy-MM-dd’.
- strings can be left with no ‘ … ‘ enclosed notation and will be considered strings, unless they translate into Null, numeric or datetime values. ‘Null’ is different from Null, and ‘1’ is different from 1. When not enclosed, any ‘ within will be automatically doubled, as ”.
- when you cannot enter one of these values directly in the cell, try to add it as a Customizable Value through the contextual menu, then modify it.
Required FROM Clause
Most platforms require a FROM clause even when you do not select data from any table, view or subquery. You may have all constants and/or global function calls on top of your builder, in the Field row. We transparently take care of this issue and automatically generate a specific FROM clause when required.
Select Constant is an extremely simple demo query whose sole purpose is to show you the generated FROM clause for a very simple SELECT 1, such as here below:
Switch to design mode the Conversion Functions demo query, for usage of some conversion functions, such as:
- ToString – number or datetime to Unicode.
- ArrayToString(separator=’|’) – PostgreSQL array to elements separated by separator.
- ToInteger / ToNumber / ToFloat – string to number (int / any / float).
- ToDateAndTime / ToDate / ToTime – string to date and/or time.
- Format(value=1, format=’C’) – specific format.
- ToTrueOrFalse – to True or False, as string.
- Aggregate Queries
- Basic Joins
- Conditional Formula
- Configuration Options
- Crosstab Queries
- Custom Joins
- Data Providers
- Database Connections
- Date and Time
- Demo Queries
- Diagram Options
- ER Notation
- Filter Data
- Fluent Expressions
- Generated Models
- Grouping Sets
- Inline Subqueries
- Many-to-Many Relationships
- Metadata Synchronization
- Join Notation
- Nesting Queries
- Null Issues and Fixes
- One-to-Zero/One Relationships
- Parameterized Queries
- Query Types
- Relationship Items
- Result Extensions
- Skip-and-Keep Queries
- Sorting Data
- Supported Databases
- Union Joins
- Window Functions