Hello All,
I´m trying to develop a consult session that query table tiitm001 with a certain description on field tiitm001.dsca I´m using dynamic SQL but my problem is the following: For the output I´m using multiocurrence screen (due to is a consult session) but I´m only can see the last record found.

Here is my code:


before.program:
import ("name",name)
import ("fami",fami)



field.regs1.cia:
before.display:
str.stoc = " "
str.pc = " "
str.dt = " "
item.dsca = " "

sql = sql.parse("select tiitm001.* " &
"from tiitm001 " &
"where tiitm001._compnr = 123 " &
"and tiitm001.dsca like """ & name & """ ")

sql.exec(sql)

while not sql.fetch(sql)
str.stoc = sprintf$("%14.2f",tiitm001.stoc)
regs1.cia = tiitm001.item & " " & tiitm001.dsca & " " & str.stoc
nombre.cia835 = "company name"
endwhile
sql.close(sql)



Any suggestion?
Thank you in advance.

Is there a reason you can not use query.extension functionality? I have done a multi-occurance session kind of like this in Baan 4 - in my case the view fields were one table and the multioccurence records were another table. I had to program all the events - to get the records. From your import I would recommend using the query.extension functionality.

Hello Mark,
I have done several tries with "normal" sql statements. But when I use LIKE sentence, throws an error when I complile the program.
Searching in the board, I have readed some recomendations about that if I need use LIKE sentence, and pass a variable (in this case, a search pattern) I migth use Dynamic SQL.

I haven´t use query.extend but I will try.

Have you done a query.extend with Select using LIKE, and a search pattern, as an input, succesfully?

Thank you.

Let me go check - I know I have used query.extend, just not sure if like was involved or not.

Here is a simple one I just tested:

declaration:

table ttiitm990 | Floor Stock Items
before.program:
query.extension = "tiitm990.item like " & chr$(34) & "317.*" & chr$(34)

Hello Mark,
Yes, your solution worked like a charm!

Thanks.