-
Type:
How To
-
Status: Obsolete
-
Affects Version/s: Exasol 6.2.x
-
Fix Version/s: None
-
Component/s: EXASolution
-
Labels:None
-
Solution:
-
Category 1:SQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Often, an analysis will be complex enough that it is very hard or cumbersome to express in a single SQL statement. The usual way to work around this is to create temporary tables with intermediate results and then combine those in a 'final' select statement:
CREATE TABLE german_cities AS ( SELECT cities.* FROM cities, countries WHERE cities.country_id=countries.country_id AND countries.name='Germany' ); SELECT b.name, c.name, count(*) FROM customer_moves a, german_cities b, german_cities c WHERE a."from" = b.city_id AND a."to" = c.city_id GROUP BY b.name, c.name;
However, this has some side-effects:
Please note that EXASolution does not know the concept of temporary tables. As this is an in-memory database and we really stick to the ACID rules, every table can be interpreted as temporary until it is committed.
So if you implement multi-path SQL using intermediate tables, we suggest you turn off auto-commit for this and drop the tables when the analysis is complete. After that, it is 'safe' to commit your changes (if any).
The better solution, in this case, is to replace the temporary tables with views, giving the following side-effects
CREATE VIEW german_cities AS ( SELECT cities.* FROM cities, countries WHERE cities.country_id=countries.country_id AND countries.name='Germany' ); SELECT b.name, c.name, count(*) FROM customer_moves a, german_cities b, german_cities c WHERE a."from" = b.city_id AND a."to" = c.city_id GROUP BY b.name, c.name;
But in the case where the multi-path query is generated on the fly by some application, you still have the overhead of views being created and possible namespace clashes.
In these cases, named subselects (also called Common Table Expressions or CTE) might be the thing you've been looking for.
Basically, they work like views, but they are not database objects, but parts of your query. No need to create them, drop them, commit them: No overhead, but still all the flexibility you wish for:
WITH german_cities AS ( SELECT cities.* FROM cities, countries WHERE cities.country_id=countries.country_id AND countries.name='Germany' ) SELECT b.name, c.name, count(*) FROM customer_moves a, german_cities b, german_cities c WHERE a."from" = b.city_id AND a."to" = c.city_id GROUP BY b.name, c.name;