SQL Transformation Functions

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 :

SUBSTRINGExtract a contiguous sequence of characters within a string
CONCATConcatenate fields or strings
CONVERTForce data conversion
CASE field WHEN … THEN … ELSE … ENDConditional statement
LTRIM, RTRIMRemove whitespace(s) before or after a string
ISNULL, COALESCEReplace a NULL value by something else
LPAD, RPADLeft (or right)-pads a string with another string, to a certain length.
LENGTHString length
CHARINDEXSearches for one character expression inside a second character expression
LOWER, UPPERLowercase or uppercase a string
REPLACEReplace a value by another one
SUMThe sum from a set of values
MAX, MINThe min or max value from a set of values
AVGThe average from a set of values
COUNTThe number of rows from a Select statement
SELECT DISTINCTRemoves duplicates
SELECT TABLE xNot a standard SQL-92 function. Allows Fuzible to manipulate data from any of the retrieved data tables
SELECT TABLE x ONLYNot a standard SQL-92 function. Allows Fuzible to only retrieve a single table from a multi-datatables result
LIMIT, TOPLimits the quantity of retrieved rows
JOIN (LEFT, OUTER, INNER, RIGHT)Joins between tables, files, webservices…
ORDER BYOrder the result set
GROUP BYAggregations
UNIONMerge data from multiple SELECT statements.
Math functions in a transformation patternEx : SELECT SUBSTRING(li_sample, CHARINDEX(li_sample, “-“) + 1, 10) FROM myfile.csv
Sub-queriesEx : 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 :

HAVINGFiltering 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 framingYou 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 fieldEx : SELECT (select id FROM myfield.csv) as id FROM myotherfile.csv
Math functions on aggregated resultsEx : SELECT COUNT(*) + 10 FROM myfile