You can use SQL to create new tables and fields in the ElastiCube Manager.

SQL Structure

SELECT  FieldName(s),Function(),  *
FROM TableName 1
INNER JOIN\ LEFT JOIN\RIGHT JOIN\FULL JOIN  TableName 2
ON TableName 1. JoinField =  TableName 2. JoinField
WHERE FieldName Condition
AND\OR FieldName Condition
GROUP BY FieldName(s)
ORDER BY FieldName(s)
HAVING FieldName(s) Condition
SQL Example

SELECT CustomerName, ContactEmail,Count(Orders)
FROM Customer
INNER JOIN Order
ON Customer.CustomerID = Order.CustomerID
WHERE Order.OrderId  BETWEEN 10 AND 100
AND Customer.CustomerName IN (‘John’,’Mary’,’David’)
OR Customer.CustomerLastName  LIKE ‘Harrison’
GROUP BY Customer.CustomerName
ORDER BY Customer.CustomerLastName
HAVING Count(Orders) > 3

Basic SQL Syntax Guide

 SELECT STATEMENT 

 SELECT  SELECT column_name(s)
FROM table_name 
 SELECT *  SELECT *
FROM table_name 
 SELECT DISTINCT  SELECT DISTINCT column_name(s)
FROM table_name                                    
Note: Nested SELECT statements will not work with an AND clause in the ElastiCube Manager.

 

FUNCTIONS AFTER SELECT CLAUSE

 FUNCTIONS             AVG() – Returns the average value
COUNT() – Returns the number of rows
MAX() – Returns the largest value
MIN() – Returns the smallest value
SUM() – Returns the sum  

 

TABLE JOIN FUNCTIONS

 INNER JOIN        SELECT column_name(s)
FROM table_name1 T1
INNER JOIN table_name2  T2
ON T1.column_name=T2.column_name
 LEFT JOIN  
 
SELECT column_name(s)
FROM table_name1 T1
LEFT JOIN table_name2  T2
ON T1.column_name=T2.column_name 
 RIGHT JOIN   SELECT column_name(s)
FROM table_name1 T1
RIGHT JOIN table_name2  T2
ON T1.column_name=T2.column_name 
 FULL JOIN  
 
SELECT column_name(s)
FROM table_name1 T1
FULL JOIN table_name2  T2
ON T1.column_name=T2.column_name 
 AS (alias)  
 
SELECT column_name AS column_alias
FROM table_nameorSELECT column_name
FROM table_name  AS table_alias
 WHERE   SELECT column_name(s)
FROM table_name
WHERE column_name operator value   

 

FUNCTIONS AFTER THE WHERE CLAUSE

 AND / OR           
      
SELECT column_name(s)
FROM table_name
WHERE condition
AND|OR condition
 BETWEEN  
 
 
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
 IN  SELECT column_name(s)
FROM table_name
WHERE column_name
IN (value1,value2,..)
 LIKE  
  
 
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern          

 

ORDERING AND GROUPING AFTER WHERE CONDITIONS

 GROUP BY  
          
      
SELECT column_name,
aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
ORDER BY  
 
 
 
SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC]
 HAVING   SELECT column_name,
aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING
aggregate_function(column_name) operator value 

 

CLAUSES TO COMBINE TABLES

 UNION                         
      
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
UNION ALL  
  
 
 
 
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2 

 

SEE ALSO

Basic SQL Guide

MSDN SQL Reference