date: Wed, 26 Oct 2022 08:24:04 -0600
from: Javier Sanchez <javiersanchezbarquero@xxxxxxxxx>
subject: Re: SQL CTE with union and group by
Maybe if you explicitly qualify all of your selected cte_t1 columns in the
definition of cte_t2 with a prefixed "t." could work, as you aliased
icte_t1 t as "t".
El mi?, 26 oct 2022 a las 8:10, K Crawford (<kscx3ksc@xxxxxxxxx>) escribi?:
I am trying to do the following SQL CTE..
I get an error
SQL State: 42703
Vendor Code: -206
Message: [SQL0206] Column or global variable T1FEEINV not found. Cause .
. . . : T1FEEINV was not found as a column of table *N in *N and was not
found as a global variable in *N. If the table is *N, T1FEEINV is not
a column of any table or view that can be referenced, or T1FEEINV is a
special register that cannot be set in an atomic compound statement.
I feel like my as t1feeinv is defining it. What am I missing?
with cte_t1 as (
select po.clientnam as t1clientnam
,po.divnam as t1divnam
,' ' as t1remitrpt
,case when po.ltrtypdes = 'General Rights' then count(po.ltrtypdes)
end as t1poct
,00 as t1qbct
,00 as t1spmct
,case when po.ltrtypdes = 'Rights Notice' then count(po.ltrtypdes)
end as t1npmct
,' ' as t1emitinv
,' ' as t1feeinv
from qs36f.cobacPO po
where po.ltrtypdes in ('General Rights', 'COBRA Specific Rights
group by po.clientnam, po.divnam, po.ltrtypdes
from qs36f.cobacQBms qb
group by qb.clientnam, qb.divnam
from qs36f.cobacQBms sp
group by sp.clientnam, sp.divnam
order by 1,2
cte_t2 as (
select t1clientnam as t2clientnam
,t1divnam as t2divnam
,min(t1remitrpt) as t2remitrpt
,max(t1poct) as t2poct
,max(t1qbct) as t2qbct
,max(t1spmct) as t2spmct
,max(t1npmct) as t2npmct
,max(t1remitinv) as t2remitinv
,max(t1feeinv) as t2feeinv
from cte_t1 t
group by t1clientnam, t1divnam
order by t1clientnam, t1divnam
select * from cte_t2
As an Amazon Associate we earn from qualifying purchases.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.