Hello all,

I am writing a select statement where I need to match the index plus
a string field that has depth of 8 strings.

select table.*
from table
where table._index1 = {:key1,:key2}
and field(1,1) = :array(1)
and field(1,2) = :array(2)
and field(1,3) = :array(3).......


when I use the :array(1) it compiles but crashes at run time
when I replace the array for vars(:var.1, :var.2,:var.3....) doesn't crash but it doesn't find my records either.


is this possible? In need to use selecteos to facilitate my program.

Thanks in advance,

Hi,

do it like an index:

select table.*
from table
where table._index1 = {:key1,:key2}
and field = {:array(1), :array(2), ...}

Think it will work for you.

select table.*
from table
where table._index1 = {:key1,:key2}
and field(1,1) = :1
and field(1,2) = :2
and field(1,3) = :3
wherebind(1,var(1))
wherebind(2,var(2))
wherebind(3,trim$(var(3))) | can manipulate the variable using functions too
selectdo

endselect


make sure var is of same domain as the of field

* I tried both ways, and from RedBatz doesn't work because is not a combined field.
* wherebind just doesn't find them and can't understand why. I do believe that it should work this way.

had to wrote an if statement in the selectdo.... I do not like that but does the job.

thanks again and will keep trying.

What table are we talking about - is this a baan standard table? I keep thinking it is how the arrays are being referenced, but never done this myself.

I keep thinking about this one query we used:

select tibom010.*
from tibom010
where tibom010._index1 >= {:1, :2, :3}
order by tibom010._index1
as set with 1 rows
wherebind (1, mitm.stack(1, level))
wherebind (2, pono.stack(level))
wherebind (3, seqn.stack(level))
selectdo
endselect

No, it is not a standard table. The problem is that the table has depth of 8 on the field am trying to query.

select tssma922.*
from tssma922
where tssma922._index1 = {:orno,:swor}
and tssma922.type = :i.type
and tssma922.stat(1,1) = :1
and tssma922.stat(1,2) = :2
and tssma922.stat(1,3) = :3
and tssma922.stat(1,4) = :4
and tssma922.stat(1,5) = :5
and tssma922.stat(1,6) = :6
and tssma922.stat(1,7) = :7
and tssma922.stat(1,8) = :8
wherebind(1,l.stat.1)
wherebind(2,l.stat.2)
wherebind(3,l.stat.3)
wherebind(4,l.stat.4)
wherebind(5,l.stat.5)
wherebind(6,l.stat.6)
wherebind(7,l.stat.7)
wherebind(8,l.stat.8)
selectdo

Thanks again

I was thinking your variables were an array also - from the code it looks like it is just 8 different variables.

infact, i put them in 8 diff vars because was not working with an array. Thought that was the problem but, as a deseperated move :confused: I created eight vars.

Did you try a wherebind(1, var(1, 1)) and wherebind (2, var(1, 2)), .... The one table I found where I could try something like that I got it to work. It might have been with enumerated domains. I will have to check when I get back to work.