Help with a tricky SQL ? (nested correlated sub-query - R41543 replicant)

RussellStainer

Member
Hi guys - new member, first post, sorry if I get the etiquette wrong.

I'm trying to replicate the R41543 UBE that runs forever and then dies on me, so I decided to have a pop at it in SQL to see if I can get some basic results which I can then summarise. It's a bit tricky, though, because there are 'document type' specific exceptions in the 41/IN UDC table to only process records from the F4111/F0911 where the OBJ is in the relevant AAIs....and it turns out to be a tricky SQL that is required with a nested correlated sub-query. I'm running it through STRSQL on an iSeries, but that should be irrelevant.
Anyway, that's a bit of the background....the error I am getting whilst trying to submit the SQL is as follows:
.
"Column SEL0005 or expression in SELECT list not valid."
.
Here is my query:
.
select t1.ilkco, t1.ildct, t1.ildoc, sum(t1.ilpaid) invvalue,
(select sum(t2.glaa) from jdprod/f0911 t2 where
t2.glkco = t1.ilkco and t2.gldoc = t1.ildoc and t2.gldct = t1.ildct
and t2.globj in (select t3.mlobj from jdprod/f4095 t3 where
t3.mlanum in ('4330','4340','4350')
and t3.mldcto = '' and t3.mldct = t1.ildct and t3.mlcost = '' and
((t3.mlco = t1.ilkco and t3.mlglpt = t1.ilglpt) or
(t3.mlco = t1.ilkco and t3.mlglpt = '****') or
(t3.mlco = '00000' and t3.mlglpt = t1.ilglpt) or
(t3.mlco = '00000' and t3.mlglpt = '****')))
group by t2.glkco, t2.gldoc, t2.gldct) glvalue from
jdprod/f4111 t1 where t1.ildgl < 104361
and t1.ildct = 'PV'
group by t1.ilkco, t1.ildct, t1.ildoc
.
So, column 5 (the one the engine is reporting is in error) is the difficult sub-query one (typical!).
.
Can any of you SQL experts out there help to see where I have gone wrong in the SQL, please?
.
Thanks in advance,
.
Russ
.
 
Hi Russ,
Did you try to add "glvalue" to the end of "group by"? If this is SQL/400 than you need to add whole CTE that creates "glvalue".

Hope this helps,

Bojan
 
Hi Russ -

It is probably because the second subquery returns a column that is not being grouped on. You will get the same type of error with a simpler query such as:

select F1, F2, sum(F3), F4 from TABLEA
GROUP BY F1, F2

SQL doesn’t know what to do with the F4 column since there isn’t an aggregate function like sum or count on it.

Jen
 
smile.gif

Thanks for that !
A "schoolboy error" !
It wouldn't recognize the 'glvalue' column as a column in the input tables, so I had to replicate the whole subquery in the Group By:
.
select t1.ilkco, t1.ildct, t1.ildoc, sum(t1.ilpaid) invvalue,
(select sum(t2.glaa) from jdprod/f0911 t2 where
t2.glkco = t1.ilkco and t2.gldoc = t1.ildoc and t2.gldct = t1.ildct
and t2.globj in (select t3.mlobj from jdprod/f4095 t3 where
t3.mlanum in ('4330','4340','4350')
and t3.mldcto = '' and t3.mldct = t1.ildct and t3.mlcost = '' and
((t3.mlco = t1.ilkco and t3.mlglpt = t1.ilglpt) or
(t3.mlco = t1.ilkco and t3.mlglpt = '****') or
(t3.mlco = '00000' and t3.mlglpt = t1.ilglpt) or
(t3.mlco = '00000' and t3.mlglpt = '****')))
group by t2.glkco, t2.gldoc, t2.gldct)
glvalue from jdprod/f4111 t1 where t1.ildgl < 104361
and t1.ildct = 'PV'
group by t1.ilkco, t1.ildct, t1.ildoc,
(select sum(t2.glaa) from jdprod/f0911 t2 where
t2.glkco = t1.ilkco and t2.gldoc = t1.ildoc and t2.gldct = t1.ildct
and t2.globj in (select t3.mlobj from jdprod/f4095 t3 where
t3.mlanum in ('4330','4340','4350')
and t3.mldcto = '' and t3.mldct = t1.ildct and t3.mlcost = '' and
((t3.mlco = t1.ilkco and t3.mlglpt = t1.ilglpt) or
(t3.mlco = t1.ilkco and t3.mlglpt = '****') or
(t3.mlco = '00000' and t3.mlglpt = t1.ilglpt) or
(t3.mlco = '00000' and t3.mlglpt = '****')))
group by t2.glkco, t2.gldoc, t2.gldct)
.
Thanks for finding the problem for me
smile.gif

.
Russ
 
Back
Top