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 12th July 2013, 11:17
MilindV's Avatar
MilindV MilindV is offline
Senior Member
 
Join Date: Feb 2008
Location: Hyderabad, India
Posts: 107
MilindV is on a distinguished road
Baan: LN, BaaN-IVc4, BaaN-Vc - DB: SQL Server - OS: Windows, Unix
Question company_nr
Baan: Other/Unknown
C/S: None/Unknown

Hi All,

Has any one used this function to retrieve data from more than one company?

I have already searched through the forum to retrieve data from multiple companies.
I saw threads where this function usage is pasted from Prog Guide.
I saw post where _compnr is used like _compnr = "100,200".
_compnr = "100,200" is not working for me. I am getting compilation error.

I am writing query in a script. My requirement id not to show data from multiple companies on session.

Please let me know if any one has successfully used company_nr predicate to retrieve data from multiple companies.

This is what I have tried. But is giving me compilation error, unknown column tdpur400.company_nr.

Code:
	select	tdpur400.orno, tdpur400.otbp, tdpur400.oamt, tdpur400.odat, tdpur400.cpay
	from	tdpur400
	where	tdpur400.orno inrange {:orno.f} and {:orno.t}
	and	tdpur400.company_nr IN (801,902)
	selectdo
		Message(concat$("|", 	tdpur400.orno, tdpur400.otbp,
					tdpur400.oamt, tdpur400.odat,
					tdpur400.cpay, tdpur400._compnr))
		l.count = l.count + 1
	endselect
Union All is what other option I am aware of, but I am not sure how effective it is with lot of conditions in where clause and complex queries.

I am asking this, because I have an requirement.
Where I will get invoice number, I need to return back all the information for that invoice (additionally BP Name, BP Address etc), the invoice may present in current company or some other finance company.

Thank You.
__________________
Regards,
--MilindV
Reply With Quote
  #2  
Old 12th July 2013, 11:31
bhushanchanda's Avatar
bhushanchanda bhushanchanda is offline
Guru
 
Join Date: Sep 2012
Location: India
Posts: 2,286
bhushanchanda has a spectacular aura aboutbhushanchanda has a spectacular aura aboutbhushanchanda has a spectacular aura about
Baan: LN FP 1-9, 10.4, a little bit of Baan IV - DB: SQL Server 2008, Oracle - OS: Windows Server 2008 R2, Unix
Hi Milind, What are you trying to do with that message command in select?

Anyway, try this. It will work.



Code:
select ttaad100.*
from   ttaad100
where ttaad100._compnr = 0
and    ttaad100.comp inrange 801 and 902
selectdo
select	tdpur400.*
	from	tdpur400
	where	tdpur400.orno inrange {:orno.f} and {:orno.t}
	and	tdpur400._compnr = :ttaad100.comp
	selectdo
		Message(concat$("|", 	tdpur400.orno, tdpur400.otbp,
					tdpur400.oamt, tdpur400.odat,
					tdpur400.cpay, ttaad100.comp))
		l.count = l.count + 1
	endselect
endselect
__________________
Regards,

Bhushan

Unless you try to do something beyond what you have already mastered, you will never grow!
Reply With Quote
Sponsored Links
  #3  
Old 12th July 2013, 12:12
MilindV's Avatar
MilindV MilindV is offline
Senior Member
 
Join Date: Feb 2008
Location: Hyderabad, India
Posts: 107
MilindV is on a distinguished road
Baan: LN, BaaN-IVc4, BaaN-Vc - DB: SQL Server - OS: Windows, Unix
Thanks Bhushan ,

The code I wrote is just dummy/test code to check company_nr predicate.

The option that you have provided appears some what similar to UNION ALL.

I am just wondering, if company_nr does not work why it is there in prog guide.
__________________
Regards,
--MilindV
Reply With Quote
  #4  
Old 12th July 2013, 12:28
bhushanchanda's Avatar
bhushanchanda bhushanchanda is offline
Guru
 
Join Date: Sep 2012
Location: India
Posts: 2,286
bhushanchanda has a spectacular aura aboutbhushanchanda has a spectacular aura aboutbhushanchanda has a spectacular aura about
Baan: LN FP 1-9, 10.4, a little bit of Baan IV - DB: SQL Server 2008, Oracle - OS: Windows Server 2008 R2, Unix
I am not sure about the company_nr, as per this Thread it is just to get the data from companies, which might be similar to what I posted. But, as your requirement is just to retrieve the data from multiple companies, it's always better to use ._compnr choice. At least we use this without any problems.

For your reference: -

Thread!
__________________
Regards,

Bhushan

Unless you try to do something beyond what you have already mastered, you will never grow!

Last edited by bhushanchanda : 12th July 2013 at 12:36.
Reply With Quote
  #5  
Old 12th July 2013, 13:12
bdittmar's Avatar
bdittmar bdittmar is offline
Guru
 
Join Date: Apr 2002
Location: Germany, 50.584097,8.544078
Posts: 1,639
bdittmar will become famous soon enough
Baan: 2.2/3.1/4c4/LN6.1 FP6/FP9/HiDox - DB: tbase, ms-sql7, oracle10gV1, 11g - OS: HP-UX, W2K3, SLES
From Progguide !

Hello,

this is from ERP progguide :

BaaN ERP COMPANY NUMBERS and OPERATION

BaanERP Programmers Guide


Company numbers

--------------------------------------------------------------------------
The company number used during execution of a BAAN 4GL query is the current company number. The current company number is:

the default company number ( defined in the session "Maintain User Data" )
a company number specified by the compnr.check() function
The <table>._compnr field
The <table>._compnr field enables the use of different company numbers. This field specifies the actual company number of the table. When this field is undefined, the current company number is taken as the default. 'Undefined' is denoted by the value -1.

When a record is selected with 'select <table>.*', the field <table>._compnr in the record contains the company number of the table. When inserting a record in a table with a specific company number, you must fill <table>._compnr with the corresponding company number. The function db.insert() takes care of this and writes the record in the correct (physical) table. No special file pointers are necessary (for example, by using db.bind()).

Example
The record where the field ttadv100._compnr = 000 belongs to the
(physical) table ttadv100000
The record where the field ttadv100._compnr = 200 belongs to the
(physical) table ttadv100200

Using <table>._compnr in the WHERE clause
You can also use <table>._compnr in the WHERE clause of a Baan SQL statement. In this case it is used as a search condition. For example:

SELECT ttadv100.*
FROM ttadv100
WHERE ttadv100._compnr = 200 AND ttadv100.cpac = 'tt'

This query lists all records from table ttadv100200 where ttadv100.cpac has the value 'tt'.

You can use <table>._compnr in the following ways:

<table>._compnr = <number> or <number> = <table>._compnr where <number> specifies an integer number. The value of <number> is taken as the company number. It must be a 3-digit number.
<table>._compnr = <bind variable> or <bind variable> = <table>._compnr where <bind variable> specifies an integer number. When the query is executed the value of <bind variable> is taken as the company number. It must be a 3-digit number.
Combining <table>._compnr conditions
A list of AND conditions can contain only one condition that includes <table>._compnr. So, the following construction is possible:
WHERE <table>._compnr = 100 AND <condition>
but the following is not possible:
WHERE <table>._compnr = 100 AND <table>._compnr = 200


It is possible to include multiple <table>._compnr._compnr conditions that specify the company number of different table references. For example, the following construction is possible:
WHERE <table1>._compnr = 100 AND <table2>._compnr = 200

In a list of OR conditions, none of the conditions may contain a condition on <table>._compnr.




BaanERP Programmers Guide


Company operations overview and synopsis

--------------------------------------------------------------------------
Overview
Use these functions to switch to another company and to retrieve information about the current company.

Synopsis
long
compnr.check ( long new_compnr )
string
get.company ( )
long
get.compnr ( )
long
switch.to.company ( long new_compnr )







BaanERP Programmers Guide


compnr.check()

--------------------------------------------------------------------------
Syntax
long compnr.check( long new_compnr )
Description
This switches to another company and sets the read-only variable COMPNR to the specified company number. It checks whether the user is authorized for the new company, and switches company only if so.

Notes
compnr.check() and switch.to.company() perform similar functions, but switch.to.company() performs additional checks. So switch.to.company() makes heavier demands on system resources.

Be aware that when there is a switch to another company, the user's first day of the week does not change, even if the new company has a different first day of the week. switch.to.company() returns an error value if the old and new companies have different first days of the week. compnr.check() does not.

Super users are authorized for all company numbers. They always have authority to switch to a different company number. In addition, super users can use the data dictionary to authorize other users for particular company numbers.

Return values
<>0 True. User is authorized; Company switched.

0 False. User is not authorized; Company not switched.


Context
Bshell function.

Example
if ( not compnr.check(200) ) then
message( "No permission to change to this company number" )
endif





BaanERP Programmers Guide


switch.to.company()

--------------------------------------------------------------------------
Syntax
long switch.to.company( long new_compnr )
Description
This switches to another company and sets the read-only variable COMPNR to the specified company number. It performs several checks before switching company and switches company only if the checks are successful. For example, it checks if the user is authorized to change to the new company, it checks if the package combination corresponds with the new company number, and it checks if the old and new companies have the same first day of the week.

Notes
compnr.check() and switch.to.company() perform similar functions, but switch.to.company() performs additional checks. So switch.to.company() makes heavier demands on system resources.

Be aware that when there is a switch to another company, the user's first day of the week does not change, even if the new company has a different first day of the week. switch.to.company() returns an error value if the old and new companies have different first days of the week. compnr.check() does not.

Super users are authorized for all company numbers. They always have authority to switch to a different company number. In addition, super users can also use the data dictionary to authorize other users for particular company numbers.

Return values
1 Success.

-1 Company not available.

-2 No permission to change to this company.

-3 Package combination does not correspond with new company number.

-4 First day of the week does not correspond to first day of the week of original company.





Regards
__________________
//Bernd
Reply With Quote
  #6  
Old 12th July 2013, 14:49
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,903
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Well I can't speak to LN and the format, but on 4c4 I would just make it a function and pass the company number into it. That is how I do it for report purposes - it is simple and it works for me.

Code:
function get_data(domain tccomp some.compnr)
{
	select	tdpur400.orno, tdpur400.otbp, tdpur400.oamt, tdpur400.odat, tdpur400.cpay
	from	tdpur400
	where	tdpur400.orno inrange {:orno.f} and {:orno.t}
	and	tdpur400._compnr = :some.compnr
	selectdo
		Message(concat$("|", 	tdpur400.orno, tdpur400.otbp,
					tdpur400.oamt, tdpur400.odat,
					tdpur400.cpay, tdpur400._compnr))
		l.count = l.count + 1
	endselect
}

Then I just call the routine for each company needed. Granted I only ever have to worry about two companies at most. Plus all of my cross company data is for reports. I have not done any display sessions for cross company data. I am not sure how I would do that.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
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


All times are GMT +2. The time now is 20:29.


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