Closed wangxiahui007 closed 1 year ago
可以请你提供详细的 SQL 示例吗?这样我们能更快的定位和修复此问题。
with cntpty_acct as (
select acct_serno,acct_num,card_num,acct_name,ACCT_OPEN_DT
from f_ncbs_mb_acct_info
where data_dt='20230406'
and cust_num in (
select cust_num
from f_ncbs_cust_info
where data_dt='20230406'
and cust_shname in ('测试')
union
select a.cust_num
from a_f_dp_acct_belong_acm a
where a.data_dt='20230406'
and a.dept_num in (
select dept_num from a_d_og_org_info_acm where data_dt='20230406' and (super_dept_num='105' or dept_num='105')
)
)
),
cust_acct as (
select acct_serno,acct_num,card_num,acct_name,ACCT_OPEN_DT
from F_NCBS_MB_ACCT_INFO
where data_dt='20230406'
and cust_num in (select cbs_cust_num from a_d_cu_cust_base where data_dt='20230406' and is_int_cust='Y' and cust_shname='张三')
)
select
d.acct_name as 客户名称,
d.acct_num as 账号,
d.card_num as 卡号,
d.ACCT_OPEN_DT as 开户日期,
d.tran_dt as 交易日期,
d.tran_tmsp AS 交易时间,
case when d.dc_flg='C' THEN '入款' ELSE '出款' END as 出入款标识,
d.tran_amt as 交易金额,
d.ivc_brf as 摘要,
d.tran_postsc as 附言,
d.tran_desc as 交易描述,
d.cntpty_acct_num as 对手账号,
d.cntpty_acct_name as 对手名称,
d.cntpty_bank_name as 交易对手行
from (
select d.seq_num
,d.acct_serno
,d.chn_type_cd
,d.tran_dt
,d.tran_tmsp
,d.dc_flg
,d.tran_amt
,d.actl_bal
,d.ivc_brf
,d.tran_postsc
,d.tran_desc
,a.cntpty_acct_num
,a.cntpty_acct_name
,a.cntpty_bank_name
,a.card_num
,c.acct_num
,c.acct_name
,c.ACCT_OPEN_DT
from F_NCBS_MB_FIN_FLOW d
inner join cust_acct c on d.acct_serno = c.acct_serno
left join A_F_TX_FIN_CNTPTY a on a.seq_num=d.seq_num
where d.data_dt=(select to_char(dateadd(to_date(CURRENT_DATE(),'yyyy-mm-dd'),-1,'dd'),'yyyymmdd'))
and d.tran_dt <='20230406'
and (a.cntpty_acct_num in (
select acct_num from cntpty_acct
union all
select card_num from cntpty_acct where nvl(card_num,'')<>''
)
or a.cntpty_acct_name in ('张三','李四')
)
union
select d.seq_num
,d.acct_serno
,d.chn_type_cd
,d.tran_dt
,d.tran_tmsp
,d.dc_flg
,d.tran_amt
,d.actl_bal
,d.ivc_brf
,d.tran_postsc
,d.tran_desc
,a.cntpty_acct_num
,a.cntpty_acct_name
,a.cntpty_bank_name
,a.card_num
,c.acct_num
,c.acct_name
,c.ACCT_OPEN_DT
from F_NCBS_MB_FIN_FLOW d
inner join cntpty_acct c on d.acct_serno = c.acct_serno
left join A_F_TX_FIN_CNTPTY a on a.seq_num=d.seq_num and d.data_dt='20230406'
where d.data_dt='20230406'
and d.tran_dt <='20230406'
and a.cntpty_acct_name = '张三'
and a.cntpty_acct_num not in (
select acct_num from cust_acct
union all
select card_num from cust_acct where nvl(card_num,'')<>''
)
) d
order by d.acct_num, d.tran_tmsp desc
;
The Type of SQL e.g. Impala
Your Code e.g. parser.parserSql(....);
Problem e.g. the code do not work.