1.3 sql数据集

创建sql数据集

在数据集管理菜单上，点击“+”按钮，在弹出的数据集下拉菜单中，选择“SQL数据集”，选择想要查询的业务数据源，如下图所示： 输入SQL语句：

select p.PRODUCT_NAME,o.shipcountry,o.shipregion,o.shipcity, o.FREIGHT from product p,ORDERS o where p.PRODUCT_ID=o.EMPLOYEEID

点击下图红色方框中的“解析”按钮，对SQL进行解析。如下图所示：

建议sql书写方法

在使用sql数据集时，不免要使用到参数引用，实现更灵活的条件控制，而在写入参数时因为使用不当，经常出现报错以及得不到想要的效果等，现提供以下几种常用sql书写建议。

• 参数为空

  UniEAP Report引擎在处理空参数的逻辑是将该参数所在的条件表达式替换为一个始终为真的表达式,如1=1,但在一些较为复杂的SQL中很难在程序中确定参数所在的完整表达式,当传入参数为空时就可能会出现替换出错的情况,导致SQL无法执行。

  针对这种情况的存在报表提供两种标识符---{# #}和{@ @},手动来标识出一个参数所在的完整条件表达式,在报表引擎发现参数为空时就可以准确的找出这个表达式,并对其进行替换。

  简单来说，参数为空时{#... #}在实际运行中被替换为“1=1”，{@...@}在实现运行中被替换为“”。使用时，可以根据此原则来写SQL语句。下面用例子说明。

  

  例如上图，如果shipregion为空,shipcountry参数为’中国’时,实际运行的sql语句为:

  select distinct shipcity,shipregion,shipcountry from orders where shipcountry='中国' and 1=1
  

  又例如：

  select * from employee where {# $a=1 #} and {# birthday < to_date($b,’yyyy-MM-dd’) #} {@group by {@ $employeeId,@} {@$name@}@}
  

  当所有参数都为空时，这个SQL就变成了：

  select * from employee where 1=1 and 1=1。
  

  【注意】 请大家在使用参数的时候尽量使用{##}或{@@}将参数表达式扩起来,特别是当参数可能为空的时候。

• in类型参数

  假设将参数in_param赋值为1,2,3,4,5 ,数据集SQL写法:

  Select * from product p where {# p.product_id in ($in_param) #}
  

  通过报表引擎处理以后实际运行的SQL语句为:

  Select * from product p where p.product_id in (1,2,3,4,5)
  

  假设将参数str_param赋值为：榨汁机:;茶几:;冲锋衣，数据集SQL写法:

  Select * from product p where {# p.product_name in ($str_param) #}
  

  通过报表引擎处理以后实际运行的SQL语句为:

  Select * from product p where p.product_name in (‘榨汁机’, ‘茶几’, ‘冲锋衣’)
  

  【注意】in类型的参数,数值类型设置参类型为字段，字符串类型设置参数类型为字符串。

• like类型参数

  将参数的数据类型设置为字段, 编写SQL：

  select * from product where {# name like '%$paramname%' #}
  

  如果数据集SQL中需要作为条件比较的字段为数值类型，如下：

  select * from product where {# name like %$paramname% #}
  

  【注意】字段类型参数的特点在于拼接数据集SQL的时候不会在参数两端自动添加引号，字符串类型的参数在拼接SQL的时候会在字符串两端自动添加引号。

• date类型参数

  因为报表工具中所定义的date数据类型与数据库中的日期数据类型是不完全一致的，以访问oracle数据库取数据为例，可以将参数的数据类型定义为字符串，编写SQL：

  select * from orders where orderdate>to_date($orderdate,'yyyy-mm-dd')
  

sql解析报错排查方法

设计器解析是为了能够获取sql中查询的所有字段信息，而不是执行sql，所以即便存在解析不通过的情况，也并不代表报表的定义有问题，大部分时因为参数使用不当造成。

1.设计器解析出错：

字段信息是和条件无关的，在过于复杂无法解析通过时，可以将带参数的sql替换成具体条件，然后解析即可成功，解析到字段后，再替换回参数。这个过程不影响执行结果的。

比如select * from product where type = $type，解析的时候应该先把where type = $type替换掉。

2.引擎解析出错：

这种情况仅发生在sql中存在参数，但是参数使用有问题，具体排查方式请使用上一节所对应的参数使用方法说明。

sql支持报表公式

需求描述

在创建数据集时，需要输入SQL语句，一般的数据集通过SQL语句就可以建立成功，但是在某些情况下，使用固定的SQL语句不能够动态的去获取数据，或者某些情况下需要根据参数去生成不同的数据集，这些功能在目前的版本中是不支持的。而如果在语句中能够添加报表的一些公式，例如逻辑判断类的公式（case公式等）和字符串类的公式（substring公式等），再把参数代入公式进行处理，就可以解决这些场景下的需求。

功能描述

当在SQL语句中需要写公式动态的去判断一些值时，需要以如下形式输入公式：

{#=公式体#}

实例如下：

Select * from product where {#=case($type=1,'price<100','price>1000')#}

公式的返回值是一个字符串，在SQL语句中需要动态返回一些值时可以使用公式，但是要保证其返回的字符串和原SQL语句拼在一起时符合SQL语句语法，上述实例中，如果参数type=1，就等同于执行了如下SQL语句：

Select * from product where price<100

同理，如果参数type不为1，就等同于执行了如下SQL语句：

Select * from product where price>1000

如果公式返回的字符串与原SQL语句组合在一起时不符合SQL语法，就会发生错误。所以要保证语句符合语法。再看如下实例：

{#=case($abc='1','SELECT * FROM(SELECT last_name as name FROM crm_users)t ','SELECT * FROM(SELECT description as name FROM crm_daily_daily_page)t')#}where t.name in {'张三','李四','王五'}

该实例为动态获取数据集的列，如果输入参数type为1，数据集会执行如下sql语句：

'SELECT * FROM(SELECT last_name as name FROM crm_users)t where t.name in {'张三','李四','王五'}'

如果输入参数不为1，就会执行另外一句SQL语句，这样就实现了动态的获取数据集的列。

由于数据集解析功能还没有完成，所以当需要使用SQL公式时，需要先把用到的列名解析出来，比如上述实例中，需要先在数据集中写一条可以执行的SQL语句，解析出列名，然后输入应该使用的公式和SQL语句，直接点确定，不需要再次解析。

注意事项

1.在SQL语句中写入的公式返回值均为字符串。

{#=case($type=1,'1','2')#}{#=case($type=1,1,2)#}

上述两个公式的返回值均为字符串，也就是说上述两种写法的结果相同。

Select * from table where type={#=case($type=1,1,2)#}

当以上述形式写公式时一定要注意，如果type是数值类型可以这么写，但是如果type不是数值类型时，需要在公式外层加上单引号。如下：

Select * from table where type='{#=case($type=1,1,2)#}'

2.如果公式中有嵌套的单引号，需要在单引号前加入反斜杠\，如：

Select * from table {#=case($type=1, 'where type=\'a\'', 'where type=\'b\'')#}

如果在嵌套的单引号中没有使用\'，公式匹配单引号就会出现问题，报表就无法生成。

3.记得在公式前加”=”，如果没有写等号，就会变成非必填参数的形式{##}，就会将其替换为1=1，公式就会失去作用。

4.建议在复杂情况下使用公式而不是大量使用{##}{@@}这样的非必填参数形式。需要对参数是否被赋值判断时，可以使用nvl()公式。

5.公式区域不支持嵌套匹配，需要写公式时在{#=#}内部输入公式即可，不需要在其内部再嵌套，如{#={#=#}#}这种形式。