qapp tool (stands for query analyzer ++)

Coordinator
Apr 23, 2010 at 6:09 PM
Edited Apr 23, 2010 at 6:40 PM

This tool has a single interface that allows the user to change the connection string to connect to SQL, Oracle, SQLite, or any ODBC database providers

Once connected to a database, one can issue simple SQL statements or customized @MACRO statements.  A macro is similiar to CLR Stored Procedure; but, is not tied to one database provider.

Here are a few data warehousing macros:

  • @Groupby <tablename> <column>

this macro is a shortcut that expands to the following select statement:

Example: @GROUPBY Product year

select year, count(*) Count from Product group by year order by year
  • @Pivot <inputTable> <outputTable> <valueColumn> <entryColumn> <groupColumn>

pivot the input table using the three columns (value, entry, group) and create an output table.  This macro creates the necessary indexes (for performance), generates the pivot query, and executes it.

Example: @PIVOT Products OUT_TABLE value ProductName Year

select
    b.ProductName [value from Products]
    ,k0.v0 [value where Year = '2006']
    ,k1.v1 [value where Year = '2007']
    ,k2.v2 [value where Year = '2008']
    ,k3.v3 [value where Year = '2009']
    ,k4.v4 [value where Year = '2010']
	/* and so on ... */
from
    (select distinct ProductName from Products) b
    inner join (select ProductName, value as v0 from Products where Year = '2006') k0 on k0.ProductName = b.ProductName
    inner join (select ProductName, value as v1 from Products where Year = '2007') k1 on k1.ProductName = b.ProductName
    inner join (select ProductName, value as v2 from Products where Year = '2008') k2 on k2.ProductName = b.ProductName
    inner join (select ProductName, value as v3 from Products where Year = '2009') k3 on k3.ProductName = b.ProductName
    inner join (select ProductName, value as v4 from Products where Year = '2010') k4 on k4.ProductName = b.ProductName
	/* and so on ... */
 order by b.ProductName


  • @Dupi <inputTable> <outputTable> <primaryColumn>

query the input table and return all rows having duplicated primary key.

  • @Dupe <inputTable> <outputTable> <primaryColumn>

query the input table and return all rows having NON-duplicated primary key.

  • @Dupo <inputTable> <outputTable> <primaryColumn> <secondaryColumn>

query the input table, dedup it by the primary, and return a row for each unique primary key

These macros are written in .NET.  The purpose is to create multi-complex queries and execute them.

Macros are marked with the [MacroMethod] attribute; and its class is marked with [Macro] attribute.