with main as (
select a.acid,a.sol_id,a.del_flg,a.cif_id,a.limit_b2kid,
a.foracid,a.acct_name,a.gl_sub_head_code,a.acct_ownership,
a.schm_code,a.acct_rpt_code,a.acct_opn_date,a.acct_cls_flg,
a.acct_cls_date,a.acct_crncy_code,a.schm_type,cast(a.bank_id as decimal) bank_id,
b.doccode,b.doctypecode,b.entitydocumentid,b.referencenumber,b.status
from tbaadxx.gaxx a left join crmusxx.entitydocumxxxx b on a.cif_id=b.orgkey
--where foracid='010230200002278'
)
,
eab as (
select * from (
select b.sol_id,a.acid,b.cif_id,a.eod_date,a.end_eod_date,a.tran_date_bal,b.schm_type,b.schm_code,
case when (b.acct_cls_flg='Y' and acct_cls_date>sysdate) or b.acct_cls_flg='N'then 'live' else 'closed' end remark,
rank() over (partition by a.acid order by a.end_eod_date desc ) rnk
from tbaadxx.eaxx a inner join tbaadxx.gaxx b on a.acid=b.acid
) x where rnk=1 and remark='live'
)
--
select a.solid,a.acid,a.foracid,a.acct_name,b.end_eod_date,b.tran_date_bal,
a.schm_code,a.schm_type
from main a inner join eab b on a.acid=b.acid and a.del_flg='N'
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
what wrong I am doing here ?
I am getting the error : ORA-32039: recursive WITH clause must have column alias list
32039. 00000 - "recursive WITH clause must have column alias list"
*Cause: A WITH clause query referred to itself (recursive) but did
not have a column alias list specified for it.
*Action: Add a column alias list for the WITH clause query name.
Error at Line: 18 Column: 14
please help