If you have read the philosophy behind the Fuzible project, you’ll see that when not querying a native SQL database, the software relies on SQL-92 to get and manipulate data from any other source.
It means that you can query any file, any webservice, any mailbox… using SQL !
You can do pretty much everything you are able to do with a “normal” database, from a simple SELECT statement, to a more complex one.
Here’s a list of supported syntax :
When you’re writing a query, a contextual menu can show up to give you suggestion(s). It’s often helpful.
Supported functions :
| TRANSFORMATION | |
| SUBSTRING | Extract a contiguous sequence of characters within a string |
| CONCAT | Concatenate fields or strings |
| CONVERT | Force data conversion |
| CASE field WHEN … THEN … ELSE … END | Conditional statement |
| LTRIM, RTRIM | Remove whitespace(s) before or after a string |
| ISNULL, COALESCE | Replace a NULL value by something else |
| LPAD, RPAD | Left (or right)-pads a string with another string, to a certain length. |
| LENGTH | String length |
| CHARINDEX | Searches for one character expression inside a second character expression |
| LOWER, UPPER | Lowercase or uppercase a string |
| REPLACE | Replace a value by another one |
| AGGREGATION | |
| SUM | The sum from a set of values |
| MAX, MIN | The min or max value from a set of values |
| AVG | The average from a set of values |
| COUNT | The number of rows from a Select statement |
| THE BASICS | |
| SELECT DISTINCT | Removes duplicates |
| SELECT TABLE x | Not a standard SQL-92 function. Allows Fuzible to manipulate data from any of the retrieved data tables |
| SELECT TABLE x ONLY | Not a standard SQL-92 function. Allows Fuzible to only retrieve a single table from a multi-datatables result |
| LIMIT, TOP | Limits the quantity of retrieved rows |
| JOIN (LEFT, OUTER, INNER, RIGHT) | Joins between tables, files, webservices… |
| WHERE | Filters |
| ORDER BY | Order the result set |
| GROUP BY | Aggregations |
| UNION | Merge data from multiple SELECT statements. |
| ADVANCED FUNCTIONS | |
| Math functions in a transformation pattern | Ex : SELECT SUBSTRING(li_sample, CHARINDEX(li_sample, “-“) + 1, 10) FROM myfile.csv |
| Sub-queries | Ex : SELECT * FROM (select * FROM myfile.csv) as subQ Ex 2 : SELECT * FROM myfile.csv WHERE id_sample NOT IN (SELECT id FROM myotherfile.csv) |
Unsupported functions :
| HAVING | Filtering without a GROUP BY aggregation |
| « null » | NULL is a pure database syntax. Ex : CASE WHEN x IS NULL THEN must be written like this : CASE WHEN x = ” THEN |
| Field framing | You can’t write something like this : Ex : SELECT [myfield] FROM [myfile] Ex2 : SELECT “myfield” FROM “myfile” |
| « GETDATE » ou « CURRENT_TIMESTAMP » | You can instead use Dynamic Parameters : Ex : SELECT * FROM myfile WHERE year > {%YYYY} |
| A subquery for a field | Ex : SELECT (select id FROM myfield.csv) as id FROM myotherfile.csv |
| Math functions on aggregated results | Ex : SELECT COUNT(*) + 10 FROM myfile |
English 
French