Re: SQL CTE with union and group by

Try to alias all the UNIONs' columns with the same names too and keep the
other change as well.

El mié, 26 oct 2022 a las 8:32, K Crawford (<kscx3ksc@xxxxxxxxx>)
escribió:

I had tried that. Just to be sure I tried it again and I get the same
message.

On Wed, Oct 26, 2022 at 9:24 AM Javier Sanchez <
javiersanchezbarquero@xxxxxxxxx> wrote:

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)

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

--
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

--
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

--
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