I have a session script where I am selecting various information, after the first select is complete, i have another select in the same statement. Why is there data in my second select that does not equal the prerequisits from my first select? ( My sum(tdssc051.cqtr) is selecting all the tdssc051.cqtr for the item and not just the ones that meet the first select criteria.

tobld = 0
totalcum = 0
totauth = 0
select tdssc051.*, tccom010.*, tiitm001.*, tdssc050.*, tdssc001.*
from tdssc051, tccom010, tiitm001, tdssc050, tdssc001
where tdssc051._index1 inrange {:cuno.f, :cdel.f, :item.f,
:cont.f, :pono.f}
and {:cuno.t, :cdel.t, :item.t, :cont.t, :pono.t}
and tdssc051.auth = :auth.f
and tdssc051.cuno refers to tccom010
and tdssc051.item refers to tiitm001
and tdssc051.auth refers to tdssc050
and tdssc001.cuno = tdssc051.cuno
and tdssc001.cdel = tdssc051.cdel
and tdssc001.item = tdssc051.item
and tdssc001.cont = tdssc051.cont
and tdssc001.pono = tdssc051.pono
and tdssc001.stat = "LV"
and tdssc001._compnr = :comr
and tdssc051._compnr = :comr
and tdssc050._compnr = :comr
and tccom010._compnr = :comr
and tiitm001._compnr = :comm
order by tdssc051.item
selectdo
select sum(tdssc001.ydeq):totalcum
from tdssc001
where tdssc001.item = :tdssc051.item
and tdssc001.cuno inrange :cuno.f and :cuno.t
and tdssc001.cdel inrange :cdel.f and :cdel.t
and tdssc001.cont inrange :cont.f and :cont.t
and tdssc001.pono inrange :pono.f and :pono.t
and tdssc001.stat = "LV"
and tdssc001._compnr = :tdssc001._compnr
selectdo

select sum(tdssc051.cqtr):totauth
from tdssc051
where tdssc051.item = :tdssc051.item
and tdssc051._compnr = :comr
and tdssc051.cuno inrange :cuno.f and :cuno.t
and tdssc051.cdel inrange :cdel.f and :cdel.t
and tdssc051.cont inrange :cont.f and :cont.t
and tdssc051.pono inrange :pono.f and :pono.t
and tdssc051.auth = :auth.f
selectdo
select sum(tisfc001.qrdr),sum(tisfc001.qdlv)
from tisfc001
where tisfc001.mitm = :tdssc051.item
and tisfc001._compnr = :comm
and tisfc001.osta between tcosta.free and tcosta.hours.adj
selectdo
totbld = tisfc001.qrdr - tisfc001.qdlv

rprt_send()
selectempty
endselect
endselect
endselect
endselect
}

Hi , I looked at your script a bit . You used same tables in every select statements. As you know that every tables have pointers for its cells. According to your script , one table like tdssc051 are used one more time in the same time. Look follow
select tdssc051.*
from tdssc051
selectdo

select sum(tdssc051.xxx)
from tdssc051
selectdo
******
endselect
endselect

This query don't works properly. Because you use same table in same time. In these cases , firstly I select needed tables value like not tdssc051.* For example select tdssc051.xxx, tdssc051.yyy and like that. That is why , My query should be like that:

select tdssc051.xxx,tdssc051.yyy,tdssc051.zzz
from tdssc051
where ******************
selectdo
temp1 = tdssc051.xxx
temp2 = tdssc051.yyy
temp3 = tdssc051.zzz

select sum(tdssc051.xxx)
from tdssc051
where ************
selectdo
endselect
tdssc051.xxx = temp1
tdssc051.yyy = temp2
tdssc051.zzz = temp3

endselect

In these way we manage to order in table's pointer. But If you need all table cell value,
then only take into primary keys of the table into consideration. It will be enough for you ! and you should examine all tables in your script like that.


Good Luck !

Or just use aliases for the inner selects. Since all you are doing is a sum an alias would work just fine.

An example how to use alias


select t_tssoc220.orno:t_orno,
t_tssoc220.lino:t_lino,
t_tssoc220.pris:pris,
t_tssoc220.item,
t_tssoc220.acpr:t_acpr
from tssoc220 t_tssoc220,tsmdm200 t_tsmdm200
where t_tssoc220._index3 = {:tssoc200.orno,:tssoc210.acln}
and t_tssoc220.item refers to t_tsmdm200

Can you use an alias with the sum function?

Yes you can use sum with an alias. Below is another sample.


select old.cono:cono, old.ptyp:ptyp, old.year:year,
old.prno:prno, old.leac:leac, old.ccur:ccur,
sum(old.fdam):fdam,
sum(old.fcam):fcam,
sum(old.fdah):fdah,
sum(old.fcah):fcah,
sum(old.fqt1):fqt1,
sum(old.fqt2):fqt2,
sum(old.ndam):ndam,
sum(old.ncam):ncam,
sum(old.ndah):ndah,
sum(old.ncah):ncah,
sum(old.nqt1):nqt1,
sum(old.nqt2):nqt2
from tfgld201 old
where old.leac = :i.old.leac
group by old.cono, old.ptyp, old.year,
old.prno, old.leac, old.ccur
selectdo