OP Multi query

version 1.5

OP Multi query (connectionID; tableID; tableIDs; fieldIDs; logicalOperators; queryOperators; queryValues; recordsFound) Longint

Parameter	Type		Description
connectionID	Longint		Connection ID with target server
tableID	Longint		Number of the table in the database
tableIDs	Array		Array of related tables numbers in the database
fieldIDs	Array		Array of related field numbers in their tables
logicalOperators	Array		Query lines logical conjunctors
queryOperators	Array		Array of query operators
queryValues	Array		Array of query values
recordsFound	Longint		Numbers of records in resulting selection
Function result	Longint		Error code result for the function

OP Multi query queries tableID using fields (from multiple tables) passed in fieldIDs. The new current selection is built from all the records in tableID. The maximum number of elements for any array is 20. If you pass an array with more than 20 elements, only the first 20 elements will be used, but no error will be returned.

tableIDs and fieldIDs are arrays of type Integer or Longint, containing the table and field numbers of the tables and fields to query.

logicalOperator and queryOperator are arrays of type Integer, Longint, Real, String or Text, containing the logical and query operators.

The logical operators are as follows:

Description	Alpha Array	Numeric Array
And	"&"	1
Or	"|"	2
Except	"#"	0

The logical operator connects each query line to the result obtained by the previous line(s). Therefore, the logical operator for the first search line is simply ignored.

The query operators are as follows:

Description	Alpha Array	Numeric Array
Equal	"="		1
Not equal	"#"		2
Greater than	">"		3
Greater than or equal to	">="		4
Less than	"<"		5
Less than or equal to	"<="		6

queryValue is an array of type String or Text that contains one or more values that can begin with, contain or end with the "@" symbol for wildcard queries.

Values for each data type are passed as strings. For example, to query for a date, pass "12/03/97" for December 3, 1997. To query for a Boolean value, pass "false" for False and "true" for True.

If OP Multi query executes successfully, it returns 0. Otherwise, this function returns one of the following errors:

Error Code	Description
-9969	Invalid field type requested.
-9971	Field number is out of range.
-9972	Table number is out of range.
10128	The 4D Open for 4th Dimension package has not been initialized.
10135	Invalid parameter type.
10136	The connection does not exist.
10151	Invalid query operator.
10152	Invalid logical operator.
10153	Unable to convert this type of data.
10154	This command cannot be executed right now.
10156	Empty search or sort definition.

Note: If there is an error due to an element of one of the arrays that you have passed, OP Multi query sets the selected element number of each of the arrays to the number of the faulty element. For example, if there is an error in the fifth element of one of the arrays, the selected element number of all of the arrays is set to 5.

This example searches for invoices concerning customers located in Ohio or Texas with an invoice total greater than 2000. It uses the following structure:

   C_LONGINT($ErrCode;$TrgTable;vRecords)
   
   ARRAY LONGINT(arTableID;3)
   ARRAY LONGINT(arFieldID;3)
   
   ARRAY TEXT(arLogicalOp;3)
   ARRAY TEXT(arQueryOp;3)
   ARRAY TEXT(arValues;3)
   
   
   $TrgTable:=2         ` We want to build a selection of records on [Invoices]
   
   arTableID{1}:=1      `       [Customers]
   arTableID{2}:=1      `       [Customers]
   arTableID{3}:=2      `       [Invoices]
   
   arFieldID{1}:=6      `       [Customers]State
   arFieldID{2}:=6      `       [Customers]State
   arFieldID{3}:=4      `       [Invoices]Invoice total
   
   arQueryOp{1}:="="   `       [Customers]State =
   arQueryOp{2}:="="   `       [Customers]State =
   arQueryOp{2}:=">"   `       [Invoices]Invoice total >
   
   arValues{1}:="Texas"   `       [Customers]State = "Texas"
   arValues{2}:="Ohio"   `       [Customers]State = "Ohio"
   arValues{3}:="2000"   `       [Invoices]Invoice total > 2000
   
   arLogicalOp{1}:=""   `       [Customers]State = "Texas"
   arLogicalOp{2}:="|"   ` or   [Customers]State = "Ohio"
   arLogicalOp{2}:="&"   ` and [Invoices]Invoice total > 2000

   $ErrCode:=OP Multi query (vConnectID;$TrgFile;arFileID;arFieldID;
      arLogicalOp;arQueryOp;arValues;vRecords)
   
   If ($ErrCode=0)
      ALERT(String(vRecords)+" record(s) were found.")
   End if 

OP Multi query selection, OP Single order by, OP Single query, QUERY.