In this post, we’re going to discuss a very useful SQL extension to work with hierarchal queries. This is sadly only implemented by Oracle and although a few other databases support it as well, it won’t work in PostgreSQL, MySql nor SQL Server. The dataset Let’s create a very simple hierarchal dataset to work with. CREATE TABLE GeoArea ( parentName VARCHAR2(100), code VARCHAR2(100) ); INSERT INTO GeoArea VALUES (NULL, 'World'); INSERT INTO GeoArea VALUES ('World', 'Europe'); INSERT INTO...| andreabergia.com
The most powerful SQL databases, like Oracle (but, in this case, also SQL Server, DB2 and Sybase) have a lot of tricks. This time we’re going to see one that applies to GROUP BY expressions. First, let’s define a simple schema: CREATE TABLE Sales ( country VARCHAR(10), sale_date DATE, amount NUMBER ); INSERT ALL INTO Sales VALUES ( 'Italy', TO_DATE('2014-10-01', 'YYYY-MM-DD'), 52) INTO Sales VALUES ( 'Italy', TO_DATE('2014-10-10', 'YYYY-MM-DD'), 54) INTO Sales VALUES ( 'Italy', TO_DATE('2...| Andrea Bergia's Website