Baanboard.com

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

User login

Frontpage Sponsor

Main

Poll
How big is your Baan-DB (just Data AND Indexes)
0 - 200 GB
18%
200 - 500 GB
28%
500 - 800 GB
3%
800 - 1200 GB
7%
1200 - 1500 GB
7%
1500 - 2000 GB
13%
> 2000 GB
24%
Total votes: 71

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 10th July 2019, 14:34
mvanta mvanta is offline
Member
 
Join Date: May 2004
Location: Athens HELLAS
Posts: 43
mvanta is on a distinguished road
Baan: LN 10.5 - DB: SQL 2014 - OS: Windows 2012
Help needed in query.extend.where.in.zoom
Baan: Infor ERP 10.5
C/S: None/Unknown

Hello all.
We try to create a script to limit the selection option of the user on a session.
we have a field that is zooming in tcemm170 and returns the company number. We want to give to the user the option to select either the logistic or the financial company.

we have the financial company from the code comp=get.compnr() and the logistic from the sql below,
extern domain tcncmp comp

select tfgld003.bcmp
from tfgld003
where tfgld003._compnr=:comp and tfgld003.indt=0
selectdo
endselect

But when trying to use query.extend.where.in.zoom we get no result and a lot of errors.
for example if we try this code,
query.extend.where.in.zoom( "tcemm170.comp= :comp" )
we get the error,
Process 431 - Error : SQLState HYL04: External variable 'comp' not found (301) (rcdset.onfinddata() in object ottstpstandard)
We are about to give up!
Please advice,

Maria

Last edited by mvanta : 10th July 2019 at 14:56.
Reply With Quote
  #2  
Old 10th July 2019, 16:57
Ajesh's Avatar
Ajesh Ajesh is offline
Guru
 
Join Date: Feb 2009
Posts: 642
Ajesh is on a distinguished road
Baan: LN 10.4 - DB: Oracle - OS: HP-Unix
try doing something like

Code:
query.extend.where.in.zoom( "tcemm170.comp in (select tfgld003.bcmp from tfgld003 where tfgld003.indt = 0 )" )
Is there a need to use get.compnr() as by default the table would look in the current company?
Reply With Quote
Sponsored Links
  #3  
Old 10th July 2019, 18:03
mvanta mvanta is offline
Member
 
Join Date: May 2004
Location: Athens HELLAS
Posts: 43
mvanta is on a distinguished road
Baan: LN 10.5 - DB: SQL 2014 - OS: Windows 2012
Thank you for your help and for answering!
:-)
It works but it returns only one of the companies, the logistic.
We need to have as well get.compnr() because the user should have 2 options:either the financial or the logistic.
We could get the financial from this sql,
select tcemm170.comp
from tcemm170
where tcemm170.comp = :comp
selectdo
endselect

but how we could combine it to your code?

Regards,
Maria
Reply With Quote
  #4  
Old 11th July 2019, 11:12
Ajesh's Avatar
Ajesh Ajesh is offline
Guru
 
Join Date: Feb 2009
Posts: 642
Ajesh is on a distinguished road
Baan: LN 10.4 - DB: Oracle - OS: HP-Unix
I think the better option is to build a customized session, txemm1170m00 and in before.program of that session, use

Code:
before.program:
              import("sess.name",sess.name)
              log.comp = get.compnr()
              select tfgld003.bcmp:fin.comp
              from tfgld003
              where tfgld003.indt=0
              selectdo
              endselect
              if sess.name = "XXXXX" then  |Where XXXX is your specific session
              query.extend.where( "tcemm170.comp = :log.comp or
               tcemm170.comp = :fin.comp")
               endif

Last edited by Ajesh : 11th July 2019 at 12:36.
Reply With Quote
  #5  
Old 11th July 2019, 11:24
mvanta mvanta is offline
Member
 
Join Date: May 2004
Location: Athens HELLAS
Posts: 43
mvanta is on a distinguished road
Baan: LN 10.5 - DB: SQL 2014 - OS: Windows 2012
Thank you very much for your time!
we will try your suggestion but I think we will get the same error we got before,
Process 431 - Error : SQLState HYL04: External variable 'comp' not found (301) (rcdset.onfinddata() in object ottstpstandard)
weird because comp is declared!

Maria
Reply With Quote
  #6  
Old 11th July 2019, 12:35
Ajesh's Avatar
Ajesh Ajesh is offline
Guru
 
Join Date: Feb 2009
Posts: 642
Ajesh is on a distinguished road
Baan: LN 10.4 - DB: Oracle - OS: HP-Unix
If you use in

query.extend.where instead of query.extend.where.in.zoom

i mean in the new session which you will create, in before.program, comp should not give an error. I have used it before.
Reply With Quote
  #7  
Old 11th July 2019, 13:20
mvanta mvanta is offline
Member
 
Join Date: May 2004
Location: Athens HELLAS
Posts: 43
mvanta is on a distinguished road
Baan: LN 10.5 - DB: SQL 2014 - OS: Windows 2012
Thank you a lot!!!

Regards,
Maria
Reply With Quote
  #8  
Old 11th July 2019, 18:32
BaanInOhio BaanInOhio is offline
Senior Member
 
Join Date: Oct 2005
Location: Northeast Ohio
Posts: 193
BaanInOhio is on a distinguished road
Baan: Baan 4c4, 5C, LN - DB: Informix, Oracle, SQL - OS: HP UX, Win2K
You have to send the converted string to query.extend.where.in.zoom since the zoom session may not know how to handle 'comp'.

query.extend.where.in.zoom("tcemm170.comp=" & str$(comp))

This will append "tcemm170.comp=100" to the end of the query for the zoom session if the 'curr' variable is returned as 100 in the select.

Use 'quoted.string' when building an extension with a string variable. In this example, 'currency' is a string filled before the call.

query.extend.where.in.zoom("tcemm170.fcua=" & quoted.string(currency))
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
Tool needed to upgrade to ERP LN higer version & Docs needed for ERP LN 10.5 cute_kanku Blogs and Web links 1 15th April 2016 13:01
Baan IV Developer Needed Carl Gonzalez Jobs and Resumes 0 22nd December 2014 19:07
Erp Ln Functional Resources Needed In France teocupa Jobs and Resumes 0 26th August 2013 19:15
LN professionals needed. Tools, Manufacturing, Logistics, Finance, Techno/Functional teocupa Jobs and Resumes 4 27th August 2012 02:23
Baan /Infor Ln Finance consultant needed for an implentation work at Venezuela enterprisepals Jobs and Resumes 0 13th October 2010 04:58


All times are GMT +2. The time now is 07:00.


©2001-2018 - Baanboard.com - Baanforums.com