SELECT A.PCODE AS CODE FROM
SELECT CODE,PCODE FROM
sjzc_znsjfx.QRYF_DW_FACT_KMCODEOFCX
WHERE PACODE = '2019'
AND COMPID IN ('1608') AND LEAF = 0 ) a,
(SELECT PCODE
FROM
sjzc_znsjfx.QRYF_DW_FACT_KMCODEOFCX
WHERE PACODE = '2019'
AND COMPID IN ('1608') AND LEAF = 1
AND CAST(ACCOUNT_NOTE AS INT) BETWEEN '01' AND '12'
GROUP BY PCODE
HAVING SUM(TMONEYF) = 0) B
WHERE INSTR(B.PCODE,A.CODE);
SELECT CODE
FROM
sjzc_znsjfx.QRYF_DW_FACT_KMCODEOFCX
WHERE PACODE = '2019'
AND COMPID IN ('1608') AND LEAF = 1
AND CAST(ACCOUNT_NOTE AS INT) BETWEEN '01' AND '12'
GROUP BY CODE
HAVING SUM(TMONEYF) = 0;
其中 code和pcode都是varchar类型,在数据库中直接使用union就可以使用,但是在代码中就会报错
SELECT CODE,PCODE FROM
sjzc_znsjfx.QRYF_DW_FACT_KMCODEOFCX
WHERE PACODE = '2019'
AND COMPID IN ('1608') AND LEAF = 0 ) a,
(SELECT PCODE
FROM
sjzc_znsjfx.QRYF_DW_FACT_KMCODEOFCX
WHERE PACODE = '2019'
AND COMPID IN ('1608') AND LEAF = 1
AND CAST(ACCOUNT_NOTE AS INT) BETWEEN '01' AND '12'
GROUP BY PCODE
HAVING SUM(TMONEYF) = 0) B
WHERE INSTR(B.PCODE,A.CODE);
SELECT CODE
FROM
sjzc_znsjfx.QRYF_DW_FACT_KMCODEOFCX
WHERE PACODE = '2019'
AND COMPID IN ('1608') AND LEAF = 1
AND CAST(ACCOUNT_NOTE AS INT) BETWEEN '01' AND '12'
GROUP BY CODE
HAVING SUM(TMONEYF) = 0;
其中 code和pcode都是varchar类型,在数据库中直接使用union就可以使用,但是在代码中就会报错
暖心向阳 发表于 2020-5-14 15:30
是在java中,gbase版本是8.5.16.0
attach
从报错信息判断,是union前后的列的数据类型不匹配,
需要您确认一下union的sql执行的数据类型。 @qxy @qxy
蓝墨水 发表于 2020-5-14 15:31
从报错信息判断,是union前后的列的数据类型不匹配,
需要您确认一下union的sql执行的数据类型。 @qxy @q ...
所有的字段类型都在图片里面,在数据库里面执行就可以,就是不知道在java代码中执行就报这个
qxy 发表于 2020-5-14 15:52
所有的字段类型都在图片里面,在数据库里面执行就可以,就是不知道在java代码中执行就报这个
...
您好,咨询了下内部专家,了解到目前GBase 8a 并没有外发 8.5.16.0版本。所以无法帮您进行分析。请问您是在哪个项目使用的?
蓝墨水 发表于 2020-5-14 16:02
您好,咨询了下内部专家,了解到目前GBase 8a 并没有外发 8.5.16.0版本。所以无法帮您进行分析。请问您是 ...
这个不是免费的吗?在网上找的
我模拟了一下,没有复现你这个问题。
环境:8.6.2.33-R33 、gbase-connector-java-8.3.81.53-build55.5.5-bin.jar
附件是测试jar程序。修改 jdbcparam.properties中的sql参数为你提供的用例sql(原sql应该不完整,我按照自己的理解改写了下)
sql=SELECT A.PCODE AS CODE FROM ( SELECT CODE,PCODE FROM QRYF_DW_FACT_KMCODEOFCX WHERE PACODE = '2019' AND COMPID IN ('1608') AND LEAF = 0 ) a, (SELECT PCODE FROM QRYF_DW_FACT_KMCODEOFCX WHERE PACODE = '2019' AND COMPID IN ('1608') AND LEAF = 1 AND CAST(ACCOUNT_NOTE AS INT) BETWEEN '01' AND '12' GROUP BY PCODE HAVING SUM(TMONEYF) = 0) B WHERE INSTR(B.PCODE,A.CODE)=0 union all SELECT CODE FROM QRYF_DW_FACT_KMCODEOFCX WHERE PACODE = '2019' AND COMPID IN ('1608') AND LEAF = 1 AND CAST(ACCOUNT_NOTE AS INT) BETWEEN '01' AND '12' GROUP BY CODE HAVING SUM(TMONEYF) = 0;
---- 数据及结果
gbase> select * from qryf_dw_fact_kmcodeofcx;
+------+--------+---------+--------+-------+------+--------------+---------+
| code | compid | caption | pacode | pcode | leaf | account_note | tmoneyf |
+------+--------+---------+--------+-------+------+--------------+---------+
| 2019 | 1608 | gbase | 2019 | a | 1 | 01 | 0 |
| 2019 | 1608 | gbase | 2019 | a | 1 | 01 | 0 |
| 2020 | 1609 | gbase2 | 2019 | a | 0 | 02 | 0 |
| 2019 | 1608 | gbase1 | 2019 | a | 1 | 12 | 0 |
| 2019 | 1608 | gbase3 | 2019 | a | 1 | 01 | 0 |
| 2019 | 1608 | gbase1 | 2019 | a | 0 | 12 | 1 |
| 2019 | 1608 | gbase3 | 2019 | a | 0 | 01 | 0 |
+------+--------+---------+--------+-------+------+--------------+---------+
7 rows in set (Elapsed: 00:00:00.03)
gbase> SELECT A.PCODE AS CODE FROM
-> (
-> SELECT CODE,PCODE FROM
-> QRYF_DW_FACT_KMCODEOFCX
-> WHERE PACODE = '2019'
-> AND COMPID IN ('1608') AND LEAF = 0 ) a,
-> (SELECT PCODE
-> FROM
-> QRYF_DW_FACT_KMCODEOFCX
-> WHERE PACODE = '2019'
-> AND COMPID IN ('1608') AND LEAF = 1
-> AND CAST(ACCOUNT_NOTE AS INT) BETWEEN '01' AND '12'
-> GROUP BY PCODE
-> HAVING SUM(TMONEYF) = 0) B
-> WHERE INSTR(B.PCODE,A.CODE)=0
-> union all
-> SELECT CODE
-> FROM
-> QRYF_DW_FACT_KMCODEOFCX
-> WHERE PACODE = '2019'
-> AND COMPID IN ('1608') AND LEAF = 1
-> AND CAST(ACCOUNT_NOTE AS INT) BETWEEN '01' AND '12'
-> GROUP BY CODE
-> HAVING SUM(TMONEYF) = 0;
+------+
| CODE |
+------+
| 2019 |
| a |
| a |
+------+
3 rows in set (Elapsed: 00:00:00.31)
环境:8.6.2.33-R33 、gbase-connector-java-8.3.81.53-build55.5.5-bin.jar
附件是测试jar程序。修改 jdbcparam.properties中的sql参数为你提供的用例sql(原sql应该不完整,我按照自己的理解改写了下)
sql=SELECT A.PCODE AS CODE FROM ( SELECT CODE,PCODE FROM QRYF_DW_FACT_KMCODEOFCX WHERE PACODE = '2019' AND COMPID IN ('1608') AND LEAF = 0 ) a, (SELECT PCODE FROM QRYF_DW_FACT_KMCODEOFCX WHERE PACODE = '2019' AND COMPID IN ('1608') AND LEAF = 1 AND CAST(ACCOUNT_NOTE AS INT) BETWEEN '01' AND '12' GROUP BY PCODE HAVING SUM(TMONEYF) = 0) B WHERE INSTR(B.PCODE,A.CODE)=0 union all SELECT CODE FROM QRYF_DW_FACT_KMCODEOFCX WHERE PACODE = '2019' AND COMPID IN ('1608') AND LEAF = 1 AND CAST(ACCOUNT_NOTE AS INT) BETWEEN '01' AND '12' GROUP BY CODE HAVING SUM(TMONEYF) = 0;
---- 数据及结果
gbase> select * from qryf_dw_fact_kmcodeofcx;
+------+--------+---------+--------+-------+------+--------------+---------+
| code | compid | caption | pacode | pcode | leaf | account_note | tmoneyf |
+------+--------+---------+--------+-------+------+--------------+---------+
| 2019 | 1608 | gbase | 2019 | a | 1 | 01 | 0 |
| 2019 | 1608 | gbase | 2019 | a | 1 | 01 | 0 |
| 2020 | 1609 | gbase2 | 2019 | a | 0 | 02 | 0 |
| 2019 | 1608 | gbase1 | 2019 | a | 1 | 12 | 0 |
| 2019 | 1608 | gbase3 | 2019 | a | 1 | 01 | 0 |
| 2019 | 1608 | gbase1 | 2019 | a | 0 | 12 | 1 |
| 2019 | 1608 | gbase3 | 2019 | a | 0 | 01 | 0 |
+------+--------+---------+--------+-------+------+--------------+---------+
7 rows in set (Elapsed: 00:00:00.03)
gbase> SELECT A.PCODE AS CODE FROM
-> (
-> SELECT CODE,PCODE FROM
-> QRYF_DW_FACT_KMCODEOFCX
-> WHERE PACODE = '2019'
-> AND COMPID IN ('1608') AND LEAF = 0 ) a,
-> (SELECT PCODE
-> FROM
-> QRYF_DW_FACT_KMCODEOFCX
-> WHERE PACODE = '2019'
-> AND COMPID IN ('1608') AND LEAF = 1
-> AND CAST(ACCOUNT_NOTE AS INT) BETWEEN '01' AND '12'
-> GROUP BY PCODE
-> HAVING SUM(TMONEYF) = 0) B
-> WHERE INSTR(B.PCODE,A.CODE)=0
-> union all
-> SELECT CODE
-> FROM
-> QRYF_DW_FACT_KMCODEOFCX
-> WHERE PACODE = '2019'
-> AND COMPID IN ('1608') AND LEAF = 1
-> AND CAST(ACCOUNT_NOTE AS INT) BETWEEN '01' AND '12'
-> GROUP BY CODE
-> HAVING SUM(TMONEYF) = 0;
+------+
| CODE |
+------+
| 2019 |
| a |
| a |
+------+
3 rows in set (Elapsed: 00:00:00.31)