LIB CONNECT TO 'CustomSQL:Snowflake_example.com';
Section DirectQuery;
C:
Select C_NATIONKEY, Sum(C_ACCTBAL) as C_SUM from tpch1.customer group by C_NATIONKEY;
R:
SELECT R_REGIONKEY, R_NAME from tpch1.region;
N:
SELECT N_REGIONKEY, N_NAME, N_NATIONKEY from tpch1.nation;
Create Relationship Between R, N Matching R_REGIONKEY With N_REGIONKEY;
Create Relationship
Between Outer Joined N, Inner Joined C On
(coalesce([N_NATIONKEY], -1) = coalesce([C_NATIONKEY], -1));
示例：向使用 数据模型管理器 制作的数据模型添加具有自定义 SQL 的表
在此示例中，首先使用在 数据模型管理器 中创建的 DirectQueryModel.main 对象中的内容构建 Direct Query 数据模型。然后，将一个附加表 C（具有字段 C_CUSTKEY 和 C_ACCTBAL）添加到数据模型。在 ORDERS 中的字段 O_CUSTKEY 与 C 中的字段 C_CUSTKEY 之间建立关系。
LIB CONNECT TO 'CustomSQL:Snowflake_example.com';
// DMM created model portion includes Orders table.
IMPORT LIVE 'ModelName@obj://DirectQueryModel.Main';
Section DirectQuery;
C: Select C_CUSTKEY, C_ACCTBAL from tpch1.customer;
Create Relationship Between ORDERS, C on (O_CUSTKEY = C_CUSTKEY);
示例：使用自定义 SQL 表构建 Direct Query 数据模型
在此示例中，使用自定义 SQL 表 C 构建 Direct Query 数据模型，其中重命名了字段 C_KEY 和 C_BAL。接下来，IMPORT LIVE 从 DirectQueryModel.main 添加在 数据模型管理器 中创建的数据模型。该数据模型包含表 Orders，并且在该模型中，Orders 和 Customer 之间存在现有关系。然后，删除 Customers 和 Orders 之间的现有关系，并在 Orders 和 C 之间定义新关系。
LIB CONNECT TO 'CustomSQL:Snowflake_example.com';
Section DirectQuery;
C: Select C_CUSTKEY as C_KEY, C_ACCTBAL as C_BAL from tpch1.customer;
// DMM created model portion includes Orders table.
IMPORT LIVE 'ModelName@obj://DirectQueryModel.Main';
// This relationship was defined using DMM between ORDERS and CUSTOMER, but I want to define my own.
Drop Relationship Between ORDERS, CUSTOMER;
Create Relationship Between ORDERS, C on (O_CUSTKEY = C_KEY);
LIB CONNECT TO 'CustomSQL:Snowflake_example.com';
Section DirectQuery;
LET MULT=100;
T1: SELECT PS_AVAILQTY AS C, SUM(PS_SUPPLYCOST) * $(MULT) AS S FROM "TPCH.01"."PARTSUPP" GROUP BY C;
IMPORT LIVE 'ModelName@obj://DirectQueryModel.Main';
section DirectQuery;
let Aggr1 = 1;
let Aggr2 = 0;
[GROUPS]:
SELECT 0 as GROUP_CODE, 'NONE' as "GROUP"
UNION ALL
SELECT 1 as GROUP_CODE, 'ORDERSTATUS' as "GROUP"
UNION ALL
SELECT 2 as GROUP_CODE, 'ORDERPRIORITY' as "GROUP"
;
[ORDERS_AGGREGATIONS]:
SELECT 'BY ORDERSTATUS' as "GROUP_NAME",
"O_ORDERSTATUS" as "ORDERS_GROUP",
sum("O_TOTALPRICE") as "ORDERS_GROUP_TOTAL_PRICE"
FROM "TEST1"."TPCH.01"."ORDERS"
WHERE '$(=min({<GROUP_CODE={0,1}>}[GROUP_CODE]))' = '1'
GROUP BY 1,2
UNION ALL
SELECT 'BY ORDERPRIORITY' as "GROUP_NAME",
"O_ORDERPRIORITY" as "ORDERS_GROUP",
sum("O_TOTALPRICE") as "ORDERS_GROUP_TOTAL_PRICE"
FROM "TEST1"."TPCH.01"."ORDERS"
WHERE '$(=min({<GROUP_CODE={0,2}>}[GROUP_CODE]))' = '2'
GROUP BY 1,2;
;
[ORDERS_AGGREGATIONS_VARS]:
SELECT 'BY ORDERSTATUS' as "GROUP_NAME_V",
"O_ORDERSTATUS" as "ORDERS_GROUP_V",
sum("O_TOTALPRICE") as "ORDERS_GROUP_TOTAL_PRICE_V"
FROM "TEST1"."TPCH.01"."ORDERS"
WHERE $(=Aggr1) = 1
GROUP BY 1,2
UNION ALL
SELECT 'BY ORDERPRIORITY' as "GROUP_NAME_V",
"O_ORDERPRIORITY" as "ORDERS_GROUP_V",
sum("O_TOTALPRICE") as "ORDERS_GROUP_TOTAL_PRICE_V"
FROM "TEST1"."TPCH.01"."ORDERS"
WHERE $(=Aggr2) = 1
GROUP BY 1,2;
;
示例：在自定义 SQL 表达式中使用函数
以下自定义 SQL 语句演示了 $(=…) 引擎表达式的简单用法，该表达式在自定义 SQL 中执行时会被展开。此表达式根据应用程序会话的用户信息检索远程数据库行。当具有电子邮件地址 'user1@qlik.com' 的 Direct Query 应用程序会话用户执行图表、查询或选择时，从远程 SQL 数据库动态检索数据行将被过滤，以仅匹配 C_NAME field 包含值 'Customer#000000007' 的行。否则，如果 Direct Query 应用程序的会话用户的电子邮件不是 'user1@qlik.com'，则仅检索并显示 C_NAME 字段包含值 'Customer#000000009' 的行。
T8: SELECT * FROM mydb.tpch01.customer WHERE mydb.tpch01.customer.C_NAME = '$(=if(GetUserAttr('userEmail') = 'user1@qlik.com', 'Customer#000000007','Customer#000000009'))';
