hi,

we have a form containing array of 5 elements which is having domain of tfgld.leac (ledger account).

user will indiviaully zoom to each array element and select a ledger.

I want to write sql query and specify the values of these all five elements in IN part of SQL as
select * from tfgld008
where tfgld008.leac in(array elements values).

Can anybody guide how to give values in IN part of SQL ?

Vishal

Hi,

One solution is to sort the array, calculate the length of the array, assign X the value of 1st element and Y the value of last element and then use in range X and Y in your sql query

Something like this -

domain tccom.bpid x,y,bpid(5)

bpid(1,1) = "000180000"
bpid(1,2) = "000200000"
bpid(1,3) = "000300000"
bpid(1,4) = "000400000"
bpid(1,5) = "000500000"

x = bpid(1,1)
y = bpid(1,5)


select tccom100.*
from tccom100
where tccom100._index1 inrange (:x) and {:y}
selectdo
message("Found")
endselect

or

You can use individual array elements in the in statement. Something like this -

domain tccom.bpid x,y,z,a,b,bpid(5)

bpid(1,1) = "000180000"
bpid(1,2) = "000200000"
bpid(1,3) = "000300000"
bpid(1,4) = "000400000"
bpid(1,5) = "000500000"

x = bpid(1,1)
y = bpid(1,2)
z = bpid(1,3)
a = bpid(1,4)
b = bpid(1,5)


select tccom100.*
from tccom100
where tccom100._index1 in (:x,:y,:z,:a,:b)
selectdo
message("Found")
endselect

And the last option I can think of is by using Dynamic SQL -

string result(100),result1(100)
domain tccom.bpid bpid(5)
long sqlid
long ii

bpid(1,1) = "000180000"
bpid(1,2) = "000400000"

result = quoted.string(bpid(1,1)) & "," & quoted.string(bpid(1,2))


result1 = "select tccom100.nama from tccom100 where tccom100.bpid in (" & trim$(result) & ")"
sqlid = sql.parse(trim$(result1))
sql.exec(sqlid)
ii = sql.fetch(sqlid)
message("Found")
while not ii| and ii <> EENDFILE
ii = sql.fetch(sqlid)
message("Found")
endwhile
sql.close(sqlid)

There is a hint given on this thread - You can try wherebind. Related Thread (http://www.baanboard.com/baanboard/showthread.php?t=42348)

Thank you very much for guidance.

Vishal

why can't you just use or condition with wherebind option

domain tfgld.leac leac(3)
table ttfgld008
leac(1,1) = "80400"
leac(1,3) = "80500.0"
leac(1,2) = ""

select tfgld008.*
from tfgld008
where tfgld008._compnr=999
and (tfgld008._index1 = {:1}
or tfgld008._index1 = {:2} or tfgld008._index1 = {:3} )
wherebind(1,leac(1,1))
wherebind(2,leac(1,2))
wherebind(3,leac(1,3))
selectdo
message("%s",tfgld008.leac)
endselect

Here is one more similar thread (http://www.baanboard.com/baanboard/showthread.php?t=7586)