× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.