Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Tools Development

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
35%
Installation Wizard into new VRC
42%
Manual into existing VRC
3%
Manual into new VRC
19%
Total votes: 31

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 21st April 2005, 11:05
en@frrom en@frrom is offline
Guru
 
Join Date: Aug 2003
Location: Belgium
Posts: 1,219
en@frrom will become famous soon enough
Baan: B50B - DB: Oracle 8.0 - OS: Win2k AS
query.extend.where syntax

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:
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.
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	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
Reply With Quote
Sponsored Links
  #2  
Old 2nd May 2005, 11:18
en@frrom en@frrom is offline
Guru
 
Join Date: Aug 2003
Location: Belgium
Posts: 1,219
en@frrom will become famous soon enough
Baan: B50B - DB: Oracle 8.0 - OS: Win2k AS
I see nobody bit so far, so I am trying my luck again....
Reply With Quote
  #3  
Old 2nd May 2005, 14:42
Andreas's Avatar
Andreas Andreas is offline
Senior Member
 
Join Date: Nov 2001
Location: Hannover
Posts: 263
Andreas is on a distinguished road
Baan: ERP LN FP5 - DB: Oracle 10g - OS: AIX, SLES
Syntax

Hi En,
i think your problem lies in the line
Code:
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
Code:
" and tccom100.prst = :prst" &
try this
Code:
" 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
__________________
Jazz is not dead, it just smells funny!
F.Z.
Reply With Quote
  #4  
Old 2nd May 2005, 15:31
Evert-Jan Bosch Evert-Jan Bosch is offline
Senior Member
 
Join Date: Feb 2005
Location: Houten, Netherlands
Posts: 187
Evert-Jan Bosch is on a distinguished road
Baan: IV, V, SSA ERP LN 6 - DB: oracle - OS: AIX
you can also try:
Code:
" and 	:prst = EMPTY or tccom100.prst = :prst)" &
__________________
Regards,
Evert-Jan
Reply With Quote
  #5  
Old 2nd May 2005, 16:21
en@frrom en@frrom is offline
Guru
 
Join Date: Aug 2003
Location: Belgium
Posts: 1,219
en@frrom will become famous soon enough
Baan: B50B - DB: Oracle 8.0 - OS: Win2k AS
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...
Reply With Quote
  #6  
Old 3rd May 2005, 08:54
Andreas's Avatar
Andreas Andreas is offline
Senior Member
 
Join Date: Nov 2001
Location: Hannover
Posts: 263
Andreas is on a distinguished road
Baan: ERP LN FP5 - DB: Oracle 10g - OS: AIX, SLES
Other point

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
Code:
"		where 	tccom110._index1 = {:tccom100.bpid})" &
gives no result but
Code:
"		where 	tccom110._index1 = {tccom100.bpid})" &
fetches some records (the colon before tccom100.bpid).

Hope this helps,
Andreas
__________________
Jazz is not dead, it just smells funny!
F.Z.

Last edited by Andreas : 4th May 2005 at 07:32.
Reply With Quote
  #7  
Old 3rd May 2005, 16:08
Evert-Jan Bosch Evert-Jan Bosch is offline
Senior Member
 
Join Date: Feb 2005
Location: Houten, Netherlands
Posts: 187
Evert-Jan Bosch is on a distinguished road
Baan: IV, V, SSA ERP LN 6 - DB: oracle - OS: AIX
YES,
that must be the solution!
__________________
Regards,
Evert-Jan
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Accessing second db in same instance - syntax learner Tools Administration & Installation 5 12th April 2005 17:10
Syntax for using USR_DBS_RES Martin Tools Administration & Installation 2 27th February 2004 16:44
Help! String operation syntax in BaaN marselhober Tools Development 2 25th August 2003 15:54
Help! String operation syntax in BaaN marselhober Tools Development 3 24th August 2003 07:49
Different syntax in AFS documentation fmorais AFS/DDC/OLE: Function servers 3 12th December 2002 19:48


All times are GMT +2. The time now is 01:02.


©2001-2017 - Baanboard.com - Baanforums.com