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 20th April 2011, 11:48
doornbos doornbos is offline
Junior Member
 
Join Date: Jun 2002
Location: The Netherlands
Posts: 27
doornbos is on a distinguished road
Baan: Baan IVc/ERPL LN - DB: Oracle - OS: AIX
Use of _compnr in sql query
Baan: ERP LN 6.1
C/S: None/Unknown

Hi,

I've a problem using _compr in a sql query (ttadv3580m000) in ERP/LN. In BaanIV i used
Code:
... where tfgld106._compr = "100,110,120" and ...
, but in ERP/LN i get error messages.

With
Code:
... where tfgld106._compnr = "110,130" and ...
i get: SQLState 42I89: Invalid company number expression (error 302) (main() in object ottadv3180parse)

With
Code:
... where tfgld106._compnr in (110,130) and ...
i get: SQLState 42I82: _compnr IN predicate is only allowed in parse mode 'ansi' (error 302) (main() in object ottadv3180parse)

With
Code:
... where (tfgld106._compnr = 110 or tfgld106._compnr = 130) and ...
i get: SQLState 42I30: Compnr specification in OR condition (error 302) (main() in object ottadv3180parse)

Did the usage of _compnr in SQL Queries change?

I also tried company_nr, but then i get this error: Unknown column 'tfgld106.company_nr'

How to create a multicompany sql query in ERP/LN?

With kindly regards,

Douwe.
Reply With Quote
Sponsored Links
  #2  
Old 20th April 2011, 13:29
mpfaender mpfaender is offline
Member
 
Join Date: Feb 2006
Location: Germany
Posts: 51
mpfaender is on a distinguished road
Baan: Infor ERP LN 6.1 - DB: MS-SQL-Server - OS: Windows 2003
Hi Douwe,

the _compnr specifies the actual company number of a table. You can use this only with one integer value.

I've never used company_nr. Sorry!

Best regards
michael
Reply With Quote
  #3  
Old 20th April 2011, 15:15
manish_patel's Avatar
manish_patel manish_patel is offline
Senior Member
 
Join Date: Apr 2007
Location: Surat-->Bangalore, INDIA
Posts: 183
manish_patel is on a distinguished road
Baan: Baan IVc4, Baan 5.0 b - DB: Oracle 11g R2 - OS: HP-UX 11.31
What you written in your example should work in Baan 4 and 5.
I can’t comment for LN as I don't have experience.

Could you please share complete where condition?
__________________
Thanks & Regards,
Manish
Reply With Quote
  #4  
Old 20th April 2011, 16:47
doornbos doornbos is offline
Junior Member
 
Join Date: Jun 2002
Location: The Netherlands
Posts: 27
doornbos is on a distinguished road
Baan: Baan IVc/ERPL LN - DB: Oracle - OS: AIX
The complete SQL is

Code:
select
  tccom710.trdt,
  tccom710.orno,
  tccom710.ccde,
  tccom710.fcom,
  tccom710.bpid,
  tccom710.isup.c,
  tccom710.ccty,
  tccom710.casi,
  tccom710.cfrw,
  tccom710.wght,
  tccom710.qana,
  tccom710.ccur,
  tccom710.gdam,
  tccom710.stam,
  tccom710.rate,
  tccom710.ratf,
  tccom710.ttyp.c,
  tccom710.ninv.c,
  tfgld106.fyer,
  tfgld106.fprd,
  tfgld106.ocmp,
  tfgld106.otyp,
  tfgld106.odoc
from 
  tccom710,
  tfgld106
where
  tfgld106._compnr = 110 and
  tfgld106.otyp = tccom710.ttyp.c and
  tfgld106.odoc = tccom710.ninv.c and
  tccom710.corg >= tccom710.corg.f and
  tccom710.corg <= tccom710.corg.t and
  tccom710.kotr >= tccom710.kotr.f and
  tccom710.kotr <= tccom710.kotr.t and
  tfgld106.olin = 1 and
  tfgld106.osrl = 0 and
  tfgld106.osrn = 0 and
  tfgld106.fyer >= tfgld106.fyer.f and
  tfgld106.fyer <= tfgld106.fyer.t and
  tfgld106.fprd >= tfgld106.fprd.f and
  tfgld106.fprd <= tfgld106.fprd.t
Instead of only company 110 I would like to query 15 companies.

With kindly regards,

Douwe.
Reply With Quote
  #5  
Old 20th April 2011, 18:34
shah_bs's Avatar
shah_bs shah_bs is offline
Guru
 
Join Date: Jan 2002
Location: Lewisville, Texas
Posts: 387
shah_bs is on a distinguished road
Baan: BAAN IVc3 with A&D2.2b - DB: ORACLE 9 - OS: HPUX
Since _compnr is not really a database field of the table itself, it is only possible to use the '=' condition for it in the where clause.

In order to do what you need, you will have to place your select inside a 'for-loop' or some loop that 'supplies' one company number at a time.
Reply With Quote
  #6  
Old 20th April 2011, 18:38
vinceco252's Avatar
vinceco252 vinceco252 is offline
Guru
 
Join Date: Mar 2002
Location: Frederick, CO
Posts: 391
vinceco252 is on a distinguished road
Baan: Baan IVc3, IVc4, 5b, 5c, ERP LN 6.1, Infor ERP 10.x - DB: SQL Server, Oracle - OS: Windows, Linux, HPUX, AIX, (a little bit of) Solaris
You could also use a union statement...
__________________
Vincent Murphy
---------------
Senior ERP Consultant
OST
vmurphy@ostusa.com
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
sql query srikarthy Tools Development 6 25th August 2010 09:46
Tips for Multi-company / Calculated items in MS SQL query Hitesh Shah Blogs and Web links 0 23rd May 2009 12:18
EASY SQL QUERY: Japanese Form Label not displayed completely. fredintaiwan Tools Development 0 4th December 2008 06:59
Strange error while importing tables: Can not create index 9999 eppesuiG Tools Administration & Installation 8 17th March 2008 04:35
An Oracle SQL Query ..... metropoj Tools Development 1 20th February 2008 21:04


All times are GMT +2. The time now is 03:42.


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