query.extend.where syntax

[en@frrom]

I wrote a session with extended search on BP's. Depending on where the session is called from, the relevant type of BP will be sales-to, inv-to, pay-by etc. The users enters some search arguments on the top part of the form, and the search results are displayes on the lower part (multi-occ).

The main table stays tccom100, but depending on the type of bp, I add a reference control in my query.extend.where to the relevant table. Below you will find a piece of my code.

I encounter 2 dificulties using query extension:
a) I have an enum field as search field; status (tccom100.prst). When I include the check on status in my query.extend.where, it goes wrong. No records are selected, no matter what the user fills on the screen.

Here a piece of the code:

function search.sls.to()
{
	query.extend.where("tccom100.nama inrange :nama and :nama.t" &
	" and	tccom100.seak inrange :seak and :seak.t" &
	" and 	(prst = EMPTY or tccom100.prst = :prst)" &
	" and	tccom100.fovn inrange :fovn and :fovn.t" &
	" and	tccom100.cadr refers to tccom130" &
	" and	exists (select 	tccom110._index1" &
	"		from 	tccom110" &
	"		where 	tccom110._index1 = {:tccom100.bpid})" &
	" and	tccom130.namc inrange :namc and :namc.t" &
	" and	tccom130.hono inrange :hono and :hono.t" &
	" and	tccom130.name inrange :name and :name.t" &
	" and	tccom130.pstc inrange :pstc and :pstc.t" &
	" and	tccom130.ccty inrange :ccty and :ccty.t")
	
}

I also tried to replace that line with
" and tccom100.prst = :prst" &
but that also doesn't return results.

The form-field prst has the same domain as tccom100.prst (tccom.prst). When I debug, I see that the field.prst.when.field.changes section is ignored, even when the field is changed. The value of prst seems to stay on EMPTY... Is there any limitation or special setting required to use enum fields on the form.

The second issue is that I actually want the check on address code (cadr) to be on tccom110, and not on tccom100. But when I do that like below, I get syntax errors on runtime.

function search.sls.to()
{
	query.extend.where("tccom100.nama inrange :nama and :nama.t" &
	" and	tccom100.seak inrange :seak and :seak.t" &
	" and 	(prst = EMPTY or tccom100.prst = :prst)" &
	" and	tccom100.fovn inrange :fovn and :fovn.t" &
	" and	exists (select 	tccom110._index1" &
	"		from 	tccom110" &
	"		where 	tccom110._index1 = {:tccom100.bpid})" &
	" and	tccom110.cadr refers to tccom130" &
	" and	tccom130.namc inrange :namc and :namc.t" &
	" and	tccom130.hono inrange :hono and :hono.t" &
	" and	tccom130.name inrange :name and :name.t" &
	" and	tccom130.pstc inrange :pstc and :pstc.t" &
	" and	tccom130.ccty inrange :ccty and :ccty.t")
	
}

Thanks in advance,
En

[en@frrom]

I see nobody bit so far, so I am trying my luck again....

[Andreas]

Hi En,
i think your problem lies in the line

and 	(prst = EMPTY or tccom100.prst = :prst)" &

How should the system evaluate 'prst = empty'? What is 'prst'? It's no table field i think.
If you try to do a normal select statement with this syntax you should get a compilation error.
As for your statement of changing the line to

" and tccom100.prst = :prst" &

try this

" and tccom100.prst = :" & prst &

As for your problem with tccom110, tccom130.
Have you coded a query.extend.select and query.extend.from?
Have also the mode (append,overwrite) in mind.
Hope this do the trick,
Andreas

[Evert-Jan Bosch]

you can also try:

" and 	:prst = EMPTY or tccom100.prst = :prst)" &

[en@frrom]

Andreas,

EMPTY is normally the value for a not filled enum variable/field. It doesn't have to be a table field, as long as the variable is of type enum, which is the case with my variable prst.

Your suggestion gives me compilation errors of "illegal type combination: string and enum".

Evert, unfortuntely your suggestion doesn't help me either. It is basically what I tried initially...

[Andreas]

Hi En,
i still not really undertstand the syntax with the prst field but after a quick test with your query i found out that the prob of selecting no records depends to the subquery on tccom110 (i hope so).
In my test

"		where 	tccom110._index1 = {:tccom100.bpid})" &

gives no result but

"		where 	tccom110._index1 = {tccom100.bpid})" &

fetches some records (the colon before tccom100.bpid).

Hope this helps,
Andreas

[Evert-Jan Bosch]

YES,
that must be the solution!