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