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.

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.

Hi Milind, What are you trying to do with that message command in select?

Anyway, try this. It will work.



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

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.

I am not sure about the company_nr, as per this Thread (http://www.baanboard.com/baanboard/showthread.php?t=62731) 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! (http://www.baanboard.com/baanboard/showthread.php?t=12792&highlight=._compnr)

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

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.


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.