sql - 如何在 Jasper Reports 中使用 postgreSQL 交叉表查询?
问题描述
我正在尝试使用带有参数的 PostgreSQL 交叉表查询在 Jasper Reports 中制作报告。
当我在 pgAdmin 4 中使用静态值执行交叉表查询时,它正在工作。当我将它复制到报表生成器的查询对话框中并添加报表参数时,它不起作用。
版本:PostgreSQL 服务器 12 TIBCO Jaspersoft® Studio 6.6.0
这是代码:
在 pgAdmin 4 中工作的交叉表查询:
select
cedula,
apellidos,
nombres,
aporteIndividual,
aporteAdicional,
aporteAdicional5,
aporteSeguro,
aporteIndividual::numeric + aporteAdicional::numeric +
aporteAdicional5::numeric + aporteSeguro::numeric as total
from (
SELECT * FROM crosstab
(
'select
p.id,
a.id,
COALESCE (p.primer_apellido, '''') || '' '' ||
COALESCE (p.segundo_apellido, '''') as apellidos,
COALESCE (p.primer_nombre, '''') || '' '' ||
COALESCE (p.segundo_nombre, '''') as nombres,
p.numero_documento,
ad.tipo_aporte,
ad.valor
from
sch_participantes.participante as p,
sch_participantes.aportes as a,
sch_participantes.aporte_detalles as ad
where
p.id = a.id_participe
and a.id = ad.id_aporte
and p.filial = 1084
and p.estado = 1
and a.mes = 1
and a.anio = 2020
order by p.primer_apellido',
'select id from sch_participantes.tipo_aporte ta
order by ta.id'
)
AS
(
id_participe integer,
id_aporte integer,
apellidos text,
nombres text,
cedula text,
aporteIndividual text,
aporteAdicional text,
aporteAdicional5 text,
aporteSeguro text
)
union all
select
p.id,
null,
COALESCE (p.primer_apellido, '') || ' ' ||
COALESCE (p.segundo_apellido, '') as apellidos,
COALESCE (p.primer_nombre, '') || ' ' ||
COALESCE (p.segundo_nombre, '') as nombres,
p.numero_documento,
null,
null,
null,
null
from
sch_participantes.participante as p
where
p.id not in
(
select
a.id_participe
from
sch_participantes.aportes as a
where
a.mes = 1
and a.anio = 2020
)
and p.filial = 1084
and p.estado = 1
) as todo
order by todo.apellidos
和报告代码:
<!-- Created with Jaspersoft Studio version 6.6.0.final using JasperReports Library version 6.6.0 -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="aportes-general" pageWidth="595" pageHeight="842" columnWidth="535" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="da69e8ed-7cd6-42e2-9d96-8f8d86be6137">
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<property name="com.jaspersoft.studio.data.sql.SQLQueryDesigner.sash.w1" value="205"/>
<property name="com.jaspersoft.studio.data.sql.SQLQueryDesigner.sash.w2" value="786"/>
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="erp_pruebas_michel"/>
<property name="com.jaspersoft.studio.property.dataset.dialog.DatasetDialog.sash.w1" value="666"/>
<property name="com.jaspersoft.studio.property.dataset.dialog.DatasetDialog.sash.w2" value="320"/>
<parameter name="filial" class="java.lang.Integer" evaluationTime="Early">
<defaultValueExpression><![CDATA[1084]]></defaultValueExpression>
</parameter>
<parameter name="año" class="java.lang.Integer" evaluationTime="Early">
<defaultValueExpression><![CDATA[2020]]></defaultValueExpression>
</parameter>
<parameter name="mes" class="java.lang.Integer" evaluationTime="Early">
<defaultValueExpression><![CDATA[1]]></defaultValueExpression>
</parameter>
<parameter name="crosstab_query" class="java.lang.String" evaluationTime="Early">
<defaultValueExpression><![CDATA["'select" +
" p.id," +
" a.id," +
" COALESCE (p.primer_apellido, '''') || '' '' ||" +
" COALESCE (p.segundo_apellido, '''') as apellidos," +
" COALESCE (p.primer_nombre, '''') || '' '' ||" +
" COALESCE (p.segundo_nombre, '''') as nombres," +
" p.numero_documento, " +
" ad.tipo_aporte," +
" ad.valor" +
" from" +
" sch_participantes.participante as p," +
" sch_participantes.aportes as a," +
" sch_participantes.aporte_detalles as ad" +
" where" +
" p.id = a.id_participe" +
" and a.id = ad.id_aporte" +
" and p.filial = " + $P{filial} +
" and p.estado = 1" +
" and a.mes = " + $P{mes} +
" and a.anio = " + $P{año} +
" order by p.primer_apellido'," +
" 'select id from sch_participantes.tipo_aporte ta" +
" order by ta.id'"]]></defaultValueExpression>
</parameter>
<queryString language="SQL">
<![CDATA[select
cedula,
apellidos,
nombres,
aporteIndividual,
aporteAdicional,
aporteAdicional5,
aporteSeguro,
aporteIndividual::numeric + aporteAdicional::numeric +
aporteAdicional5::numeric + aporteSeguro::numeric as total
from (
SELECT * FROM crosstab ($P{crosstab_query})
AS
(
id_participe integer,
id_aporte integer,
apellidos text,
nombres text,
cedula text,
aporteIndividual text,
aporteAdicional text,
aporteAdicional5 text,
aporteSeguro text
)
union all
select
p.id,
null,
COALESCE (p.primer_apellido, '') || ' ' ||
COALESCE (p.segundo_apellido, '') as apellidos,
COALESCE (p.primer_nombre, '') || ' ' ||
COALESCE (p.segundo_nombre, '') as nombres,
p.numero_documento,
null,
null,
null,
null
from
sch_participantes.participante as p
where
p.id not in
(
select
a.id_participe
from
sch_participantes.aportes as a
where
a.mes = $P{mes}
and a.anio = $P{año}
)
and p.filial = $P{filial}
and p.estado = 1
) as todo
order by todo.apellidos]]>
</queryString>
<field name="cedula" class="java.lang.String"/>
<field name="apellidos" class="java.lang.String"/>
<field name="nombres" class="java.lang.String"/>
<field name="aporteIndividual" class="java.math.BigDecimal"/>
<field name="aporteAdicional" class="java.math.BigDecimal"/>
<field name="aporteAdicional5" class="java.math.BigDecimal"/>
<field name="aporteSeguro" class="java.math.BigDecimal"/>
<field name="total" class="java.math.BigDecimal"/>
<background>
<band/>
</background>
<title>
<band height="72">
<frame>
<reportElement mode="Opaque" x="-20" y="-20" width="595" height="92" backcolor="#006699" uuid="0dc47f7a-395a-4b8d-87b4-e927fca8fa61"/>
<staticText>
<reportElement x="20" y="20" width="410" height="30" forecolor="#FFFFFF" uuid="3c6488eb-bfbc-4fb6-b9b2-3637a1cf0c9d"/>
<textElement>
<font size="19" isBold="true"/>
</textElement>
<text><![CDATA[Reporte general de aportes]]></text>
</staticText>
<textField>
<reportElement x="20" y="50" width="300" height="30" forecolor="#FFFFFF" uuid="4a131c99-d637-4199-9335-493f0e7a7750"/>
<textElement>
<font size="14"/>
</textElement>
<textFieldExpression><![CDATA["Período:" + $P{mes} + "-" + $P{año}]]></textFieldExpression>
</textField>
</frame>
</band>
</title>
<pageHeader>
<band height="13"/>
</pageHeader>
<columnHeader>
<band height="18">
<property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.spreadsheet.SpreadsheetLayout"/>
<staticText>
<reportElement x="0" y="0" width="50" height="18" uuid="4d23b483-a6f5-44f1-b4b0-e0c92d8736b0">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="fdf238bc-d6fa-41f7-a846-a09be881e2a8"/>
</reportElement>
<text><![CDATA[cedula]]></text>
</staticText>
<staticText>
<reportElement x="50" y="0" width="130" height="18" uuid="b2c90d08-f6a7-441c-92c3-3487aed4a04f">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="87c434fd-7660-4c3e-8ca4-97bb9499349b"/>
</reportElement>
<text><![CDATA[apellidos]]></text>
</staticText>
<staticText>
<reportElement x="180" y="0" width="120" height="18" uuid="4aca3646-0e0e-4253-83f3-7903cb60b048">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="289c8d74-6077-45d8-9373-c238a4598284"/>
</reportElement>
<text><![CDATA[nombres]]></text>
</staticText>
<staticText>
<reportElement x="300" y="0" width="50" height="18" uuid="3e268eea-8ecb-4fb1-8bfe-6f99f39caccb">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="8d1cedc0-4fd2-4f06-8725-b17630f08437"/>
</reportElement>
<text><![CDATA[aporteIndividual]]></text>
</staticText>
<staticText>
<reportElement x="350" y="0" width="50" height="18" uuid="5f5e3710-7eef-48c5-a962-6120bb9fa67b">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="fa8014b3-1b03-4443-bf80-167703b8f1cb"/>
</reportElement>
<text><![CDATA[aporteAdicional]]></text>
</staticText>
<staticText>
<reportElement x="400" y="0" width="50" height="18" uuid="00653733-6788-45b6-ab57-d37d12293a6d">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="d066fefc-d2fb-49b9-bde1-f706ea220cb9"/>
</reportElement>
<text><![CDATA[aporteAdicional5]]></text>
</staticText>
<staticText>
<reportElement x="450" y="0" width="50" height="18" uuid="cf37f752-5ba2-4950-8200-099943713cd7">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="e434b3c8-20bc-4631-b59b-a4939675978f"/>
</reportElement>
<text><![CDATA[aporteSeguro]]></text>
</staticText>
<staticText>
<reportElement x="500" y="0" width="50" height="18" uuid="623db75c-ff28-4aab-8fc3-190f479cd8c5">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="5cd0ec9d-c45b-4e7b-9fbc-2bb616521f0e"/>
</reportElement>
<text><![CDATA[total]]></text>
</staticText>
</band>
</columnHeader>
<detail>
<band height="30">
<property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.spreadsheet.SpreadsheetLayout"/>
<textField>
<reportElement x="0" y="0" width="50" height="30" uuid="2a352a63-f1fe-4a2e-bb74-937d2e3f335b">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="fdf238bc-d6fa-41f7-a846-a09be881e2a8"/>
</reportElement>
<textFieldExpression><![CDATA[$F{cedula}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="50" y="0" width="130" height="30" uuid="970896d3-426b-41f2-b097-784d83a2e172">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="87c434fd-7660-4c3e-8ca4-97bb9499349b"/>
</reportElement>
<textFieldExpression><![CDATA[$F{apellidos}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="180" y="0" width="120" height="30" uuid="0c2e4d66-7faa-4baa-a77a-dfbe60dfe466">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="289c8d74-6077-45d8-9373-c238a4598284"/>
</reportElement>
<textFieldExpression><![CDATA[$F{nombres}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="300" y="0" width="50" height="30" uuid="0c6e4a8e-5b0e-4a07-9d73-282781817c95">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="8d1cedc0-4fd2-4f06-8725-b17630f08437"/>
</reportElement>
<textFieldExpression><![CDATA[$F{aporteIndividual}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="350" y="0" width="50" height="30" uuid="d2af8ebf-cbc9-407e-8dbe-f1af49525966">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="fa8014b3-1b03-4443-bf80-167703b8f1cb"/>
</reportElement>
<textFieldExpression><![CDATA[$F{aporteAdicional}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="400" y="0" width="50" height="30" uuid="a78a3c65-c069-447d-a3ae-84087e3f4977">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="d066fefc-d2fb-49b9-bde1-f706ea220cb9"/>
</reportElement>
<textFieldExpression><![CDATA[$F{aporteAdicional5}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="450" y="0" width="50" height="30" uuid="5f22b066-53b8-47dd-b2c9-bb026eb69dae">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="e434b3c8-20bc-4631-b59b-a4939675978f"/>
</reportElement>
<textFieldExpression><![CDATA[$F{aporteSeguro}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="500" y="0" width="50" height="30" uuid="1f698fc0-da4d-4aa9-b21e-6ba74dfe8df0">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="5cd0ec9d-c45b-4e7b-9fbc-2bb616521f0e"/>
</reportElement>
<textFieldExpression><![CDATA[$F{total}]]></textFieldExpression>
</textField>
</band>
</detail>
<columnFooter>
<band/>
</columnFooter>
<pageFooter>
<band height="17">
<textField>
<reportElement mode="Opaque" x="0" y="4" width="515" height="13" backcolor="#E6E6E6" uuid="0b71f3fa-21ce-4aa4-8fbc-84c015c403e3"/>
<textElement textAlignment="Right"/>
<textFieldExpression><![CDATA["Page "+$V{PAGE_NUMBER}+" of"]]></textFieldExpression>
</textField>
<textField evaluationTime="Report">
<reportElement mode="Opaque" x="515" y="4" width="40" height="13" backcolor="#E6E6E6" uuid="837ccbf7-57ea-4894-a13f-415d2472630f"/>
<textFieldExpression><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression>
</textField>
<textField pattern="EEEEE dd MMMMM yyyy">
<reportElement x="0" y="4" width="100" height="13" uuid="ae716933-1e7e-4a7c-b470-d58ad1afcd72"/>
<textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression>
</textField>
</band>
</pageFooter>
<summary>
<band height="47">
<staticText>
<reportElement x="270" y="0" width="100" height="30" uuid="5042bbb1-6dc0-4b69-a4f6-49f75dc42a25"/>
<text><![CDATA[Total Aportes]]></text>
</staticText>
</band>
</summary>
</jasperReport>
当我执行预览时,我得到这个语法错误:
引起:org.postgresql.util.PSQLException: ERROR: error de sintaxis en o cerca de «'select p.id, a.id, COALESCE (p.primer_apellido, '''') || '''' || COALESCE (p.segundo_apellido, '''') as apellidos, COALESCE (p.primer_nombre, '''') || '''' || COALESCE (p.segundo_nombre, '''') 作为名词,p.numero_documento,
ad.tipo_aporte,ad.valor 来自 sch_participantes.participantes 作为 p,sch_participantes.aportes 作为 a,
sch_participantes.aporte_detalles 作为 ad 其中 p.id = a。 id_participe 和 a.id = ad.id_aporte 和 p.filial = 1084 和 p.estado = 1
和 a.mes = 1 和 a.anio = 2020 由 p.primer_apellido'» 在 org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) 在 org.postgresql.core.v3.QueryExecutorImpl.processResults 订购(QueryExecutorImpl.java:1835) 在 org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) 在 org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500) 在 org.postgresql.jdbc2。 AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:310) .. . 6 更多
出于测试目的,我将 SQL 查询替换为固定的交叉表内部查询,如下所示:
<queryString language="SQL">
<![CDATA[select
cedula,
apellidos,
nombres,
aporteIndividual,
aporteAdicional,
aporteAdicional5,
aporteSeguro,
aporteIndividual::numeric + aporteAdicional::numeric +
aporteAdicional5::numeric + aporteSeguro::numeric as total
from (
SELECT * FROM crosstab (
'select
p.id,
a.id,
COALESCE (p.primer_apellido, '''') || '' '' ||
COALESCE (p.segundo_apellido, '''') as apellidos,
COALESCE (p.primer_nombre, '''') || '' '' ||
COALESCE (p.segundo_nombre, '''') as nombres,
p.numero_documento,
ad.tipo_aporte,
ad.valor
from
sch_participantes.participante as p,
sch_participantes.aportes as a,
sch_participantes.aporte_detalles as ad
where
p.id = a.id_participe
and a.id = ad.id_aporte
and p.filial = 1084
and p.estado = 1
and a.mes = 1
and a.anio = 2020
order by p.primer_apellido',
'select id from sch_participantes.tipo_aporte ta
order by ta.id'
)
AS
(
id_participe integer,
id_aporte integer,
apellidos text,
nombres text,
cedula text,
aporteIndividual text,
aporteAdicional text,
aporteAdicional5 text,
aporteSeguro text
)
union all
select
p.id,
null,
COALESCE (p.primer_apellido, '') || ' ' ||
COALESCE (p.segundo_apellido, '') as apellidos,
COALESCE (p.primer_nombre, '') || ' ' ||
COALESCE (p.segundo_nombre, '') as nombres,
p.numero_documento,
null,
null,
null,
null
from
sch_participantes.participante as p
where
p.id not in
(
select
a.id_participe
from
sch_participantes.aportes as a
where
a.mes = $P{mes}
and a.anio = $P{año}
)
and p.filial = $P{filial}
and p.estado = 1
) as todo
order by todo.apellidos]]>
</queryString>
并且报告预览执行良好。
任何建议都非常感谢。
解决方案
JasperReports 将$P{..}
报告查询转换为 JDBC 准备语句参数。也就是说,如果您WHERE column = $P{..}
在报表中查询,报表将WHERE column = ?
作为准备好的语句运行,报表参数值作为语句参数发送。
准备好的语句参数代表查询中的单个值,因此crosstab($P{..})
即使参数值包含逗号也不起作用。可能可行的是crosstab($P{first_query}, $P{second_query})
,只需注意准备好的语句参数作为原始值提供,因此您不应将值括在引号中或在值内转义引号。
但是,如果您只想在查询中以文本方式插入参数值,则可以使用$P!{..}
代替$P{..}
. 由于报表中的参数值看起来像一个查询片段,SELECT * FROM crosstab ($P!{crosstab_query})
因此在报表查询中使用应该可以工作。
推荐阅读
- rust - 如何使用 Cargo 运行项目示例?
- google-chrome - 我怎样才能让谷歌浏览器扩展打开一个隐身模式
- postgresql - npgsql - 它是否使用下面的 JDBC 驱动程序连接到 Postgres
- python - 如何摆脱列表中每行末尾的某些字符?
- javascript - How to use your js files that use node.js when node integration is disabled for better security?
- vue.js - 在同一级别上交替 v-for DOM 元素
- assembly - MPLab 模拟器 PIC16 汇编程序无限循环 - 程序没有结束
- python - 使用 list1 作为键和 list2 作为值创建字典
- java - 如何从我的 python 脚本中获取参数并从我的 java 项目中读取它们
- mongodb - 带有嵌套数组的 MongoDB 聚合查找