hi,

what query shuld i write to get recode frm table tdinr140 with 900 compny and 700 compny. In BaaniV i write (tdilc101._compnr=900 or tdilc101._compnr=700) . But INLNP its not working.

hi,

what query shuld i write to get recode frm table tdinr140 with 900 compny and 700 compny. In BaaniV i write (tdilc101._compnr=900 or tdilc101._compnr=700) . But INLNP its not working.

Hello,

have a look at your dev guide :

company_nr predicate
The company_nr predicate is used to restrict the set of possible values for the company_nr column. In that respect a company_nr predicate is just the same as any other search condition involving an arbitrary column reference.

One aspect in which the company_nr predicate deviates is that when no company_nr predicate is specified, then the set of possible values for the company_nr column is restricted to just one company number: the current company number of the user.

Syntax

<company_nr predicate>
::= <company_nr column reference> = <company value>
| <company_nr column reference> IN <company number set>


<company_nr column reference>
::= !! a Column reference whose column name is company_nr

<company value>
::= <company number>
| <company_nr column reference>
| Parameter

<company number set>
::= ( <company number> [{, <company number>}...] )

<company number>
::= Integer constantSyntactical restrictions

I. The value of the <company number> must lie between 0 and 999, all inclusive.

II. The <parameter> must be of type integer.

III. A <company_nr predicate> shall neither be contained in an OR condition nor be contained in a NOT condition.

Added !!! -------------------------

The following example demonstrates the incorrect use of a <company_nr predicate> in a OR condition.

SELECT * FROM dbtst120
WHERE dbtst120.company_nr IN (100,200) OR empno = 10
IV. A query specification shall contain at most one <company number set>.

The following example demonstrates the incorrect use of more than one <company number set> in one query specification.

SELECT * FROM dbtst120, dbtst100
WHERE dbtst120.company_nr IN (100,200) AND dbtst100.company_nr IN (200,300)
V. At most one <company_nr predicate> shall reference any one table reference.

The following example demonstrates the incorrect use of more than one <company_nr predicate> referencing the same table reference.

SELECT * FROM dbtst120
WHERE company_nr = 100 AND company_nr = 200
VI. If a company number set of any table reference is restricted with a <company_nr predicate> then the company number set of everytable reference shall be restricted with a <company_nr predicate>.

The following example demonstrates the incorrect use of more than one <company_nr predicate> referencing the same table reference.

SELECT * FROM dbtst120 AS "emps" LEFT JOIN dbtst100 ON workdept = deptno
WHERE "emps".company_nr = 100
Semantics

The company_nr predicate always evaluates to True.

Examples with correct syntax :

The following company_nr predicate restricts the set of possible company numbers of table dbtst120 to the single-element set containing the value 812.

dbtst120.company_nr = 812
The following company_nr predicate restricts the set of possible company numbers of table dbtst120 to the single-element set containing the value of the parameter current.compnr at the time the containing SQL statemtent is executed.

dbtst120.company_nr = :current.compnr
The following example restricts table dbtst120 ("emps") to company numbers 100 and 200, and because of the join condition on the company_nr column, also restricts table dbtst100 ("depts") to company number 100 and 200. Note that the data of the tables is joined only within a single company number, as requested by the ON condition of the join.

SELECT "emps".firstnme, "depts".deptname
FROM dbtst120 AS "emps" LEFT JOIN dbtst100 AS "depts"
ON workdept = deptno AND "emps".company_nr = "depts".company_nr
WHERE "emps".company_nr IN (100,200)


Regards

Hi Bernd ,

Thank you so much for your update but i am not able to understand how do i use this in my code. Can you please make it little bit simpler!

select whinr140.*
from whinr140
where whinr140.cwar inrange :cwar.f and :cwar.t
and whinr140.item inrange :item.f and :item.t
and whinr140._compnr in (700, 900)

but getting this error
pwhaal201510( 68): Error SQL: SQLState 42I82: _compnr IN predicate is only allowed in parse mode 'ansi' (error 302)

what to do friennds please help me out.................

See this thread thread (http://www.baanboard.com/baanboard/showthread.php?t=12792&highlight=._compnr). From what I gather it should be "700, 900". Found that searching the forum for "._compnr" - you can see other threads if you do the search.

Yogesh,

As Bernd pointed to the docs - it is not possible to have the company company number in an "or" condition. The way I read into this is that you cannot pick a table contents from two different companies. Then what is a guy to do? My search in the programmers guide for "_compnr" yielded a result for "UNION operator". In this article there is a sample sql

SELECT _compnr, firstnme, lastname
FROM dbtst120
WHERE salary > 40000
AND _compnr = 000

UNION ALL

SELECT _compnr, firstnme, lastname
FROM dbtst120
WHERE salary > 40000
AND _compnr = 001

ORDER BY _compnr


That looks similar to what you are trying to accomplish. I am not sure if this will work or if it is the best approach. Please test and post your results.

While I was writing my post, Bernd must have been updating his. His clarification with the use of company_nr and the "in" operator looks much more elegant. Am going to leave my original post as an example of how not to do things :).

HI ~Vamsi,

its working very fine and getting right result. The two company problem get resolved. Thanks so much. You are doing great job.



SELECT whinr140.*
FROM whinr140
where whinr140.item inrange :item.f and :item.t
and whinr140.cwar inrange :cwar.f and :cwar.t
and _compnr = 900


UNION ALL

SELECT whinr140.*
FROM whinr140
where whinr140.item inrange :item.f and :item.t
and whinr140.cwar inrange :cwar.f and :cwar.t
and _compnr = 700














Yogesh,

As Bernd pointed to the docs - it is not possible to have the company company number in an "or" condition. The way I read into this is that you cannot pick a table contents from two different companies. Then what is a guy to do? My search in the programmers guide for "_compnr" yielded a result for "UNION operator". In this article there is a sample sql

SELECT _compnr, firstnme, lastname
FROM dbtst120
WHERE salary > 40000
AND _compnr = 000

UNION ALL

SELECT _compnr, firstnme, lastname
FROM dbtst120
WHERE salary > 40000
AND _compnr = 001

ORDER BY _compnr


That looks similar to what you are trying to accomplish. I am not sure if this will work or if it is the best approach. Please test and post your results.

HI mark_h,

The given thered is very helpfull. Thank you Mark.












See this thread thread (http://www.baanboard.com/baanboard/showthread.php?t=12792&highlight=._compnr). From what I gather it should be "700, 900". Found that searching the forum for "._compnr" - you can see other threads if you do the search.

Hi Bernd,

My problem get solved as Mark ponied out. thank you so much for your posting.


Hello,

have a look at your dev guide :

company_nr predicate
The company_nr predicate is used to restrict the set of possible values for the company_nr column. In that respect a company_nr predicate is just the same as any other search condition involving an arbitrary column reference.

One aspect in which the company_nr predicate deviates is that when no company_nr predicate is specified, then the set of possible values for the company_nr column is restricted to just one company number: the current company number of the user.

Syntax

<company_nr predicate>
::= <company_nr column reference> = <company value>
| <company_nr column reference> IN <company number set>


<company_nr column reference>
::= !! a Column reference whose column name is company_nr

<company value>
::= <company number>
| <company_nr column reference>
| Parameter

<company number set>
::= ( <company number> [{, <company number>}...] )

<company number>
::= Integer constantSyntactical restrictions

I. The value of the <company number> must lie between 0 and 999, all inclusive.

II. The <parameter> must be of type integer.

III. A <company_nr predicate> shall neither be contained in an OR condition nor be contained in a NOT condition.

Added !!! -------------------------

The following example demonstrates the incorrect use of a <company_nr predicate> in a OR condition.

SELECT * FROM dbtst120
WHERE dbtst120.company_nr IN (100,200) OR empno = 10
IV. A query specification shall contain at most one <company number set>.

The following example demonstrates the incorrect use of more than one <company number set> in one query specification.

SELECT * FROM dbtst120, dbtst100
WHERE dbtst120.company_nr IN (100,200) AND dbtst100.company_nr IN (200,300)
V. At most one <company_nr predicate> shall reference any one table reference.

The following example demonstrates the incorrect use of more than one <company_nr predicate> referencing the same table reference.

SELECT * FROM dbtst120
WHERE company_nr = 100 AND company_nr = 200
VI. If a company number set of any table reference is restricted with a <company_nr predicate> then the company number set of everytable reference shall be restricted with a <company_nr predicate>.

The following example demonstrates the incorrect use of more than one <company_nr predicate> referencing the same table reference.

SELECT * FROM dbtst120 AS "emps" LEFT JOIN dbtst100 ON workdept = deptno
WHERE "emps".company_nr = 100
Semantics

The company_nr predicate always evaluates to True.

Examples with correct syntax :

The following company_nr predicate restricts the set of possible company numbers of table dbtst120 to the single-element set containing the value 812.

dbtst120.company_nr = 812
The following company_nr predicate restricts the set of possible company numbers of table dbtst120 to the single-element set containing the value of the parameter current.compnr at the time the containing SQL statemtent is executed.

dbtst120.company_nr = :current.compnr
The following example restricts table dbtst120 ("emps") to company numbers 100 and 200, and because of the join condition on the company_nr column, also restricts table dbtst100 ("depts") to company number 100 and 200. Note that the data of the tables is joined only within a single company number, as requested by the ON condition of the join.

SELECT "emps".firstnme, "depts".deptname
FROM dbtst120 AS "emps" LEFT JOIN dbtst100 AS "depts"
ON workdept = deptno AND "emps".company_nr = "depts".company_nr
WHERE "emps".company_nr IN (100,200)


Regards