Sample Kyvos SQL queries for Alteryx

Applies to: Kyvos Enterprise Kyvos Cloud (SaaS on AWS) Kyvos AWS Marketplace

Kyvos Azure Marketplace Kyvos GCP Marketplace Kyvos Single Node Installation (Kyvos SNI)

Samples for the types of queries that work with Kyvos:

Examples of Kyvos supported queries

/*============================================================================
################################################################################

SQL Query examples for working cases

################################################################################
==============================================================================*/

/* Case#1  */
SELECT *
FROM   `kyvos_browsing_automationcubes`.`order date` `Order Date`
LIMIT  10

/* Note: SELECT * query with LIMIT clause will provide all the columns and result as dummy data */



/* Case#2  */
SELECT `order date`.`category` AS `category`
FROM   `kyvos_browsing_automationcubes`.`order date` `Order Date`
GROUP  BY `order date`.`category`
ORDER  BY `order date`.`category` 

/* Example of how to select dimension */


/* Case#3  */
SELECT Min(Year(`order_date1`.`orderdate`)) AS `temp_tc___561953214__0_`,
       Count(1)                             AS `x__alias__1`
FROM   `kyvos_dataaccuracycubes`.`order date` `order_date1`
       LEFT JOIN (SELECT `order_date1`.`productname`    AS `productname`,
                    Max(`order_date1`.`max sales`) AS `x__alias__0`
             FROM   `kyvos_dataaccuracycubes`.`order date` `order_date1`
             WHERE  ( NOT ( `order_date1`.`productname` IS NULL ) )
             GROUP  BY `order_date1`.`productname`
             ORDER  BY `x__alias__0` ASC,
                       `productname` ASC
             LIMIT  100) `t0`
         ON ( `order_date1`.`productname` = `t0`.`productname` )
HAVING ( Count(1) > 0 ) 
/* Example of how to use left join and aggregated sql query */



/* Case#4  */
SELECT Min(Year(`order_date1`.`orderdate`)) AS `temp_tc___561953214__0_`,
       Count(1)                             AS `x__alias__1`
FROM   `kyvos_dataaccuracycubes`.`order date` `order_date1`
       JOIN (SELECT `order_date1`.`productname`    AS `productname`,
                    Max(`order_date1`.`max sales`) AS `x__alias__0`
             FROM   `kyvos_dataaccuracycubes`.`order date` `order_date1`
             WHERE  ( NOT ( `order_date1`.`productname` IS NULL ) )
             GROUP  BY `order_date1`.`productname`
             ORDER  BY `x__alias__0` ASC,
                       `productname` ASC
             LIMIT  100) `t0`
         ON ( `order_date1`.`productname` = `t0`.`productname` )
HAVING ( Count(1) > 0 )
/* Example of how to use join and aggregated SQL query */


/* Case#5  */
SELECT `t0`.`avg_avg_discount_ok`     AS `avg_avg_discount_ok`,
       `t0`.`avg_avg_quantity_ok`     AS `avg_avg_quantity_ok`,
       `t0`.`avg_avg sales_ok`        AS `avg_avg sales_ok`,
       `t3`.`x_measure__4`            AS `sum_calculation_702843039217930242_ok`,
       `t0`.`sum_avg shippingcost_ok` AS `sum_avg shippingcost_ok`,
       `t0`.`sum_min_profit_ok`       AS `sum_min_profit_ok`,
       `t0`.`sum_min_sales_ok`        AS `sum_min_sales_ok`,
       `t0`.`yr_shipdate_ok`          AS `yr_shipdate_ok`
FROM   (
                SELECT   year(`ORDER date`.`shipdate`)                    AS `yr_shipdate_ok`,
                         avg(`ORDER date`.`avg discount`)                 AS `avg_avg_discount_ok`,
                         avg(cast(`ORDER date`.`avg quantity` AS DOUBLE)) AS `avg_avg_quantity_ok`,
                         avg(`ORDER date`.`avg sales`)                    AS `avg_avg sales_ok`,
                         sum(`ORDER date`.`avg shippingcost`)             AS `sum_avg shippingcost_ok`,
                         sum(`ORDER date`.`min profit`)                   AS `sum_min_profit_ok`,
                         sum(`ORDER date`.`min sales`)                    AS `sum_min_sales_ok`
                FROM     `kyvos_dataaccuracycubes`.`ORDER date` `ORDER date`
                GROUP BY year(`ORDER date`.`shipdate`) ) t0
JOIN
       (
                SELECT   `t1`.`yr_shipdate_ok`    AS `yr_shipdate_ok`,
                         sum(`t2`.`x_measure__3`) AS `x_measure__4`
                FROM     (
                                  SELECT   `ORDER date`.`country`        AS `country`,
                                           year(`ORDER date`.`shipdate`) AS `yr_shipdate_ok`
                                  FROM     `kyvos_dataaccuracycubes`.`ORDER date` `ORDER date`
                                  GROUP BY `ORDER date`.`country`,
                                           year(`ORDER date`.`shipdate`) ) t1
                JOIN
                         (
                                  SELECT   `ORDER date`.`country`        AS `country`,
                                           sum(`ORDER date`.`sum sales`) AS `x_measure__3`
                                  FROM     `kyvos_dataaccuracycubes`.`ORDER date` `ORDER date`
                                  GROUP BY `ORDER date`.`country` ) t2
                ON       (
                                  `t1`.`country` = `t2`.`country`)
                GROUP BY `t1`.`yr_shipdate_ok` ) t3
ON     (
              `t0`.`yr_shipdate_ok` = `t3`.`yr_shipdate_ok`)
		
/* Example of how to use multiple joins and aggregated SQL query */		



/* Case#6  */
SELECT Count(`product-sales_detail_50gb`.`order_quantity`) AS
       `cnt_order_quantity_ok`,
       `product-sales_detail_50gb`.`product_category`      AS `product_category`
       ,
       `product-sales_detail_50gb`.`product_name`          AS
       `product_name`
FROM   `kyvos_browsing_automationcubes`.`product-sales_detail_50gb`
       `Product-Sales_Detail_50GB`
WHERE  ( ( `product-sales_detail_50gb`.`product_name` IN ( 'Tripod Stand' ) )
          OR ( `product-sales_detail_50gb`.`product_name` IS NULL )
          OR ( ( `product-sales_detail_50gb`.`product_name` >= '32 inch' )
               AND ( `product-sales_detail_50gb`.`product_name` <= '46 inch' ) )
          OR ( ( `product-sales_detail_50gb`.`product_name` >=
                 'Connector Cables and Ax.'
               )
               AND ( `product-sales_detail_50gb`.`product_name` <=
                     'Lens Attachment' )
             )
          OR ( ( `product-sales_detail_50gb`.`product_name` >= 'Lens Kit' )
               AND ( `product-sales_detail_50gb`.`product_name` <= 'TV Remote' )
             ) )
GROUP  BY `product-sales_detail_50gb`.`product_category`,
          `product-sales_detail_50gb`.`product_name`
UNION
SELECT Avg(`product-sales_detail_50gb`.`discount`)    AS `avg_discount_ok`,
       `product-sales_detail_50gb`.`product_category` AS `product_category`,
       `product-sales_detail_50gb`.`product_name`     AS `product_name`
FROM   `kyvos_browsing_automationcubes`.`product-sales_detail_50gb`
       `Product-Sales_Detail_50GB`
WHERE  ( ( `product-sales_detail_50gb`.`product_name` IN ( 'Tripod Stand' ) )
          OR ( `product-sales_detail_50gb`.`product_name` IS NULL )
          OR ( ( `product-sales_detail_50gb`.`product_name` >= '32 inch' )
               AND ( `product-sales_detail_50gb`.`product_name` <= '46 inch' ) )
          OR ( ( `product-sales_detail_50gb`.`product_name` >=
                 'Connector Cables and Ax.'
               )
               AND ( `product-sales_detail_50gb`.`product_name` <=
                     'Lens Attachment' )
             )
          OR ( ( `product-sales_detail_50gb`.`product_name` >= 'Lens Kit' )
               AND ( `product-sales_detail_50gb`.`product_name` <= 'TV Remote' )
             ) )
GROUP  BY `product-sales_detail_50gb`.`product_category`,
          `product-sales_detail_50gb`.`product_name`;

/* Example of how to use UNION/UNION ALL and and aggregated SQL query*/			  


/* Case#7  */  
SELECT   `country`,
         count(*)
FROM     `kyvos_browsing_automationcubes`.`ORDER date`
WHERE    country IN("Albania",
                    "Azerbaijan",
                    "Cuba",
                    "US",
                    "India")
GROUP BY `country`
EXCEPT
SELECT   `country`,
         count(*)
FROM     `kyvos_browsing_automationcubes`.`ORDER date`
WHERE    country IN("US",
                    "India")
GROUP BY `country`
EXCEPT
SELECT   `country`,
         count(*)
FROM     `kyvos_browsing_automationcubes`.`ORDER date`
WHERE    `country` ='US'
GROUP BY `country`;

/* Example of how to use Except clause */	



/* Case#8  */
SELECT `a`.`country`,
       `a`.`state`,
       `a`.`region`,
       `a`.`city`,
       Sum(`a`.`gbp_rt`)
FROM   `kyvos_browsing_automationcubes`.`order date` `a`
WHERE  EXISTS (SELECT `b`.`region`
               FROM   `kyvos_browsing_automationcubes`.`order date` `b`
               WHERE  `a`.`country` = `b`.`country`
                      AND `a`.`country` = 'Afghanistan'
               GROUP  BY `b`.`region`)
GROUP  BY `a`.`country`,
          `a`.`state`,
          `a`.`city`,
          `a`.`region`;

SELECT `a`.`country`,
       `a`.`state`,
       `a`.`region`,
       `a`.`city`,
       Sum(`a`.`gbp_rt`)
FROM   `kyvos_browsing_automationcubes`.`order date` `a`
WHERE  NOT EXISTS (SELECT `b`.`region`
                   FROM   `kyvos_browsing_automationcubes`.`order date` `b`
                   WHERE  `b`.`country` = 'US'
                   GROUP  BY `b`.`region`)
GROUP  BY `a`.`country`,
          `a`.`state`,
          `a`.`city`,
          `a`.`region`;
		  
/* Example of how to use Exists clause */	


/* Case#9  */
SELECT `country`,
       Count(*)
FROM   `kyvos_browsing_automationcubes`.`order date`
WHERE  country IN( "US", "India" )
GROUP  BY `country`
INTERSECT
SELECT `country`,
       Count(*)
FROM   `kyvos_browsing_automationcubes`.`order date`
WHERE  country IN(  "US", "India" )
GROUP  BY `country` 
/* Example of how to use intersect clause */

Types of queries that do not work with Kyvos.

Examples of non-supported queries

/*============================================================================
################################################################################

SQL Query examples for not working cases

################################################################################
==============================================================================*/

/* Case#1  */
SELECT *
FROM   `kyvos_browsing_automationcubes`.`order date` `Order Date`

/* Note: Could not execute query as query is not supported on aggregated cube data. */


/* Case#2  */
SELECT Max(`order date`.`category`) AS `category`
FROM   `kyvos_browsing_automationcubes`.`order date` `Order Date`
ORDER  BY `order date`.`category`

/* Note: Could not execute query as query is not supported on aggregated cube data. GROUP BY `category` is not used on used dimension .you cannot use a measure as a dimension and dimension fields as measures unless they are defined in the cube separately. */


/* Case#3  */
SELECT Sum(`order date`.`category`) AS `category`
FROM   `kyvos_browsing_automationcubes`.`order date` `Order Date`
GROUP  BY `order date`.`category`
ORDER  BY `order date`.`category`

/* Note: Summary function 'Sum' is not supported on dimension column:[category].*/

/* Case#4  */
SELECT Sum(( CASE
               WHEN ( `retail_distribution_cube`.`price status` = 'Markdown' )
             THEN 1
               ELSE -1
             end ))                                   AS
       `temp_tc___2981869157__0_`,
       Min(`retail_distribution_cube`.`price status`) AS
       `temp_tc___4115986948__0_`,
       `retail_distribution_cube`.`price status`      AS `price_status`
FROM   `kyvos_browsing_automationcubes`.`retail_distribution_cube`
       `Retail_Distribution_Cube`
GROUP  BY `retail_distribution_cube`.`price status` 

 /* Note: Constant value(1) other than zero is not supported in expression. */

/* Case#5  */
SELECT COUNT(DATEDIFF(TO_DATE(CAST(DATE_ADD(NEXT_DAY(CAST(CONCAT(DATE_ADD(CAST(`order_date`.`orderdate` AS TIMESTAMP), CAST(7 * -1 AS INT)), SUBSTR(CAST(CAST(`order_date`.`orderdate` AS TIMESTAMP) AS TIMESTAMP), 11)) AS TIMESTAMP),'SU'),-7) AS DATE)), TO_DATE(`order_date`.`orderdate`))) AS `temp_calculation_313844629194178560_10736926100`, SUM(DATEDIFF(TO_DATE(CAST(DATE_ADD(NEXT_DAY(CAST(CONCAT(DATE_ADD(CAST(`order_date`.`orderdate` AS TIMESTAMP), CAST(7 * -1 AS INT)), SUBSTR(CAST(CAST(`order_date`.`orderdate` AS TIMESTAMP) AS TIMESTAMP), 11)) AS TIMESTAMP),'SU'),-7) AS DATE)), TO_DATE(`order_date`.`orderdate`))) AS `temp_calculation_313844629194178560_37800143780`, COUNT(1) AS `x_alias_0` FROM `tableautest`.`order date` `order_date` HAVING (COUNT(1) > 0)

/* Note: Summary function 'Count' is not supported on dimension column:[orderdate].*/

/* Case#6  */
SELECT a11.country          country,
       Count(a11.citycount) citycount
FROM   `Kyvos_Browsing_AutomationCubes`.`Order Date` orderdate
       JOIN (SELECT porderdate.country country
             FROM   (SELECT orderdate.country
                            country,
                            Rank ()
                              OVER(
                                ORDER BY Count(orderdate.citycount) DESC)
                            citycount
                     FROM   `Kyvos_Browsing_AutomationCubes`.`Order Date` 
                     GROUP  BY orderdate.country) porderdate
             WHERE  ( porderdate.citycount <= 10 )) pa12
         ON ( orderdate.country = pa12.country )
GROUP  BY orderdate.country 

/* Note: Window functions (Rank) are not supported.*/

/* Case#7  */
SELECT Min(Year(`order_date1`.`orderdate`)) AS `temp_tc___561953214__0_`,
       Count(1)                             AS `x__alias__1`
FROM   `kyvos_dataaccuracycubes`.`order date` `order_date1`
       FULL OUTER JOIN (SELECT `order_date1`.`productname`    AS `productname`,
                    Max(`order_date1`.`max sales`) AS `x__alias__0`
             FROM   `kyvos_dataaccuracycubes`.`order date` `order_date1`
             WHERE  ( NOT ( `order_date1`.`productname` IS NULL ) )
             GROUP  BY `order_date1`.`productname`
             ORDER  BY `x__alias__0` ASC,
                       `productname` ASC
             LIMIT  100) `t0`
         ON ( `order_date1`.`productname` = `t0`.`productname` )
HAVING ( Count(1) > 0 ) 

/* Note: Could not execute query as query with 'FullOuter' join is not supported on aggregated cube data. */

/* Case#8  */
SELECT Min(Year(`order_date1`.`orderdate`)) AS `temp_tc___561953214__0_`,
       Count(1)                             AS `x__alias__1`
FROM   `kyvos_dataaccuracycubes`.`order date` `order_date1`
        RIGHT OUTER JOIN (SELECT `order_date1`.`productname`    AS `productname`,
                    Max(`order_date1`.`max sales`) AS `x__alias__0`
             FROM   `kyvos_dataaccuracycubes`.`order date` `order_date1`
             WHERE  ( NOT ( `order_date1`.`productname` IS NULL ) )
             GROUP  BY `order_date1`.`productname`
             ORDER  BY `x__alias__0` ASC,
                       `productname` ASC
             LIMIT  100) `t0`
         ON ( `order_date1`.`productname` = `t0`.`productname` )
HAVING ( Count(1) > 0 )

/* Note: Could not execute query as query with 'RightOuter' join is not supported on aggregated cube data. */


/* Case#9  */
SELECT Min(Year(`customer`.`orderdate`)) AS `temp_tc___561953214__0_`,
       Count(1)                             AS `x__alias__1`
FROM   `kyvos_dataaccuracycubes`.`order date` `customer`
       LEFT JOIN (SELECT `customer`.`productname`    AS `productname`,
                    Max(`customer`.`max sales`) AS `x__alias__0`
             FROM   `kyvos_dataaccuracycubes`.`customer`  `customer`
             WHERE  ( NOT ( `customer`.`productname` IS NULL ) )
             GROUP  BY `customer`.`productname`
             ORDER  BY `x__alias__0` ASC,
                       `productname` ASC
             LIMIT  100) `t0`
         ON ( `customer`.`productname` = `t0`.`productname` )
HAVING ( Count(1) > 0 ) 

/* Note: SQL query over multiple cubes is not supported. */