10.511: Advanced SDK for Customizing Visualizations

Starting with Strategy Secure Enterprise 10.0, the Strategy SQL Engine generates SQL to support case sensitive database objects for some database types.  The SQL engine can generate SQL to support case sensitivity in Table names only OR Column names only OR Table names AND Column names (depending on the database type). Details of the SQL generation are explained in the sections below: 
 
SQL Engine support for case-sensitive Table names only
The following section illustrates how the Strategy SQL Engine generates SQL when a table with a mix-cased table name (a table named such as "Lu_Customer") is added to a Strategy project.
Table names are enclosed in a single tick (`) for the following database types:

• MySQL 5.x 
• MariaDB 5.x
• MariaDB 10.x

For example:

select a14.REGION_ID REGION_ID,
max(a17.REGION_NAME) REGION_NAME0,
a12.INCOME_ID INCOME_ID,
max(a16.BRACKET_DESC) BRACKET_DESC0,
a15.YEAR_ID YEAR_ID,
sum((a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT))) Revenue
from `ORDER_DETAIL` a11
join `Lu_Customer` a12
on (a11.Customer_Id = a12.Customer_Id)
join `LU_EMPLOYEE` a13
on (a11.EMP_ID = a13.EMP_ID)
join `LU_CALL_CTR` a14
on (a13.CALL_CTR_ID = a14.CALL_CTR_ID)
join `LU_DAY` a15
on (a11.ORDER_DATE = a15.DAY_DATE)
join `LU_INCOME` a16
on (a12.INCOME_ID = a16.INCOME_ID)
join `LU_REGION` a17
on (a14.REGION_ID = a17.REGION_ID)
group by a14.REGION_ID,
a12.INCOME_ID,
a15.YEAR_ID

 
 Table names are enclosed in double quotes for the following database types:

• Cirro

For example:

select a14.REGION_ID REGION_ID,
max(a17.REGION_NAME) REGION_NAME0,
a12.INCOME_ID INCOME_ID,
max(a16.BRACKET_DESC) BRACKET_DESC0,
a15.YEAR_ID YEAR_ID,
sum((a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT))) Revenue
from "ORDER_DETAIL" a11
join "Lu_Customer" a12
on (a11.Customer_Id = a12.Customer_Id)
join "LU_EMPLOYEE" a13
on (a11.EMP_ID = a13.EMP_ID)
join "LU_CALL_CTR" a14
on (a13.CALL_CTR_ID = a14.CALL_CTR_ID)
join "LU_DAY" a15
on (a11.ORDER_DATE = a15.DAY_DATE)
join "LU_INCOME" a16
on (a12.INCOME_ID = a16.INCOME_ID)
join "LU_REGION" a17
on (a14.REGION_ID = a17.REGION_ID)
group by a14.REGION_ID,
a12.INCOME_ID,
a15.YEAR_ID

 
SQL Engine support for case-sensitive table and column names
The following section illustrates how the Strategy SQL Engine generates SQL when a table with mix-cased table and column names (a table named "Lu_Customer" with a column named "Customer_Id") is added to a Strategy project.
Note: The SQL Engine assumes that all of the warehouse tables tied to the 'Customer_Id' column through Strategy attributes or metrics have the same case sensitive name.  For the following examples, that means that both the "Lu_Customer" and the "ORDER_DETAIL" table should have the column named "Customer_Id".  If not, the SQL will fail with a "column not found" error message.
 
Table and column names are enclosed in double quotes for the following database types:

• Pivotal Greenplum 4.x 
• Pivotal HAWQ 1.0.x
• SAP HANA

For example:

select a14."REGION_ID" AS "REGION_ID",
max(a17."REGION_NAME") AS "REGION_NAME0",
a12."INCOME_ID" AS "INCOME_ID",
max(a16."BRACKET_DESC") AS "BRACKET_DESC0",
a15."YEAR_ID" AS "YEAR_ID",
sum((a11."QTY_SOLD" * (a11."UNIT_PRICE" - a11."DISCOUNT"))) AS "Revenue"
from "ORDER_DETAIL" a11
join "Lu_Customer" a12
on (a11."Customer_Id" = a12."Customer_Id")
join "LU_EMPLOYEE" a13
on (a11."EMP_ID" = a13."EMP_ID")
join "LU_CALL_CTR" a14
on (a13."CALL_CTR_ID" = a14."CALL_CTR_ID")
join "LU_DAY" a15
on (a11."ORDER_DATE" = a15."DAY_DATE")
join "LU_INCOME" a16
on (a12."INCOME_ID" = a16."INCOME_ID")
join "LU_REGION" a17
on (a14."REGION_ID" = a17."REGION_ID")
group by a14."REGION_ID",
a12."INCOME_ID",
a15."YEAR_ID"

 
 Table and column names are enclosed in a single tick (`) for the following database types:

• Denodo 5.x
• MapR Drill 1.x

For example:

select a14.`REGION_ID` REGION_ID,
max(a17.`REGION_NAME`) REGION_NAME0,
a12.`INCOME_ID` INCOME_ID,
max(a16.`BRACKET_DESC`) BRACKET_DESC0,
a15.`YEAR_ID` YEAR_ID,
sum((a11.`QTY_SOLD` * (a11.`UNIT_PRICE` - a11.`DISCOUNT`))) Revenue
from `ORDER_DETAIL` a11
join `Lu_Customer` a12
on (a11.`Customer_Id` = a12.`Customer_Id`)
join `LU_EMPLOYEE` a13
on (a11.`EMP_ID` = a13.`EMP_ID`)
join `LU_CALL_CTR` a14
on (a13.`CALL_CTR_ID` = a14.`CALL_CTR_ID`)
join `LU_DAY` a15
on (a11.`ORDER_DATE` = a15.`DAY_DATE`)
join `LU_INCOME` a16
on (a12.`INCOME_ID` = a16.`INCOME_ID`)
join `LU_REGION` a17
on (a14.`REGION_ID` = a17.`REGION_ID`)
group by a14.`REGION_ID`,
a12.`INCOME_ID`,
a15.`YEAR_ID`

 
SQL Engine support for case-sensitive Column names only
The following section illustrates how the Strategy SQL Engine generates SQL when a table with mixed cased column names (a column named "Customer_Id") is added to a Strategy project.
Column names are enclosed in double quotes for the following database types:

• IBM DB2 Version 10.1 for Linux, UNIX and Windows
• IBM DB2 Version 10.5 for Linux, UNIX and Windows
• IBM DB2 Version 11.1 for Linux, UNIX and Windows
• IBM DB2 10 for z/OS
• IBM DB2 11 for z/OS
• IBM InfoSphere BigInsights 3.x
• Pivotal HAWQ 1.3.x 

For example:

select a14."REGION_ID" AS "REGION_ID",
max(a17."REGION_NAME") AS "REGION_NAME0",
a12."INCOME_ID" AS "INCOME_ID",
max(a16."BRACKET_DESC") AS "BRACKET_DESC0",
a15."YEAR_ID" AS "YEAR_ID",
sum((a11."QTY_SOLD" * (a11."UNIT_PRICE" - a11."DISCOUNT"))) AS "Revenue"
from ORDER_DETAIL a11
join Lu_Customer a12
on (a11."Customer_Id" = a12."Customer_Id")
join LU_EMPLOYEE a13
on (a11."EMP_ID" = a13."EMP_ID")
join LU_CALL_CTR a14
on (a13."CALL_CTR_ID" = a14."CALL_CTR_ID")
join LU_DAY a15
on (a11."ORDER_DATE" = a15."DAY_DATE")
join LU_INCOME a16
on (a12."INCOME_ID" = a16."INCOME_ID")
join LU_REGION a17
on (a14."REGION_ID" = a17."REGION_ID")
group by a14."REGION_ID",
a12."INCOME_ID",
a15."YEAR_ID"