Hi,
SQL question ....

I want to select all po lines having back order if the
total back order qty on the order (all lines) is within a range.

For e.g.
Range = 100 - 200

Order Line BackOrder
1 10 30
1 20 30
1 30 30

2 10 50
2 20 100
2 30 0

3 10 250
3 20 100

In the above example the following lines will be picked up
Order Line BackOrder
2 10 50
2 20 100

I do not want to do a validation and do a continue in the SELECTDO
All recs "entering" the SELECTDO must be valid ones.
I do not want to filter them out further in the SELECTDO.

table
tdpur041
fields
Purchase Order tdpur041.orno
Position tdpur041.pono
Back Order tdpur041.bqua

1.
select tdpur041.orno,tdpur041.pono,tdpur041.bqua
from tdpur041
where 100<=(select sum(a.bqua) from tdpur041 a where a.orno=tdpur041.orno)
and 200>=(select sum(a.bqua) from tdpur041 a where a.orno=tdpur041.orno)


but next is faster

2.
domain tcorno temp.orno
domain tcbool corect.orno
double sum.bqua

temp.orno=0
corect.orno=false
select tdpur041.orno,tdpur041.pono,tdpur041.bqua
from tdpur041
selectdo
if temp.orno<>tdpur041.orno then
select sum(a.bqua):sum.bqua
from tdpur041 a
where a.orno=:tdpur041.orno
endselect
if sum.bqua>=100 and sum.bqua<=200 then
corect.orno=true
else
corect.orno=false
endif
temp.orno=tdpur041.orno
endif
if corect.orno then
....
endif
endselect

I guess the first query could also be written as follows...

select tdpur041.orno,tdpur041.pono,tdpur041.bqua
from tdpur041
where (select sum(a.bqua)
from tdpur041 a
where a.orno=tdpur041.orno) >= 100 and <= 200 )
selectdo
endselect

...which undoubtfully would be better than doing the
same subquery twice.

Tomas Sanchez Tejero
tsanchez@consultant.com

It would be good if it was possible. The compiler gives error message:

Syntax error: 'select' not expected

You need to but the exists clause in the select

Select tdpur041.orno, tdpur041.pono, tdpur041.bqua
from tdpur041
where exists (select sum(a.bqua)
from tdpur041 a
where a.orno = tdpur041.orno
and a.bqua >= 100
and a.bqua <= 200)
selectdo
endselect

The problem is to select order positions where total backorder quantity for order is in ranges, not position backorder quantity.

The solition is next:

select tdpur041.orno,tdpur041.pono,tdpur041.bqua
from tdpur041
where exists (select a.orno, sum(a.bqua) from tdpur041 a where a.orno=tdpur041.orno group by a.orno having sum(a.bqua) inrange 100 and 200)

Unfortunatelly, BAAN does not support this.

Hi ... Thanks for the answers
But ... none of 'em solve the problem.

isimeon ... your answer won't work coz i can't have something like
100<=(select sum(a.bqua) from tdpur041 a where a.orno=tdpur041.orno)
it's gonna spit out errors while compiling.

tsanchez ...
where (select sum(a.bqua)
from tdpur041 a
where a.orno=tdpur041.orno) >= 100 and <= 200 )
That's gonna spit errors.

drkost .... your answer won't work coz
the range is to be applied to the sum of the bquas of all the order lines.
(not for each line).

Hence ... looks like this question is still open.
Calling for SQL gurus :)

Thx

isimeon ... your answer won't work coz i can't have something like
100<=(select sum(a.bqua) from tdpur041 a where a.orno=tdpur041.orno)
it's gonna spit out errors while compiling.

BigJohn, is it "gonna" or did it spit errors? For the spec you wrote, isimeon had all the answers. Atleast on my machine the code compiled and worked as specified.

Tomas' solution would have worked if Baan allowed (a > 100 and < 200) as a valid expression (Python (http://www.python.org) does - 100 < a < 200).

dkorst's solution while technically correct does not solve the functional problem at hand. That solution pulls all order lines where there is atleast one line which matches the condition tdpur401.bqua > 100 and tdpur401.bqua < 200.

table ttdpur401
long fp, ret
#define PUT(...) ret = seq.puts(..., fp)
function main()
{
fp = seq.open("baanboard.txt", "w")
PUT("|************** Output from isimeon1() *******************")
isimeon1()
PUT("|************** End Output from isimeon1() ***************")
PUT("")
PUT("")
PUT("|************** Output from isimeon2() *******************")
isimeon2()
PUT("|************** End Output from isimeon2() ***************")
PUT("")
PUT("")
PUT("|************** Output from tsanchez() *******************")
| tsanchez()
PUT("|************** End Output from tsanchez() ***************")
PUT("")
PUT("")
PUT("|************** Output from dkorst() *******************")
dkorst()
PUT("|************** End Output from dkorst() ***************")
PUT("")
PUT("")
PUT("|************** Output from isimeon3() *******************")
| isimeon3()
PUT("|************** End Output from isimeon3() ***************")
ret = seq.close(fp)
}
function isimeon1()
{
select tdpur401.orno,tdpur401.pono,tdpur401.bqua
from tdpur401
where 100<=(select sum(a.bqua) from tdpur401 a where a.orno=tdpur401.orno)
and 200>=(select sum(a.bqua) from tdpur401 a where a.orno=tdpur401.orno)
selectdo
PUT(sprintf$("%s %d %d", tdpur401.orno, tdpur401.pono, tdpur401.bqua))
endselect
}

function isimeon2()
{
domain tcorno temp.orno

domain tcbool corect.orno
double sum.bqua

| temp.orno=0 vamsi.o - This is a string domain in BaanERP
temp.orno=""
corect.orno=false

select tdpur401.orno,tdpur401.pono,tdpur401.bqua
from tdpur401
selectdo
if temp.orno<>tdpur401.orno then
select sum(a.bqua):sum.bqua
from tdpur401 a
where a.orno=:tdpur401.orno
endselect
if sum.bqua>=100 and sum.bqua<=200 then
corect.orno=true
else
corect.orno=false
endif
temp.orno=tdpur401.orno
endif
if corect.orno then
PUT(sprintf$("%s %d %d", tdpur401.orno, tdpur401.pono, tdpur401.bqua))
endif
endselect
}

|function tsanchez()
|{
| select tdpur401.orno,tdpur401.pono,tdpur401.bqua
| from tdpur401
| where (select sum(a.bqua)
| from tdpur401 a
| where a.orno=tdpur401.orno) >= 100 and <= 200 )
| selectdo
| PUT(sprintf$("%s %d %d", tdpur401.orno, tdpur401.pono, tdpur401.bqua))
| endselect
|}
|Tomas, in Baan the expression (a > 10 and < 20) is not legal.
|The construct has to be (a > 10 and a < 20).

function dkorst()
{
select tdpur401.orno, tdpur401.pono, tdpur401.bqua
from tdpur401
where exists (select sum(a.bqua)
from tdpur401 a
where a.orno = tdpur401.orno
and a.bqua >= 100
and a.bqua <= 200)
selectdo
PUT(sprintf$("%s %d %d", tdpur401.orno, tdpur401.pono, tdpur401.bqua))
endselect
}

|function isimeon3()
|{
| select tdpur401.orno,tdpur401.pono,tdpur401.bqua
| from tdpur401
| where exists (select a.orno, sum(a.bqua) from tdpur401 a where a.orno=tdpur401.orno group by a.orno having sum(a.bqua) inrange 100 and 200)
| selectdo
| PUT(sprintf$("%s %d %d", tdpur401.orno, tdpur401.pono, tdpur401.bqua))
| endselect
|}

Hi BigJohn,

I have compiled solution 1. and 2. without errors. Post your full code or e-mail me. What Baan version and portingset you use?

Regards

Are you compiling query 1. in SQL Queries (session ttadv3180m000)? There are some SQL limitations and query 1. does not work.