sql - CTE 查询中的错误“无法从存储过程中选择 RDB$DB_KEY”

Question

运行火鸟 SQL 2.5.6

这按预期工作：

select guarantor_id, guarantor_first, guarantor_last,  chart, patient_First, 
patient_last, pcp, copaybalance, status_pat, effective_dor, elapsed, case 
when elapsed <30 then '0-30' when elapsed <60 then '31-60' when elapsed <90 
then '61-90' when elapsed <120 then '91-120'  else '121+' end as bucket from
(

select guarantor_id, first_name as guarantor_first, last_name as 
guarantor_last, patno as chart, fname as patient_First, lname as 
patient_last, addr_id as pcp,  copaybalance, status_pat, effective_dor,
datediff(day, cast(effective_dor as date), cast('today' as date)) as elapsed
from
(

select at1.*, at2.date1 as dor, at2.cptcode as orig_cpt, case when at2.date1 
is null then at1.date1 else at2.date1 end as effective_dor, fname,lname, 
guarantor_id, register.addr_id, first_name, last_name, status_pat from
(select patno, invoiceno, trnsxno, date1, cptcode, copaybalance from 
archive_Transactions where archive_flag = 1 and cptcode not in 
('1','2','4','RETCHK') and copaybalance >= 0.01)  at1
left outer join (Select * from archive_transactions where archive_flag = 1 
and cptcode not in ('1','2')) at2 on at2.txnopaid = at1.trnsxno
inner join register on register.patno = at1.patno
inner join contact on contact.id = register.guarantor_id
order by patno
) 
)

接下来，我将该查询包装到 CTE 结构中（用于在更大的查询中进行进一步操作）：

with b as 
(

select guarantor_id, guarantor_first, guarantor_last,  chart, patient_First, 
patient_last, pcp, copaybalance, status_pat, effective_dor, elapsed, case 
when elapsed <30 then '0-30' when elapsed <60 then '31-60' when elapsed <90 
then '61-90' when elapsed <120 then '91-120'  else '121+' end as bucket from
(

select guarantor_id, first_name as guarantor_first, last_name as 
guarantor_last, patno as chart, fname as patient_First, lname as p 
patient_last, addr_id as pcp,  copaybalance, status_pat, effective_dor,
datediff(day, cast(effective_dor as date), cast('today' as date)) as elapsed
from
(

select at1.*, at2.date1 as dor, at2.cptcode as orig_cpt, case when at2.date1 
is null then at1.date1 else at2.date1 end as effective_dor, fname,lname, 
guarantor_id, register.addr_id, first_name, last_name, status_pat from
(select patno, invoiceno, trnsxno, date1, cptcode, copaybalance from 
archive_Transactions where archive_flag = 1 and cptcode not in 
('1','2','4','RETCHK') and copaybalance >= 0.01)  at1
left outer join (Select * from archive_transactions where archive_flag = 1 
and cptcode not in ('1','2')) at2 on at2.txnopaid = at1.trnsxno
inner join register on register.patno = at1.patno
inner join contact on contact.id = register.guarantor_id
order by patno
) 
)
)

select * from b

我希望得到相同的结果。相反，我收到一条错误消息：

SQL error code = -607
Cannot SELECT RDB$DB_KEY from a stored procedure
SQL warning code =301
DATE date type is now called TIMESTAMP
SQL warning code = 301
DATE data type is now called TIMESTAMP

我能够运行许多其他包含 CTE 的查询，其中许多更复杂，例如：

with myAging as 
(
select patno,fname,lname, sum(copaybalance) as pat_owed, min(e_dor) as 
eff_pat_dor, guarantor_id
from
(
select patno, fname,lname, trnsxno, date1, cptcode, copaybalance, 
max(effective_dor) as e_dor, guarantor_id from
(
select at1.*, at2.date1 as dor, at2.cptcode as orig_cpt, case when at2.date1 
is null then at1.date1 else at2.date1 end as effective_dor, fname,lname, 
guarantor_id from
(select patno, invoiceno, trnsxno, date1, cptcode, copaybalance from 
archive_Transactions where archive_flag = 1 and cptcode not in 
('1','2','4','RETCHK') and copaybalance >= 0.01)  at1
left outer join (Select * from archive_transactions where archive_flag = 1 
and cptcode not in ('1','2')) at2 on at2.txnopaid = at1.trnsxno
inner join register on register.patno = at1.patno
order by patno
)
group by patno, fname,lname, trnsxno, date1, cptcode, copaybalance, 
guarantor_id
) group by patno,fname,lname, guarantor_id
) 

select a.*, m.fam_dor, m2.fam_owed
from
(
select guarantor_id, guarantor, address_1, address_2, city, state, zip_code, 
patno, ma.fname,ma.lname,ma.pat_owed,ma.eff_pat_dor
from
(
select patno, ( first_name || case when middle_name = '' then ' ' when m 
middle_name is null then ' ' else (' ' || middle_name || ' ') end  ||  
last_name)
as guarantor,  contact.address_1, contact.address_2, contact.city, c 
contact.state, contact.zip_code, copaybal, guarantor_id from register 
inner join contact on contact.id = register.guarantor_id
where copaybal >= 0.01
 ) g
left outer join (select patno, fname, lname, pat_owed,eff_pat_dor from 
myAging) ma on ma.patno = g.patno


order by guarantor_id
) a
inner join
(select guarantor_id, min(eff_pat_dor) as fam_dor from myAging group by 
guarantor_id) m on m.guarantor_id = a.guarantor_id
inner join
(select guarantor_id, sum(pat_owed) as fam_owed from myAging group by 
guarantor_id) m2 on m2.guarantor_id = a.guarantor_id

...所以这个必须以某种方式从根本上不同，但我不明白如何。

标签： sqlfirebirdcommon-table-expressionfirebird2.5