I'd suggest to name the columns not in the Sub-select, but at the beginning
of the CTE, i.e.
With Cte_T1 (T1clientnam, T1divnam, T1remitrpt, T1poct, T1qbct, T1spmct,
T1npmct, T1emitinv, T1feeinv)
As (
Select Po.Clientnam, Po.Divnam, ....
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of K
Crawford
Sent: Mittwoch, 26. Oktober 2022 16:10
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: SQL CTE with union and group by
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)
else 00
end as t1poct
,00 as t1qbct
,00 as t1spmct
,case when po.ltrtypdes = 'Rights Notice' then count(po.ltrtypdes)
else 00
end as t1npmct
,' ' as t1emitinv
,' ' as t1feeinv
from qs36f.cobacPO po
where po.ltrtypdes in ('General Rights', 'COBRA Specific Rights Notice
Letter')
group by po.clientnam, po.divnam, po.ltrtypdes
UNION
select qb.clientnam
,qb.divnam
,' '
,00
,count(qb.clientNam)
,00
,00
,' '
,' '
from qs36f.cobacQBms qb
group by qb.clientnam, qb.divnam
UNION
select sp.clientnam
,sp.divnam
,' '
,00
,00
,count(sp.clientNam)
,00
,' '
,' '
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
--
KCrawford
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
