postgresql预览报错

1.1 问题描述

postgresql在设计器预览报错查询没有传回任何结果，如图：

1.2 解决思路

问题原因：调用的语句中如果存在插入临时表，帆软设计器只会执行第一部分的。

解决思路：引用with

2.1 最初写法

create temp table TEMP1(

EMPLOYEE_ID int not null,

EMPLOYEE_NO VARCHAR(256),

EMPLOYEE_NAME VARCHAR(256),

ACCOUNT_TRANSACTION INT default 0,

REGISTER INT default 0

);

--充值

insert into TEMP1(EMPLOYEE_ID,EMPLOYEE_NO,EMPLOYEE_NAME,ACCOUNT_TRANSACTION)

select TXN_BY,B.EMPLOYEE_NO,B.EMPLOYEE_NAME,SUM(1) 充值人次

from api_sta.ACCOUNT_TRANSACTION A join api_sta.EMPLOYEE_INFO B ON A.TXN_BY = B.EMPLOYEE_ID AND B.EMPLOYMENT_TYPE_CODE = '263894'

where   A.TXN_TYPE_CODE = '307261'

GROUP BY TXN_BY,B.EMPLOYEE_NO,B.EMPLOYEE_NAME;

--挂号

insert into TEMP1(EMPLOYEE_ID,EMPLOYEE_NO,EMPLOYEE_NAME,REGISTER)

select REGISTERING_OPERATOR_ID,B.EMPLOYEE_NO,B.EMPLOYEE_NAME,SUM(1) 挂号人次

from api_sta.REGISTER A join api_sta.EMPLOYEE_INFO B ON A.REGISTERING_OPERATOR_ID = B.EMPLOYEE_ID AND B.EMPLOYMENT_TYPE_CODE = '263894'

GROUP BY REGISTERING_OPERATOR_ID,B.EMPLOYEE_NO,B.EMPLOYEE_NAME;

select EMPLOYEE_ID,EMPLOYEE_NO,EMPLOYEE_NAME,sum(ACCOUNT_TRANSACTION) ACCOUNT_TRANSACTION,sum(REGISTER) REGISTER from TEMP1

group by EMPLOYEE_ID,EMPLOYEE_NO,EMPLOYEE_NAME;

drop table if exists TEMP1;

在数据库执行效果：

在设计器执行效果：

2.2 引用with写法

写法一：

WITH 自助机 AS(

--充值

select TXN_BY id,B.EMPLOYEE_NO,B.EMPLOYEE_NAME,count(*) 充值人次,'0' 挂号人次

from api_sta.ACCOUNT_TRANSACTION A join api_sta.EMPLOYEE_INFO B ON A.TXN_BY = B.EMPLOYEE_ID AND B.EMPLOYMENT_TYPE_CODE = '263894'

where  A.TXN_TYPE_CODE = '307261'

GROUP BY id,B.EMPLOYEE_NO,B.EMPLOYEE_NAME

union all 

--挂号

select REGISTERING_OPERATOR_ID id,B.EMPLOYEE_NO,B.EMPLOYEE_NAME,'0' 充值人次,SUM(1) 挂号人次

from api_sta.REGISTER A join api_sta.EMPLOYEE_INFO B ON A.REGISTERING_OPERATOR_ID = B.EMPLOYEE_ID AND B.EMPLOYMENT_TYPE_CODE = '263894'

GROUP BY id,B.EMPLOYEE_NO,B.EMPLOYEE_NAME)

select * from 自助机

在数据库执行效果：

在设计器执行效果：

写法二：

WITH 充值人次 AS (

       select B.EMPLOYEE_NO,B.EMPLOYEE_NAME,SUM(1) 充值人次

       from api_sta.ACCOUNT_TRANSACTION A join api_sta.EMPLOYEE_INFO B ON A.TXN_BY = B.EMPLOYEE_ID AND B.EMPLOYMENT_TYPE_CODE = '263894' where  A.TXN_TYPE_CODE = '307261'

       GROUP BY B.EMPLOYEE_NO,B.EMPLOYEE_NAME

     ), 挂号人次 AS (

       select B.EMPLOYEE_NO,B.EMPLOYEE_NAME,SUM(1) 挂号人次

       from api_sta.REGISTER A join api_sta.EMPLOYEE_INFO B ON A.REGISTERING_OPERATOR_ID = B.EMPLOYEE_ID AND B.EMPLOYMENT_TYPE_CODE = '263894'

       GROUP BY B.EMPLOYEE_NO,B.EMPLOYEE_NAME

     )

SELECT EMPLOYEE_NO,

       EMPLOYEE_NAME,

       SUM(1)充值人次,

       SUM(1)挂号人次

FROM api_sta.EMPLOYEE_INFO

WHERE EMPLOYEE_NO IN (SELECT B.EMPLOYEE_NO FROM api_sta.ACCOUNT_TRANSACTION A left join api_sta.EMPLOYEE_INFO B ON A.TXN_BY = B.EMPLOYEE_ID AND B.EMPLOYMENT_TYPE_CODE = '263894' where  A.TXN_TYPE_CODE = '307261')

GROUP BY EMPLOYEE_NO,EMPLOYEE_NAME;

在数据库执行效果：

在设计器执行效果：