OP Multi query selection

4D - Documentation   Français   English   German   4D Open for 4D, Command Theme List   4D Open for 4D, Command Alphabetical List   Back   Previous   Next

version 1.5


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

ParameterTypeDescription
connectionIDLongintConnection ID with target server
tableIDLongintNumber of the table in the database
tableIDsArrayArray of related tables numbers in the database
fieldIDsArrayArray of related field numbers in their tables
logicalOperatorsArrayQuery lines logical conjunctors
queryOperatorsArrayArray of query operators
queryValuesArrayArray of query values
recordsFoundLongintNumbers of records in resulting selection
Function resultNumberError code result for the function

Description

OP Multi query selection queries tableID using fields (from multiple tables) passed in fieldIDs. The new current selection is build from tableID 's current selection.

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 of the array 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:

DescriptionAlpha ArrayNumeric 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:

DescriptionAlphaNumeric
ArrayArray
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, you must pass "false" for False and "true" for True.

Error Codes

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

Error CodeDescription
-9969Invalid field type requested.
-9971Field number is out of range.
-9972Table number is out of range.
10128The 4D Open for 4th Dimension package
has not been initialized.
10135Invalid parameter type.
10136The connection does not exist.
10151Invalid query operator.
10152Invalid logical operator.
10153Unable to convert this type of data.
10154This command cannot be executed right now.
10156Empty 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 array 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.

Example

This example searches for invoices concerning customers located in Ohio or Texas on a selection of invoices prviously made by the user

. 
   C_LONGINT($ErrCode;$TrgTable;vRecords)

   ARRAY LONGINT(arTableID;0)
   ARRAY LONGINT(arFieldID;0)
   
   ARRAY TEXT(arLogicalOp;0)
   ARRAY TEXT(arQueryOp;0)
   ARRAY TEXT(arValues;0)

   UserSelectInvoices    `Display a dialog that let the user specify criteria

   ` Perform the query specified by the user
   OP Multi query (vConnectID;2;arFileID;arFieldID;
      arLogicalOp;arQueryOp;arValues;vRecords)

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

   ` Then queries the user-defined selection with new criteria

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

See Also

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


4D - Documentation   Français   English   German   4D Open for 4D, Command Theme List   4D Open for 4D, Command Alphabetical List   Back   Previous   Next