Database independent Advanced Query commands

By Gonçalo Almeida on 21 Jul 2014
Hi all,
 
Persuant to this technical note: http://www.outsystems.com/NetworkDocuments/DocumentFromExterior.aspx?DocumentId=637
I came up with an idea to improve the coding speed and portability of Advanced Queries, since they pose the most problems when we need to convert SQL syntax from one database to another.
 
Taking this example: SELECT {table}.[id] + ' - ' + {table}.[name] FROM {table}
 
This will not work in an Oracle Database, since the concatenation char is "||" and not the "+".
 
So, how about writting this, instead: SELECT {Concatenate({table}.[id], ' - ', {table}.[name])} FROM {table}
The Platform, at compile time, would recognize the "{" as it does now for tables and would transform to the apropriate syntax, depending on wich database it is running on.
 
"Concatenate" is but a universal name that could be used. More examples:
 
SELECT {table}.* FROM {table} WHERE {table}.[creationDate] < {SystemDate()}
SELECT {table}.* FROM {table} WHERE {table}.[creationDateTime] < {SystemDateTime()}
 
SELECT {table}.* FROM {table} WHERE {table}.[name] = {EmptyString()}
 
SELECT {table}.* FROM {table} ORDER BY {ConvertType({table}.[order], nvarchar)}
 
SELECT {Top(10)} {table}.* FROM {table}
 
I'm sure there are many others (like the date functions and so on), but I think I got my point across.
I attached a picture of an advanced query editor. The red rectangle could contain all the functions that could be used like this (perhaps the design guys can find a better place for it).
 
Tell me what you think of this.
 
regards
 advQuery.png
This idea has no comments yet. Be the first to comment!