Codes 2xxx
Advantage Error Guide
Contents
2008 ISAM memory allocation failure 2009 ISAM path or file name too long 2010 ISAM table already exists 2011 ISAM index expression not valid 2012 ISAM column name too long 2013 Stack limit exceeded due to recursion 2014 Invalid ROWID 2015 ISAM path or file name is not valid 2101 Memory allocation failure 2102 Data truncated 2103 Functionality not supported 2104 Invalid cursor name 2105 Cursor name in use 2106 Connection in use 2107 Invalid Cursor State 2108 Invalid column id 2109 Conversion error 2110 Driver not capable 2111 Out of range 2112 Assignment error 2113 Unexpected end of statement 2114 Element too big 2115 Expected lexical element not found 2116 Malformed number 2117 Unexpected extra token 2118 Malformed escape clause 2119 Internal error 2120 ISAM error 2121 Column not found 2122 Table not found 2123 Data type of parameter cannot be determined 2124 Invalid operand for operator 2125 Invalid table name 2126 ORDER BY ordinal greater than select list count 2127 Too many ORDER BY items 2128 Cannot ORDER BY an expression 2129 Unequal number of insert columns and insert values 2130 Insert value was not valid 2131 Value in an IN expression must be a simple value 2132 Table alias used multiple times 2133 Column on list multiple times 2134 Invalid column name 2135 No such type 2136 Invalid number of parameters for type 2137 Column found in multiple tables 2139 Invalid connection string 2140 Unable to connect 2141 Parameter missing 2142 - 2144 Internal Error 2145 Unable to ORDER BY this column 2146 Unable to perform sort for ORDER BY or DISTINCT 2147 GROUP BY contains too many columns 2148 Unable to GROUP BY this column 2149 Aggregate function not allowed in this context 2150 SELECT * not allowed if there is a GROUP BY 2151 Unable to do sort for GROUP BY 2152 HAVING specified without a GROUP BY 2153 Division by zero 2154 The data type of a parameter cannot be determined 2155 String concatenation overflow 2156 Invalid index name 2157 Too many key columns for index 2158 Scalar function not found 2159 Invalid argument to scalar function 2160 - 2165 Internal Error 2166 SELECT sub-query returns more than one column 2167 SELECT sub-query returned more than one row 2168 - 2188 Internal Error 2189 Inconsistent number of columns in the result sets participating in the UNION statement 2190 Incompatible column data types in the result sets participating in the UNION statement 2191 Invalid Data Type 2192 GROUP BY and HAVING are not supported in UNION statement 2194 Unable to perform DISTINCT operation on some of the columns 2195 Column in the ORDER BY clause is not in the result set 2196 Column not found in GROUP BY clause 2197 Illegal VIEW operation 2198 Invalid PROCEDURE name 2200 Error removing temporary input file 2201 Invalid Ordinal 2202 Invalid use of constant value 2203 Error encountered while reading an intermediate file 2204 The key specified as the primary key is too large 2205 A column specified in the index is of an invalid type 2206 Update statement is ambiguous 2207 Error creating input or output table temp filename 2208 A stored procedure's input value was truncated 2209 Table not unique 2210 Invalid index option 2211 CONTAINS(*) not allowed in GROUP BY statement 2212 Aggregate cannot be nested 2213 Invalid Collation 2214 Invalid Coercion 2215 Invalid options in CREATE TABLE statement 2216 Unexpected end of the SQL script 2217 A declaration statement in the SQL script is not valid 2218 Variable not found 2219 General SQL script error 2220 Cursor not open 2221 Cursor already open 2222 Invalid cursor column reference 2223 Cursor is not on a valid row 2224 An exception was raised in the SQL script 2225 Assignment error in the SQL script 2226 Invalid usage of a subquery 2227 Assignment out of range in the SQL script 2228 Invalid view definition 2229 Ambiguous reference in the SQL script 2230 Bad Function Definition 2231 Numeric Result Out Of Range 2232 Numeric Overflow 2233 Invalid DEBUG command 2234 Invalid Use of Parameter error_2235_select_from_aep_with_no_output 2236 Conversion Error error_2239_Invalid_Procedure_Arguments
2008 ISAM memory allocation failure
Problem: An allocation failed inside the ISAM layer in the SQL engine.
Solution: The query may require more memory to execute. Either reduce the scope of the query or provide additional memory.
2009 ISAM path or file name too long
Problem: Either the connection path or the combination of the connection path and the table name being accessed is too long.
Solution: The fully combined path name must be 255 characters or less. Verify that your SQL statement does not exceed this.
2010 ISAM table already exists
Problem: The table named in a CREATE TABLE statement already exists.
Solution: The query engine does not allow you to overwrite existing tables with the CREATE TABLE statement. Use the DROP TABLE statement to delete the existing table first.
2011 ISAM index expression not valid
Problem: The index cannot be created for the specified index.
Solution: Make sure the specified field is not a memo type or the index key size is within the range supported by Advantage. See Advantage Proprietary File Format Specifications and Xbase File Format Specifications for information on the maximum index key sizes.
2012 ISAM column name too long
Problem: The column name exceeded the SQL engine limit of 128 characters.
Solution: Restructure the table so that all field names are 128 characters or less.
2013 Stack limit exceeded due to recursion
Problem: The runtime stack limit was reached during the processing of an SQL statement. The Advantage Database Server for Windows has a stack size of 1 MB.
Solution: If you encounter this error, reduce the complexity of the SQL statement.
2014 Invalid ROWID
Problem: An invalid ROWID value is specified in an SQL statement.
Solution: Make sure that the ROWID value is correct by SELECTING the ROWID from the table.
2015 ISAM path or file name is not valid
Problem: An invalid table name or index name is specified in the SQL statement. This problem is usually the result of specifying an invalid relative path for the table or index.
Solution: Make sure that the table name or index name is correct.
2101 Memory allocation failure
Problem: An allocation failed inside the SQL engine.
Solution: The query may require more memory to execute. Either reduce the scope of the query, or provide additional memory.
2102 Data truncated
Problem: An internal buffer within the SQL engine was too small, and data was truncated when copied into it. The most likely cause of this error is the result of data conversions. For example if you set a DBF numeric field parameter with a double or a character value, it might overflow the field width or precision. The SQL engine returns this code as a warning. All updates still occur when this code is returned. For example, if an UPDATE statement is executed that assigns too many digits of precision to a numeric field, the 2102 code will be returned, but all updates on all applicable rows will still be performed.
Solution: Verify that the statement is using compatible data types.
2103 Functionality not supported
Problem: The Advantage Database Server does not support some functionality used in the SQL statement. For example, unsupported field types, stored procedures, and parameters inside scalar functions.
Solution: Verify the statement contains valid field types and the functionality is supported.
2104 Invalid cursor name
Problem: An invalid cursor name is specified.
Solution: This error indicates an internal problem in the Advantage SQL engine. In general, this error should not be returned to the application directly. If the application does receive this error, the SQL statement handle should not be used any further. Close the existing SQL statement handle and create and use a new one. Please report the error with a small sample application demonstrating the issue to Advantage Technical Support at advantage@extendsys.com.
2105 Cursor name in use
Problem: Specified cursor name is already in use.
Solution: This error indicates an internal problem in the Advantage SQL engine. In general, this error should not be returned to the application directly. If the application does receive this error, the SQL statement handle should not be used any further. Close the existing SQL statement handle and create and use a new one. Please report the error with a small sample application demonstrating the issue to Advantage Technical Support at advantage@extendsys.com.
2106 Connection in use
Problem: The connection is already in use.
Solution: This error indicates an internal problem in the Advantage SQL engine. In general, this error should not be returned to the application directly. If the application does receive this error, the SQL statement handle should not be used any further. Close the existing SQL statement handle and create and use a new one. Please report the error with a small sample application demonstrating the issue to Advantage Technical Support at advantage@extendsys.com.
2107 Invalid Cursor State
Problem: The SQL statement handle is not in a correct state to complete the requested operation.
Solution: This error indicates an internal problem in the Advantage SQL engine. In general, this error should not be returned to the application directly. If the application does receive this error, the SQL statement handle should not be used any further. Close the existing SQL statement handle and create and use a new one. Please report the error with a small sample application demonstrating the issue to Advantage Technical Support at advantage@extendsys.com.
2108 Invalid column id
Problem: An invalid column id is used.
Solution: This error indicates an internal problem in the Advantage SQL engine. In general, this error should not be returned to the application directly. If the application does receive this error, the SQL statement handle should not be used any further. Close the existing SQL statement handle and create and use a new one. Please report the error with a small sample application demonstrating the issue to Advantage Technical Support at advantage@extendsys.com.
2109 Conversion error
Problem: The SQL engine was unable to perform the conversion from one data type to another. This error can occur when using parameterized queries when the data assigned to a parameter does not match the data in the table. It can also occur when using the scalar function CONVERT or CAST incorrectly.
Solution: Verify that the statement is using compatible data types.
2110 Driver not capable
Problem: The SQL engine cannot complete the request.
Solution: This error indicates an internal problem in the Advantage SQL engine. In general, this error should not be returned to the application directly. If the application does receive this error, the SQL statement handle should not be used any further. Close the existing SQL statement handle and create and use a new one. Please report the error with a small sample application demonstrating the issue to Advantage Technical Support at advantage@extendsys.com.
2111 Out of range
Problem: A data conversion within the SQL engine exceeded the range of the target value. For example, a parameter value stored as a double may have exceeded a short integer field range. Or there is data corruption in the tables used by the SQL query.
Solutions:
2112 Assignment error
Problem: A data conversion within the SQL engine failed due to an incorrect data type. For example, if you assign a numeric parameter value with a character string that contains non-numeric characters, you will get this error.
Solution: Verify that the parameter values are correct.
2113 Unexpected end of statement
Problem: The SQL parser encountered the end of the SQL statement.
Solution: Verify that the SQL statement is valid. For example, make sure that comments in the SQL statements are terminated properly.
2114 Element too big
Problem: The SQL parser encountered a token that was too large.
Solution: Verify that individual tokens (aside from character string constants) do not exceed 1024 bytes.
2115 Expected lexical element not found
Problem: The SQL parser encountered an unexpected token.
Solution: Verify that the SQL statement is valid.
2116 Malformed number
Problem: The SQL parser encountered an invalid numeric value.
Solution: Verify that the SQL statement is valid.
2117 Unexpected extra token
Problem: The SQL parser found unexpected tokens at the end of the SQL statement.
Solution: Verify that the SQL statement is valid.
2118 Malformed escape clause
Problem: The SQL parser encountered an invalid escape clause.
Solution: Verify that the SQL statement is valid.
2119 Internal error
Problem: The Advantage SQL engine encountered an internal error.
Solution: This error indicates an internal problem in the Advantage SQL engine. In general, this error should not be returned to the application directly. If the application does receive this error, the SQL statement handle should not be used any further. Close the existing SQL statement handle and create and use a new one. Please report the error with a small sample application demonstrating the issue to Advantage Technical Support at advantage@extendsys.com.
2120 ISAM error
Problem: Advantage SQL engine encountered an internal error.
Solution: This error indicates an internal problem in the Advantage SQL engine. In general, this error should not be returned to the application directly. If the application does receive this error, the SQL statement handle should not be used any further. Close the existing SQL statement handle and create and use a new one. Please report the error with a small sample application demonstrating the issue to Advantage Technical Support at advantage@extendsys.com.
2121 Column not found
Problem: The SQL engine could not find a specified column. For example, a SELECT statement referred to a column that does not exist.
Solution: Verify that the columns referenced in the SQL statement all exist in the table. Also verify that you are using the correct alias on column names. For example, if you use an alias on a table name, you must use that alias with the column name rather than the table name. Example: "SELECT a.field FROM mytable a". The alias "a" is used on "mytable". "SELECT mytable.field FROM mytable a" is not correct.
2122 Table not found
Problem: The SQL engine could not find a table referenced in the SELECT statement. This error can occur with statements of the form "SELECT mytable.* ".
Solution: Verify that the table name or alias used in the SELECT list is correct.
2123 Data type of parameter cannot be determined
Problem: The SQL engine was not able to determine the data type of a parameter within the query. See Parameters in SQL Statements for more information.
Solutions:
2124 Invalid operand for operator
Problem: The SQL engine encountered the use of an operator that was not compatible with the operands. For example, the LIKE operator cannot be used with date fields. Also, you cannot compare mismatched data types. For example, " WHERE datefield = numericfield" is not valid.
Solution: Verify that the operators and operands match.
2125 Invalid table name
Problem 1: The SQL statement contains a table name that is too long.
Solution 1: Verify that the combined connect path and table name is 255 characters or less.
Problem 2: A drive letter is used in the table name and the Advantage Database Server is used to execute the SQL statement. For example, "SELECT * INTO "c:\temp\tmpTable" FROM MyTable" or "CREATE TABLE "c:\temp\tmpTable" ( fld1 integer )". Because the drive letter mappings of the Advantage Database Server is likely to be different from the client computer's drive letter mappings, a drive letter is not allowed when executing SQL statements that require creating a table.
Solution 2: Use a UNC path or relative path. For example, "SELECT * INTO "\MyAdsServer\temp\tmpTable" FROM MyTable".
2126 ORDER BY ordinal greater than select list count
Problem:** An ordinal value used in the ORDER BY clause is greater than the available number of columns. For example, the statement "SELECT field FROM mytable ORDER by 2" is not valid because the ordinal "2" is greater than the number of columns selected (1).
Solution:** Adjust the ordinal value.
2127 Too many ORDER BY items
Problem: The ORDER BY clause of the statement contains too many items.
Solution: Reduce the number of items in the ORDER BY clause. See SQL Statement Limits for the maximum number of items allowed in the ORDER BY clause.
2128 Cannot ORDER BY an expression
Problem: The ORDER BY clause contained a reference to an expression.
Solution: Change the ORDER BY clause to refer only to columns existing in tables.
2129 Unequal number of insert columns and insert values
Problem: The number of values in the INSERT statement does not match the number of columns. For example, "INSERT INTO mytable (field1, field2) VALUES (1, 2, 3)" is not valid.
Solution: Adjust the column list or the values list so that they match.
2130 Insert value was not valid
Problem: A value in the INSERT value list was not valid. For example, "INSERT INTO mytable (field) VALUES ( sum(field2) )" is not valid.
Solution: The values in the INSERT list cannot be aggregate functions or logical expressions such as "3 < 4".
2131 Value in an IN expression must be a simple value
Problem: The IN clause in the SQL statement contains an invalid value. For example, "SELECT * FROM mytable WHERE field IN (2*3)" is not valid.
Solution: The values in the IN clause must be simple constants, parameters, or the USER literal.
2132 Table alias used multiple times
Problem: The SQL statement used a non-unique table alias.
Solution: Verify that all aliases used on table names in the SQL statement are unique.
2133 Column on list multiple times
Problem: A column list in the SQL statement contained a column name multiple times. For example, "INSERT INTO mytable (field1, field1) VALUES (1, 2)" is not valid.
Solution: Verify that the column list names each field only once.
2134 Invalid column name
Problem: The column name in a CREATE TABLE statement is not valid.
Solution: Verify that the column name is not too long for the table type. DBF tables are limited to 10 characters. Visual FoxPro (VFP) tables that are in a data dictionary are limited to 128 characters. ADT tables in SQL usage are limited to 128 characters.
2135 No such type
Problem: A CREATE TABLE statement contained an invalid type.
Solution: Verify that the column types are valid.
2136 Invalid number of parameters for type
Problem: A CREATE TABLE statement contained the wrong number of parameters for a type. For example, "CREATE TABLE mytable (d date(5))" is not valid because the "date" type does not take any parameters.
Solution: Correct the column definitions.
2137 Column found in multiple tables
Problem: A column name in an SQL statement was found in multiple tables and, thus, was not uniquely identifiable.
Solution: Use a table alias or table name with the column name to uniquely identify it. For example, "SELECT field FROM table1, table2" is not valid if "field" exists in both "table1" and "table2" because it is ambiguous. You must specifically identify which table to use. For example, "SELECT table1.field from table1, table2".
2139 Invalid connection string
Problem: An invalid connection string is specified.
Solution: This error indicates an internal problem in the Advantage SQL Engine. In general, this error should not be returned to the application directly. If the application does receive this error, the SQL statement handle should not be used any further. Close the existing SQL statement handle and create and use a new one. Please report the error with a small sample application demonstrating the issue to Advantage Technical Support at advantage@extendsys.com.
2140 Unable to connect
Problem: Unable to connect to Advantage Database Server or Advantage Local Server
Solution: This error indicates an internal problem in the Advantage SQL engine. In general, this error should not be returned to the application directly. If the application does receive this error, the SQL statement handle should not be used any further. Close the existing SQL statement handle and create and use a new one. Please report the error with a small sample application demonstrating the issue to Advantage Technical Support at advantage@extendsys.com.
2141 Parameter missing
Problem: An SQL statement was executed before all parameters were provided.
Solution: Correct the SQL statement or make sure all parameter values are specified before executing the statement.
2142 - 2144 Internal Error
Problem: Internal error.
Solution: This error indicates an internal problem in the Advantage SQL Engine. In general, this error should not be returned to the application directly. If the application does receive this error, the SQL statement handle should not be used any further. Close the existing SQL statement handle and create and use a new one. Please report the error with a small sample application demonstrating the issue to Advantage Technical Support at advantage@extendsys.com.
2145 Unable to ORDER BY this column
This error is returned when the SQL engine is not able to supported the ORDER BY clause of the SQL statement. The following are some possible causes and solutions.
Problem: If the ORDER BY is used in a SELECT statement that defines a cursor in an SQL script, or that is a value subquery, then there are additional restriction on the columns in the ORDER BY clause. For example, the column cannot be from a system table.
Solution: Use table expression to create an intermediate result set and then SELECT ... ORDER BY... on the intermediate result.
Example:
// This script returns 2145 error
DECLARE Columns CURSOR AS
SELECT *
FROM System.Columns
WHERE field_default_value is not null
ORDER BY [Parent];
OPEN Columns;
// Rewrite to this to avoid error
DECLARE Columns CURSOR AS
SELECT * FROM
( SELECT *
FROM System.Columns
WHERE field_default_value is not null ) t
ORDER BY [Parent];
OPEN Columns;
Problem: The ORDER BY clause contained a column that cannot be ordered (e.g., memo, binary, or character field longer than 1024 characters).
Solution: Use only fixed-length fields that are 1024 characters or less in the ORDER BY clause.
2146 Unable to perform sort for ORDER BY or DISTINCT
Problem: An error occurred while attempting to sort data for the SQL statement. The most likely cause is a problem with the file system (unable to create a temporary file, unable to write to the disk, etc.).
Solution: If using the Advantage Database Server, the temporary files used for sorting are created in the directory specified in the connect path. With Advantage Local Server, the system temp directory (e.g., the directory specified by the TEMP environment variable) is used for temporary files. Verify that the disk contains enough space for the sorting.
2147 GROUP BY contains too many columns
Problem: The GROUP BY clause of the statement contains too many items.
Solution: Reduce the number of items in the GROUP BY clause.
2148 Unable to GROUP BY this column
Problem: The GROUP BY clause contained a column that cannot be ordered (e.g., memo, binary, or character field longer than 1024 characters).
Solution: Use only fixed-length fields that are 1024 characters or less in the GROUP BY clause.
2149 Aggregate function not allowed in this context
Problem: An SQL statement used an aggregate function (SUM, AVG, MIN, MAX, COUNT) in an incorrect manner.
Solution: Do not use aggregate functions in column lists. For example, "INSERT INTO mytable (field) VALUES (MIN(field))" is not valid.
2150 SELECT * not allowed if there is a GROUP BY
Problem: An SQL statement used the * token in a SELECT list when a GROUP BY clause was specified.
Solution: You must specifically name the columns in the SELECT list when a GROUP BY clause is used. The wildcard * token is not allowed in that context.
2151 Unable to do sort for GROUP BY
Problem: An error occurred while attempting to sort data for the SQL statement. The most likely cause is a problem with the file system (unable to create a temporary file, unable to write to the disk, etc.).
Solution: If using the Advantage Database Server, the temporary files used for sorting are created in the directory specified in the connect path. With Advantage Local Server, the system temp directory (e.g., the directory specified by the TEMP environment variable) is used for temporary files. Verify that the disk contains enough space for the sorting.
2152 HAVING specified without a GROUP BY
Problem: The HAVING clause was used without a GROUP BY clause.
Solution: You must use a GROUP BY clause in order to use the HAVING clause.
2153 Division by zero
Problem: The SQL engine detected a division by zero error while executing the statement.
Solution: Modify the SQL statement so that it does not produce division by zero errors. If, for example, your statement is "SELECT 1.0 / value FROM mytable", you might want to add a WHERE clause of the form "WHERE value <> 0" to avoid division by zero.
2154 The data type of a parameter cannot be determined
Problem: A parameter by itself in the SELECT list or ORDER BY clause has an ambiguous data type. For example, given the statements "SELECT ? FROM mytable" or "SELECT * FROM myTable ORDER BY ?", the data type of the parameter in either statement cannot be determined.
Solution: Remove the ambiguous parameter from the statement. The use of the parameter in the SELECT list or ORDER BY clause is generally not meaningful.
2155 String concatenation overflow
Problem: The result of a string concatenation (with the + operator or the CONCAT scalar function) exceeded a computed expression length. In versions 7.0 and earlier, the result of these concatenations was limited to 1024 characters. This limitation has been removed. In 7.0 and later release, this error can occur if the SQL statement contains the expression ? + strExpr where ? is a parameter and its value is set to a string longer than 1024 characters.
Solution: Adjust the SQL statement to avoid exceeding the maximum literal length, or use a cast function to explicitly specify the precision of the character string parameter. For example: Cast( ? AS SQL_VARCHAR( 2048)).
2156 Invalid index name
Problem: An index name used in a CREATE INDEX or DROP INDEX statement was too long.
Solution: Verify that the index name is 63 characters or less.
2157 Too many key columns for index
Problem: A CREATE INDEX statement contained too many columns.
Solution: The maximum number of columns allowed in an SQL CREATE INDEX statement is 15. Content indexes (for full text searches) can only have one column.
2158 Scalar function not found
Problem: A scalar function used in an SQL statement does not exist.
Solution: Verify that the scalar function name is spelled correctly.
2159 Invalid argument to scalar function
Problem: An SQL statement specified an incorrect argument for a scalar function.
Solution: Refer to the Advantage SQL documentation for information about the scalar function you are trying to use.
Problem: A parameter is used in an SQL scalar function that supports multiple data types. For example, the IIF() and ISNULL() may take different data types as input and return as output different types of values. If the query engine was not able to determine the type of the parameter, the 2159 error will be returned. A sample statement that may return 2159 error is "SELECT IIF( id = 1, :param1, :param2 ) FROM table1". The query engine is not able to determine the type of the resulting column until the execution time and will return 2159 error instead.
Solution: Use explicit CAST() or CONVERT() on the parameter(s) so there is no ambiguity to the data type. The above sample statement may be changed to the following to avoid the error: "SELECT IIF( id = 1, CAST( :param1 AS SQL_CHAR(20)), :param2 AS SQL_CHAR(20))) FROM table1".
2160 - 2165 Internal Error
Problem: Internal error.
Solution: This error indicates an internal problem in the Advantage SQL engine. In general, this error should not be returned to the application directly. If the application does receive this error, the SQL statement handle should not be used any further. Close the existing SQL statement handle and create and use a new one. Please report the error with a small sample application demonstrating the issue to Advantage Technical Support at advantage@extendsys.com.
2166 SELECT sub-query returns more than one column
Problem: A sub-query incorrectly specified more than one column in the SELECT list. For example, "SELECT field1, field2 FROM table1 WHERE field1 IN (SELECT * FROM table2)" is not valid if table2 contains more than one field.
Solution: Change the sub-query so that it selects only a single column.
2167 SELECT sub-query returned more than one row
Problem: A sub-query returned more than one row in a context where a single row was expected. For example, "SELECT * FROM table1 WHERE table1.field1 = (SELECT table2.field1 FROM table2)" is not valid if table2 contains more than one row.
Solution: Adjust the WHERE clause on the sub-query so that it returns only a single row.
2168 - 2188 Internal Error
Problem: Internal error.
Solution: This error indicates an internal problem in the Advantage SQL engine. In general, this error should not be returned to the application directly. If the application does receive this error, the SQL statement handle should not be used any further. Close the existing SQL statement handle and create and use a new one. Please report the error with a small sample application demonstrating the issue to Advantage Technical Support at advantage@extendsys.com.
2189 Inconsistent number of columns in the result sets participating in the UNION statement
Problem: The individual queries in the UNION statement do not return the same number of columns in the result set. The queries in the UNION statement must contain the same number of columns in the result set. For example, "SELECT Lastname FROM table1 UNION SELECT lastname, firstname FROM table2" is not valid because the second SELECT returns 2 columns in the result set while the first SELECT returns only 1 column in the result set.
Solution: Adjust the SELECT clauses in the UNION statement so they all return the same number of columns.
2190 Incompatible column data types in the result sets participating in the UNION statement
Problem: Each resulting column of the individual queries in the UNION statement must be the same data type for all queries. For example, "SELECT lastname FROM table1 UNION SELECT DOB FROM table2" is not valid because the second SELECT returns a column with data type of date while the first SELECT returns only a column of characters.
Solution: Adjust the SELECT clauses in the UNION statement so they all return the same data type at each column position.
2191 Invalid Data Type
Problem: The data type of an expression in the SQL statement is not valid in the specified context.
Solution: Use Cast() or Convert() function to force the expression into the required type.
Problem: Incompatible data types are given to the UNION statements, IFNULL, IIF, or CASE expressions. For example: "SELECT 1 FROM system.iota UNION SELECT 'abc' FROM system.iota" will resulted in 2191 error because the integer value in the first SELECT is not compatible with the string value in the second SELECT for the purpose of the UNION. Similarly, "SELECT IIF( bVal, 1, 'abc' ) FROM system.iota" will also return 2191 error.
Solution: Use Cast() or Convert() function to force the expressions into the compatible type.
Note: In version 8.0 and earlier releases, the error 2191 means "UNION statement can only be ordered by ordinal number". The problem and solution for this error are below.
Problem: The combined result set of the UNION statement can be ORDER BY ordinal numbers only. Individual queries in the UNION statement cannot be sorted with the ORDER BY clause. For example, "SELECT lastname, firstname FROM table1 UNION SELECT lastname, firstname FROM table2 ORDER BY lastname" is not valid because the ORDER BY is not specified with ordinal number.
Solution: Adjust the ORDER BY clause in the UNION statement to use ordinal number columns.
2192 GROUP BY and HAVING are not supported in UNION statement
Problem: GROUP BY and HAVING clauses are not valid in a UNION statement. For example, "SELECT lastname, firstname FROM table1 GROUP BY lastname UNION SELECT lastname, firstname FROM table2 ORDER BY lastname" is not valid because of the GROUP BY clause in the statement. This limitation exists in the version 6.0 and earlier releases of the Advantage products.
Solution: Remove the GROUP BY or HAVING clause from the UNION statement or upgrade to the Advantage Database Server/Advantage Local Server 6.1 (or greater) release.
2194 Unable to perform DISTINCT operation on some of the columns
Problem: The DISTINCT operation cannot be performed on some columns in the result set. DISTINCT operation will fail if the query contains any of the following column types in the result set: character or binary columns that are larger than 1 KB, or MEMO, BLOB, or VARCHAR columns.
Solution: Adjust the SELECT list so it does not include any offending column.
2195 Column in the ORDER BY clause is not in the result set
Problem: In certain cases, the ORDER BY clause cannot contain columns that are not in the result set. Two such cases are: 1) a UNION statement and 2) an ORDER BY clause containing any column that is an expression. For example, "SELECT lastname FROM table1 UNION SELECT lastname FROM table2 ORDER BY 1, firstname" is not valid because the column firstname is not in the result set. "SELECT UCASE( lastname ) FROM table1 ORDER BY 1, firstname" is not valid because the ordinal number 1 in the ORDER BY clause specifies an expression column and firstname column is not in the result set.
Solution: Adjust the ORDER BY clause so it conforms to the restriction.
2196 Column not found in GROUP BY clause
Problem: The SQL engine expected to find the specified column in the GROUP BY clause.
Solution: If you use an aggregate function and a non-aggregated column name in a SELECT statement, then you must use a GROUP BY clause. For example, an application will get this error if it executes this statement: "SELECT lastname, COUNT(lastname) FROM employee".
The statement must also include the clause "GROUP BY lastname". In addition, if a GROUP BY clause is used, then every column that is not in an aggregate function in the SELECT list must be included in the GROUP BY clause.
2197 Illegal VIEW operation
The operation attempted is not legal on VIEWs
2198 Invalid PROCEDURE name
The stored procedure name is invalid.
2200 Error removing temporary input file
The temporary file used as input to the stored procedures could not be deleted. Make sure the file is closed by the stored procedure DLL. To prevent problems, do not open the input or output tables with a connection that is in a transaction.
2201 Invalid Ordinal
The SQL statement used an ordinal in the GROUP BY or ORDER BY clause but the ordinal specified does not correspond to the number of values in the SELECT clause. For example: SELECT lastname FROM Employees ORDER BY 2 -- The ordinal 2 is not valid because there is only one value in the SELECT clause.
2202 Invalid use of constant value
Constant values, including numeric values and string literal, are not allowed in the ORDER BY or GROUP BY clause of SELECT statement, or in the CREATE INDEX statement. For example, SELECT lastname FROM Employees ORDER BY 'lastname' is not allowed because 'lastname' is a string literal. The correct statement may be SELECT lastname FROM Employees ORDER BY "lastname" or SELECT lastname FROM Employees ORDER BY lastname.
2203 Error encountered while reading an intermediate file
This is an unexpected internal error. The Advantage SQL engine has encountered an error while processing an intermediate or temporary result file. If this error is returned, please contact Advantage Technical Support with a detailed description of the problem and the SQL statement that generates the error.
2204 The key specified as the primary key is too large
The columns specified as the primary key of the table is too large. Reduce the number of columns specified as the primary key or reduce the length of the columns that are part of the primary key. See Advantage Proprietary File Format Specifications for information about maximum index key size limitations.
2205 A column specified in the index is of an invalid type
The variable length column type cannot be used as part of an index or primary key.
2206 Update statement is ambiguous
The table to be updated is listed more than once in the FROM clause of an UPDATE statement. A sample ambiguous update statement is:
UPDATE table1 SET col1 = 2 FROM table1 a INNER JOIN table1 b WHERE a.id = b.id2
It is not possible for the SQL engine to determine whether the instance "a" of table1 or the instance "b" of table1 should be updated.
2207 Error creating input or output table temp filename
An error was encountered creating the temporary filename for use with an input/output parameter table. Additional error details were logged to the ads_err.adt or ads_err.dbf file. Verify the temporary file path in the data dictionary is valid. If problems persist, contact Advantage Technical Support with error log information.
2208 A stored procedure's input value was truncated
An input value was larger than one of the input tables parameters. Check the stored procedure's input parameter definition and use a value of the proper size.
2209 Table not unique
Problem: A reference to all columns in a table was not unique.
Solution: If multiple tables exist in the SQL statement, table aliases must be used to qualify the reference. For example, the following statement is not valid.
SELECT * FROM tbl1, tbl2 WHERE tbl1.id=tbl2.id AND CONTAINS( *, 'some words' )
It contains the * (all columns reference) in the CONTAINS scalar function, but it does not uniquely identify a table. It would need to be changed to something like the following:
SELECT * FROM tbl1, tbl2 WHERE tbl1.id=tbl2.id AND CONTAINS( tbl1.*, 'some words' )
2210 Invalid index option
Problem: An option in a CREATE INDEX statement was not valid.
Solution: Verify that the options specified in the CREATE INDEX statement are valid. If you specified options for creating a full text search index, verify that the keyword CONTENT is provided. If the CONTENT keyword is provided, verify that the UNIQUE and DESC keywords are not specified.
2211 CONTAINS(*) not allowed in GROUP BY statement
Problem: A SELECT statement with a GROUP BY clause contained the scalar function CONTAINS with the "all columns" wild card (*) as the first parameter.
Solution: When appearing in a statement with a GROUP BY clause, the CONTAINS scalar function can only be of the form where the first parameter is an explicit field name. It cannot use the * (all columns) operator.
2212 Aggregate cannot be nested
Problem: The expression for an aggregate function (MIN, MAX, SUM, AVG or COUNT) contains another aggregate function. For example: SUM( MIN( id )).
Solution: The nested aggregate is not a valid SQL expression.
2213 Invalid Collation
Problem: Invalid comparison or operation on strings with different collations.
Solution: Use the COLLATE clause to coerce on side to a valid collation.
2214 Invalid Coercion
Problem: Explicit collation is only legal on a string value.
Solution: Dont use the COLLATE clause on a non-string value.
2215 Invalid options in CREATE TABLE statement
An invalid option is specified in the CREATE statement. The error text should contain more information about the specific error. For example: the following CREATE TABLE statement returns the error because it specified both options to create a temporary table and a database table.
CREATE TABLE #Table1 ( id integer, name char(20) ) IN DATABASE
The # prefix before the table name specifies that the table is a temporary table while the IN DATABASE clause specifies that the table should be added into the data dictionary. An error is returned because the two options are mutually exclusive.
The error may also be returned when invalid options are specified in a CREATE INDEX, a CREATE FUNCTION, a CREATE PACKAGE statement or other CREATE statement.
2216 Unexpected end of the SQL script
Problem:** The SQL script cannot be parsed correctly because the expected block ending token was not found.
Solution:** Make sure the script is written correctly. Verify that all control statements, such as IF, WHILE, and TRY statements, have corresponding END [xxx] tokens to denote the end of the control statement.
2217 A declaration statement in the SQL script is not valid
Problem:** There was an error while parsing a DECLARE statement in the SQL script.
Solution:** Check the error message for an indication of the cause of the error and the location of it in the script.
2218 Variable not found
Problem:** An SQL script statement is referencing a variable that has not been declared.
Solution:** Check the error message for the location of the error in the script. Make sure the variable is spelled correctly or declare the variable at the beginning of the SQL script.
2219 General SQL script error
Problem:** An error occurred while executing an SQL script statement.
Solution:** Check the error message for the cause of the error and its location in the script.
2220 Cursor not open
Problem:** An SQL script statement is attempting to access information from a cursor that has not been opened.
Solution:** Check the error message for the location of the error in the script. Make sure the cursor has been opened before it is used.
2221 Cursor already open
Problem:** An SQL script statement is attempting to open a cursor that is already opened.
Solution:** Check the error message for the location of the error in the script. Make sure the OPEN statement at that location is not attempting to open a cursor that has been opened by a statement executed prior to this statement.
2222 Invalid cursor column reference
Problem:** A cursor column reference in an expression is not valid. The column name may not be valid or the data type of the column may not be valid for the expression.
Solution:** Check the error message for the location of the error in the script. Check that the column is in the cursor. If the cursor is opened and closed with a different cursor definition, the column name must correspond to a column in the cursor that is currently open.
2223 Cursor is not on a valid row
Problem:** Data from the column could not be read because the cursor is not on a valid row. The row pointer is either before the first row or after the last row in the cursor.
Solution:** Check the error message for the location of the error in the script. Verify that FETCH is called on the cursor before reading the column data and that the result from the FETCH statement is checked before trying to access the data.
2224 An exception was raised in the SQL script
Problem:** An exception was raised in the SQL script and it is not handled by any TRY CATCH statement in the script.
Solution:** This may not be an unexpected error. This error is returned as a result of executing a RAISE statement in the SQL script.
2225 Assignment error in the SQL script
Problem:** An error occurred while executing an assignment statement in the SQL script.
Solution:** Check the error message for the cause of the error and the location of the error in the script. A possible cause of this error is assigning values to read only variables such as __errclass, __errcode and __errtext.
2226 Invalid usage of a subquery
Problem: A subquery was used in an invalid position in an SQL statement.
Solution: Use a temporary table to store the result of the subquery, then use the temporary table in place of the subquery in the original SQL statement or redesign the SQL statement to comply to the allowed usage of subqueries.
2227 Assignment out of range in the SQL script
Problem:** The value assigned to a variable is out of the range supported by the type of the variable.
Solution:** Check the error message for the location of the error in the script and ensure that that the value assigned to the variable is within the support range for the variable. Variables declared in an SQL script have the same range as the corresponding field type. See ADT Field Types and Specifications or DBF Field Types and Specifications for field type specification details.
2228 Invalid view definition
Problem:** The view definition is not valid.
Solution:** Check the error message for the name of the problematic view and ensure that the statement used to define the view is a valid SQL SELECT statement. The view definition may be obtained by querying the system.views virtual table.
2229 Ambiguous reference in the SQL script
Problem:** An identifier used in an SQL statement is ambiguous. It may reference either a table column or a script variable.
Solution:** Check the error message for the location of the error in the script and determine the intended purpose for the identifier. Depending on the intended purpose of the identifier, the conflict may be resolved by either changing the name of the variable (see DECLARE or making a explicit column reference using the TableAlias.ColumnName notation.
2230 Bad Function Definition
Problem:** The specification of a User Defined Function is not valid. Possible causes of this error are: syntax error in the function specification, unsupported data type in the function parameter, or a bad function name.
Solution:** Correct the function specification.
2231 Numeric Result Out Of Range
Problem: The result of an exact numeric expression exceeds the range of resultant data type. An example is multiplication of two exact numeric values with large-scale (digits after the decimal point) values. This error may be returned during query preparation or query evaluation. Another example will be when the result of an expression with integer operands exceed the range of a signed 32 bit integer value.
Solution: Cast one of the operands to the approximate numeric (i.e., Double) or to an exact numeric with larger range.
See Also
Exact Numeric vs Approximate Numeric
2232 Numeric Overflow
Problem: The evaluation of an exact numeric expression exceeds the predetermined precision. This is a run time error. In addition, subtraction and multiplication operations, the scale of the exact numeric expression is determined following the SQL standards guideline and the precision is capped at the maximum supported value (30). If the result of evaluating exceed the supported precision, this error is returned.
Solution: Cast one of the operands to the approximate numeric (i.e., Double).
See Also
Exact Numeric vs Approximate Numeric
2233 Invalid DEBUG command
There was an error executing the DEBUG statement. Check the error text for additional information.
2234 Invalid Use of Parameter
Parameter is not allowed in the definition of database object such as views, stored procedures or user defined functions.
error_2235_select_from_aep_with_no_output
Problem: A SELECT was run using a stored procedure in the table list but the stored procedure is defined as having no output parameter or having varying parameter.
Solution: Add output parameters to the stored procedure or run the SELECT using a procedure with explicit output parameters.
2236 Conversion Error
Problem: The SQL engine was unable to perform the conversion from one data type to another. This error can occur when using parameterized queries when the data assigned to a parameter does not match the data in the table. It can also occur when using the scalar function CONVERT or CAST incorrectly.
Solution: Verify that the statement is using compatible data types.
error_2239_Invalid_Procedure_Arguments
Problem: The arguments for a stored procedure were the incorrect type.
Solution: Change the arguments to be the expected type. Use the CAST or CONVERT SQL scalar functions to change the argument types if necessary.