|
[QODBC-ALL] & [QXL- ALL] SQL Language Support (Syntax)
Posted by Rajendra Dewani (QODBC Support) on 30 November 2023 12:14 PM
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
[QODBC-ALL] & [QXL- ALL] SQL Language Support (Syntax)Stored Procedures Command ListApplicable to [QODBC-Desktop], [QODBC-Online], [QODBC-POS], [QXL- Desktop] & [QXL- Online]
Stored ProceduresSP_COLUMNS table name Instruction: Returns a recordset of the columns in the specified table. Example: sp_columns Customer Returns:
SP_FQSAVETOCACHEROLLBACK on|off Instruction: Sets or clears whether the cache is backed up or reset if an error occurs when doing multiple inserts using the FQSaveToCache flag field. The off mode can help with performance if the rollback is not needed. The default is on. Example: sp_fqsavetocacherollback off Returns: This is an execute the command and does not return a recordset.
SP_LASTINSERTID Instruction: Returns a recordset with one row (or multiple rows if done after a sp_batchupdate command) and two columns containing the last ListID or last TxnID from the last insert done and its error status on the current connection. The value is obtained from the return value of the previous insert (or inserts if done inside a sp_batchstart/sp_batchupdate block) performed on the same connection for that table. Example: sp_lastinsertid Customer Returns:
SP_LASTINSERTIDRETURN on|off Instruction: Sets or clears whether the last insert ID is returned. Example: sp_lastinsertidreturn off Returns: This is an execute the command and does not return a recordset.
SP_OPTIMIZEFULLSYNC tablename|ALL Instruction: This command will reload the specified table from scratch. It helps ensure that the optimizer perfectly syncs with the QuickBooks company file. The ALL option will do all tables. Example: sp_optimizefullsync Customer Returns: This is an execute the command and does not return a recordset.
SP_OPTIMIZEUPDATESYNC tablename|ALL Instruction: This command will synchronize the specified table with the QuickBooks data file using changed and deleted data. It helps ensure that the optimizer is up to date with the QuickBooks company file. The ALL option will do all tables. Example: sp_optimizeupdatesync Customer Returns: This is an execute the command and does not return a recordset.
SP_PRIMARYKEYStablename Instruction: Returns a recordset of the primary key segments in the specified table. Example: sp_primarykeys Customer Returns:
SP_QBFILENAME Instruction: This command returns the full path to the open QuickBooks company file. It returns one column, one-row record set. Example: sp_qbfilename Returns:
*Note: sp_report and other reports commands are unavailable for QODBC POS. QuickBooks POS SDK does not support reports, and thus it is not available in QODBC POS. SP_REPORTtablename [show] Instruction: Similar to the SQL Keyword SELECT. Used to run built-in QuickBooks reports. See http://www.qodbc.com/data, make your country selection, then select the REPORTS link. Example: sp_report ARAgingSummary show Current_Title, Amount_Title, Text, Label, Current, Amount parameters DateMacro = ‘Today', AgingAsOf = ‘Today' Returns:
SP_REPORTS Instruction: Returns a recordset of the information of all reports. Example: sp_reports Returns:
SP_SPECIALCOLUMNS tablename [ ROWVER ]|[ BEST_ROWID ] Instruction: Returns a recordset of the special columns. RowVer describes which column holds the row ID, and Best_RowID describes the column that is the row identifier. Best_RowID is the default if neither is specified. Example: sp_specialcolumns Customer Best_RowID Returns:
Example2: sp_specialcolumns Customer RowVer Returns:
SP_TABLES table name Instruction: Returns a recordset with the list of tables available from the ODBC Driver. Example: sp_tables Returns:
SP_CATEGORIES Instruction: Returns a recordset with the list of the categories for all the tables. Example: sp_categories Returns:
SP_CATEGORYTABLES Instruction: Returns a recordset with the list of all the tables and the categories they belong to. Example: sp_categorytables Returns:
SP_CATEGORYREPORTS Instruction: Returns a recordset with the list of all the reports and the categories they belong to. Example: sp_categoryreports Returns:
SP_FOREIGNKEYS table name table name Instruction: Returns a recordset of the detailed relationship information of two tables. Example: sp_foreignkeys Customer Invoice Returns:
SP_PARAMETERS Instruction: Returns a recordset with the list of the detailed information of parameters of each report. Example: sp_parameters Returns:
SP_STATISTICS tablename Instruction: Returns a recordset with the list of all the indexes and a list of statistical information of the specified table or indexed view. Example: sp_statistics Customer Returns:
Keywords: last insert id Function ListApplicable to [QODBC-Desktop], [QODBC-Online], [QODBC-POS], [QXL- Desktop] & [QXL- Online]
FunctionsThis is a list of all of the SQL functions supported by the QODBC Driver and their associated syntax. QODBC String FunctionsASCII ( string_exp ) Instruction: Returns the ASCII code value of the leftmost character of string_exp as an integer. Example: SELECT {fn ASCII("Name")} AS "ASCII", "Name" FROM Customer Returns:
CHAR ( code ) Instruction: Returns the character with the ASCII code value specified by the code. The code value should be between 0 and 255; otherwise, the return value is data source-dependent. Example: SELECT {fn CHAR(65)} + {fn CHAR(66)} AS "APlusB", "Name" FROM Customer Returns:
CONCAT ( string_exp1, string_exp2 ) Instruction: Returns a character string that results from concatenating string_exp2 to string_exp1. A NULL value will be returned if the column represented by string_exp1 or string_exp2 contains a NULL value. Example: SELECT {fn CONCAT("BillAddressState", "BillAddressPostalCode")} AS "STZip", "Name" FROM Customer Returns:
DIFFERENCE ( string_exp1, string_exp2 ) Instruction: Returns an integer value that indicates the difference between the values returned by the SOUNDEX function for string_exp1 and string_exp2. Example: SELECT {fn DIFFERENCE("Name", 'Abercrombie, Kristy')} AS "Difference", "Name" FROM Customer Returns:
INSERT ( string_exp1, start, length, string_exp2 ) Instruction: Returns a character string where length characters have been deleted from the string_exp1 beginning at the start and where string_exp2 has been inserted into string_exp1, beginning at the start. Example: SELECT {fn INSERT("Name", 3, 2, '*Inserted*')} AS "Inserted", "Name" FROM Customer Returns:
LCASE ( string_exp ) Instruction: Converts all uppercase characters in string_exp to lowercase. Example: SELECT {fn LCASE("Name")} AS "LCase", "Name" FROM Customer Returns:
UCASE ( string_exp ) Instruction: Converts all lowercase characters in string_exp to uppercase. Example: SELECT {fn UCASE("Name")} AS "UCase", "Name" FROM Customer Returns:
LEFT ( string_exp, count ) Instruction: Returns the leftmost count of characters of string_exp. Example: SELECT {fn LEFT("Name", 5)} AS "Left5", "Name" FROM Customer Returns:
RIGHT ( string_exp, count ) Instruction: Returns the rightmost count of characters of string_exp. Example: SELECT {fn RIGHT(“Name”, 5)} AS "Right5", "Name" FROM Customer Returns:
LENGTH ( string_exp ) Instruction: Returns the number of characters in string_exp, excluding trailing blanks and the string termination character. Example: SELECT {fn LENGTH("Name")} AS "Length", "Name" FROM Customer Returns:
BIT_LENGTH ( string_exp ) Instruction: Returns the bit length of string_exp, excluding trailing blanks and the string termination character. Example: SELECT {fn BIT_LENGTH("Name")} AS "BitLength", "Name" FROM Customer Returns:
CHAR_LENGTH ( string_exp ) Instruction: Returns the number of chars in string_exp, excluding trailing blanks and the string termination character. Example: SELECT {fn CHAR_LENGTH("Name")} AS "CharLength", "Name" FROM Customer Returns:
CHARACTER_LENGTH ( string_exp ) Instruction: Returns the number of characters in string_exp, excluding trailing blanks and the string termination character. (Almost the same as the LENGTH function) Example: SELECT {fn CHARACTER_LENGTH("Name")} AS "CharacterLength", "Name" FROM Customer Returns:
OCTET_LENGTH ( string_exp ) Instruction: Returns the octet length of string_exp, excluding trailing blanks and the string termination character. (Almost the same as the LENGTH function) Example: SELECT {fn OCTET_LENGTH("Name")} AS "OctetLength", "Name" FROM Customer Returns:
LOCATE ( string_exp1, string_exp2[, start] ) Instruction: Returns the starting position of the first occurrence of string_exp1 within string_exp2. The search for the first occurrence of string_exp1 begins with the first position in string_exp2 unless the optional argument is specified. If start is set, the search begins with the character position indicated by the valuebirth. The first character position in string_exp2 is indicated by the value 1. If string_exp1 is not found within string_exp2, the value 0 is returned. Example: SELECT {fn LOCATE('a', "Name", 2)} AS "LocationOfA", "Name" FROM Customer Returns:
LTRIM ( string_exp ) Instruction: Returns the characters of string_exp, with leading blanks removed. Example: SELECT {fn LTRIM("Name")} AS "LTrim", "Name" FROM Customer Returns:
RTRIM ( string_exp ) Instruction: Returns the characters of string_exp, with trailing blanks removed. Example: SELECT {fn RTRIM("Name")} AS "RTrim", "Name" FROM Customer Returns:
REPEAT ( string_exp1, repeat times ) Instruction: Returns the characters of string_exp1, repeating it repeat times. Example: SELECT {fn REPEAT("Name",2)} as "Repeat2","Name" FROM Customer Returns:
POSITION ( string_exp1, string_exp2 ) Instruction: Returns an integer value that shows the position where string_exp1 first begins in string_exp2 (including spaces) Example: SELECT {fn POSITION('a' IN "Name")} As "PositionOfA", "Name" FROM Customer Returns:
REPLACE ( string_exp1, string_exp2,string_exp3 ) Instruction: Returns the characters of string_exp3, which takes the place of string_exp2 value in column string_exp1. Example: SELECT {fn REPLACE("NAME",'330 Main St','abc')} AS "Replace","Name" FROM Customer Returns:
SOUNDEX ( string_exp ) Instruction: Returns a character string representing the sound of the words in string_exp . Example: SELECT {fn SOUNDEX("Name")} AS "Soundex", "Name" FROM Customer Returns:
SPACE ( count ) Instruction: Returns a character string consisting of count spaces. Example: SELECT '[' + {fn SPACE(10)} + ']' AS "TenSpaces", "Name" FROM Customer Returns:
SUBSTRING ( string_exp, start, length ) Instruction: Returns a character string that is derived from string_exp beginning at the character position specified by start for length characters. Example: SELECT {fn SUBSTRING("Name", 2, 5)} AS "Middle5Characters", "Name" FROM Customer Returns:
QODBC Numeric FunctionsABS ( numeric_exp|float_exp|integer_exp ) Instruction: Returns the absolute value of numeric_exp . Example: SELECT "Name", {fn ABS(Balance)} AS "ABSBalance", "Balance" FROM Customer Returns:
ACOS ( float_exp ) Instruction: Returns the arccosine of float_exp as an angle, expressed in radians. Example: SELECT {fn ACOS({fn CONVERT(0, SQL_FLOAT)})} AS "ACOSValue", CompanyName AS "CompanyName" FROM Company Returns:
ASIN ( float_exp ) Instruction: Returns the arcsine of float_exp as an angle, expressed in radians. Example: SELECT {fn ASIN({fn CONVERT(0, SQL_FLOAT)})} AS "ASINValue", CompanyName AS "CompanyName" FROM Company Returns:
ATAN ( float_exp ) Instruction: Returns the arctangent of float_exp as an angle, expressed in radians. Example: SELECT {fn ATAN({fn CONVERT(1, SQL_FLOAT)})} AS "ATANValue", CompanyName AS “CompanyName” FROM Company Returns:
ATAN2 ( float_exp1, float_exp2 ) Instruction: Returns the arctangent of the x and y coordinates specified by float_exp1 and float_exp2 , respectively, as an angle, expressed in radians. Example: SELECT {fn ATAN2({fn CONVERT(1, SQL_FLOAT)}, {fn CONVERT(2, SQL_FLOAT)})} AS "ATAN2Value", CompanyName AS “CompanyName” FROM Company Returns:
COS ( float_exp ) Instruction: Returns the cosine of float_exp, where float_exp is an angle expressed in radians. Example: SELECT {fn COS({fn CONVERT(1, SQL_FLOAT)})} AS "COSValue", CompanyName AS “CompanyName” FROM Company Returns:
COT ( float_exp ) Instruction: Returns the cotangent of float_exp, where float_exp is an angle expressed in radians. Example: SELECT {fn COT({fn CONVERT(1, SQL_FLOAT)})} AS "COTValue", CompanyName AS “CompanyName” FROM Company Returns:
CEILING ( numeric_exp|float_exp|integer_exp ) Instruction: Returns the smallest integer greater than or equal to numeric_exp . Example: SELECT "Name", {fn CEILING("Balance")} AS "CeilingBalance", "Balance" FROM Customer Returns:
DEGREES ( numeric_exp|float_exp|integer_exp ) Instruction: Returns the number of degrees converted from numeric_exp radians. Example: SELECT {fn DEGREES(1)} AS "DegreesReturned", CompanyName AS “CompanyName” FROM Company Returns:
EXP ( float_exp ) Instruction: Returns the exponential value of float_exp. Example: SELECT {fn EXP({fn CONVERT(1, SQL_FLOAT)})} AS "ExpReturned", CompanyName AS “CompanyName” FROM Company Returns:
FLOOR (numeric_exp|float_exp|integer_exp) Instruction: Returns largest integer less than or equal to numeric_exp. Example: SELECT "Name", {fn FLOOR("Balance")} AS "FloorBalance", "Balance" FROM Customer Returns:
LOG ( float_exp ) Instruction: Returns the natural logarithm of float_exp. Example: SELECT {fn LOG({fn CONVERT(25, SQL_FLOAT)})} AS "LogReturned", CompanyName AS “CompanyName” FROM Company Returns:
LOG10 ( float_exp ) Instruction: Returns the base ten logarithm of float_exp. Example: SELECT {fn LOG10({fn CONVERT(25, SQL_FLOAT)})} AS "Log10Returned", CompanyName AS “CompanyName” FROM Company Returns:
MOD ( integer_exp1, integer_exp2 ) Instruction: Returns the remainder (modulus) of integer_exp1 divided by integer_exp2 . Example: SELECT {fn MOD(27, 7)} AS "Mod7Returned", CompanyName AS “CompanyName” FROM Company Returns:
PI () Instruction: Returns the constant value of pi as a floating point value. Example: SELECT {fn PI()} AS "PI", CompanyName AS “CompanyName” FROM COMPANY Returns:
POWER ( numeric_exp|float_exp|integer_exp, integer_exp ) Instruction: Returns the value of numeric_exp to the power of integer_exp . Example: SELECT {fn POWER(4, 3)} AS "PowerValue", CompanyName AS "CompanyName" FROM COMPANY Returns:
RADIANS ( numeric_exp|float_exp|integer_exp ) Instruction: Returns the number of radians converted from numeric_exp degrees. Example: SELECT {fn RADIANS(57.29578)} AS "RadiansValue", CompanyName AS “CompanyName” FROM COMPANY Returns:
RAND ([ integer_exp|float_exp|numeric_exp ]) Instruction: Returns a random floating point value using integer_exp as an optional seed value. Example: SELECT {fn RAND()} AS "RandValue" FROM COMPANY Returns:
ROUND ( numeric_exp|float_exp|integer_exp, integer_exp ) Instruction: Returns numeric_exp rounded to integer_exp placed right of the decimal point. If integer_exp is negative, numeric_exp is rounded to | integer_exp | and placed to the left of the decimal point. Example: SELECT "Name", {fn ROUND(Balance, 1)} AS "RoundBalance", "Balance" FROM Customer Returns:
SIGN ( numeric_exp|float_exp|integer_exp ) Instruction: Returns an indicator or the sign of numeric_exp . If numeric_exp is less than zero, -1 is returned. If numeric_exp equals zero, 0 is returned. If numeric_exp is more significant than zero, one is returned. Example: SELECT "Name", {fn SIGN(Balance)} AS "SignOfBalance", "Balance" FROM Customer Returns:
SIN ( float_exp ) Instruction: Returns the sine of float_exp, where float_exp is an angle expressed in radians. Example: SELECT {fn SIN({fn CONVERT(1, SQL_FLOAT)})} AS "SINValue", CompanyName AS “CompanyName” FROM Company Returns:
SQRT ( float_exp ) Instruction: Returns the square root of float_exp. Example: SELECT {fn SQRT({fn CONVERT(47, SQL_FLOAT)})} AS "SQRTValue", CompanyName AS “CompanyName” FROM Company Returns:
TAN ( float_exp ) Instruction: Returns the tangent of float_exp, where float_exp is an angle expressed in radians. Example: SELECT {fn TAN({fn CONVERT(1, SQL_FLOAT)})} AS "TANValue", CompanyName AS “CompanyName” FROM Company Returns:
TRUNCATE ( numeric_exp|float_exp|numeric_exp, integer_exp ) Instruction: Returns numeric_exp truncated to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is truncated to | integer_exp | places to the left of the decimal point. Example: SELECT "Name", {fn TRUNCATE(Balance, 1)} AS "TruncateBalance", "Balance" FROM Customer Returns:
QODBC Time and Date FunctionsCURDATE () Instruction: Returns the current date as a date value. Example: SELECT {fn CURDATE()} AS "CurDate" FROM Company Returns:
CURRENT_DATE () Instruction: Returns the current date as a date value. Example: SELECT {fn CURDATE_DATE()} AS "CurrentDate" FROM Company Returns:
CURTIME() Instruction: Returns the local time as a time value. Example: SELECT {fn CURTIME()} AS "CurTime" FROM Company Returns:
CURRENT_ TIME() Instruction: Returns the current time as a time value. Example : SELECT {fn CURRENT_TIME()} AS "CurrentTime" FROM Company Returns:
CURRENT_ TIMESTAMP() Instruction: Returns the current date and time as a timestamp value. Example: SELECT {fn CURRENT_TIMESTAMP()} AS "CurrentTimeStamp" FROM Company Returns:
DAYNAME ( date_exp ) Instruction: Returns a character string containing the data source-specific name of the day (for example, Sunday through Saturday or Sun. through Sat. for a data source that uses English) for the day portion of date_exp . Example: SELECT {fn DAYNAME({fn CURDATE()})} AS "CurDayName", CompanyName AS "CompanyName" FROM Company Returns:
DAYOFMONTH ( date_exp| string_exp|timestamp_exp ) Instruction: Returns the day of the month in date_exp as an integer value in the range of 1-31. Example: SELECT {fn DAYOFMONTH({fn CURDATE()})} AS "CurDayOfMonth" FROM Company Returns:
DAYOFWEEK ( date_exp ) Instruction: Returns the day to the week in date_exp as an integer value in the range of 1-7, where one represents Sunday. Example: SELECT {fn DAYOFWEEK({fn CURDATE()})} AS "CurDayOfWeek" FROM Company Returns:
DAYOFYEAR ( date_exp ) Instruction: Returns the day of the year in date_exp as an integer value in the range of 1-366. Example: SELECT {fn DAYOFYEAR({fn CURDATE()})} AS "CurDayOfYear" FROM Company Returns:
EXTRACT( date_exp|timestamp_exp ) Instruction: Returns the part of date_exp or timestamp_exp . Example: SELECT {fn EXTRACT(YEAR FROM{fn CURRENT_DATE()})} AS "Year",{fn EXTRACT(MONTH FROM{fn CURRENT_DATE()})} AS "Month",{fn EXTRACT(DAY FROM{fn CURRENT_DATE()})} AS "Day",CompanyName as "CompanyName" FROM Company Returns:
HOUR ( time_exp| string_exp|timestamp_exp ) Instruction: Returns the hour in time_exp or char_exp or timestamp_exp as an integer value in the range of 0-23. Example: SELECT {fn HOUR({fn CURTIME()})} AS "CurHour" FROM Company Returns:
MINUTE ( time_exp| string_exp|timestamp_exp ) Instruction: Returns the minute in time_exp as an integer value in the range of 0-59. Example: SELECT {fn MINUTE({fn CURTIME()})} AS "CurMinute" FROM Company Returns:
MONTH ( date_exp| string_exp|timestamp_exp ) Instruction: Returns the month in date_exp as an integer value in the range of 1-12. Example: SELECT {fn MONTH({fn CURDATE()})} AS "CurMonth" FROM Company Returns:
MONTHNAME ( date_exp| string_exp|timestamp_exp ) Instruction: Returns a character string containing the data source-specific name of the month (for example, January through December or Jan. through Dec. for a data source that uses English) for the month portion of date_exp . Example: SELECT {fn MONTHNAME({fn CURDATE()})} AS "CurMonthName” , CompanyName AS "CompanyName" FROM Company Returns:
NOW () Instruction: Returns the current date and time as a timestamp value. Example: SELECT {fn NOW()} AS "Now" FROM Company Returns:
QUARTER ( date_exp| string_exp|timestamp_exp ) Instruction: Returns the quarter in the date_exp as an integer value in the range of 1-4, where 1 represents January 1 through March 31. Example: SELECT {fn QUARTER({fn CURDATE()})} AS "CurQuarter", CompanyName AS "CompanyName" FROM Company Returns:
SECOND ( time_exp| string_exp|timestamp_exp ) Instruction: Returns the second in time_exp as an integer value in the range of 0-59. Example: SELECT {fn SECOND({fn CURTIME()})} AS "CurSecond" FROM Company Returns:
TIMESTAMPADD ( interval, integer_exp, timestamp_exp ) Instruction: Returns the timestamp calculated by adding integer_exp intervals of type interval to timestamp_exp . Valid values of interval are the following keywords: SQL_TSI_FRAC_SECOND, SQL_TSI_HOUR, SQL_TSI_SECOND, SQL_TSI_DAY, SQL_TSI_MINUTE, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR where fractional seconds are expressed in billionths of a second. Notes: If timestamp_exp is a time value and interval specifies days, weeks, months, quarters, or years, the date portion of timestamp_exp is set to the current date before calculating the resulting timestamp. If timestamp_exp is a date value and interval specifies fractional seconds, seconds, minutes, or hours, the time portion of timestamp_exp is set to 0 before calculating the resulting timestamp. Example: SELECT Name, {fn TIMESTAMPADD(SQL_TSI_YEAR, 1, HiredDate)} AS "Anniversary" FROM Employee Returns:
TIMESTAMPDIFF ( interval, timestamp_exp1, timestamp_exp2 ) Instruction: Returns the integer number of intervals of type interval by which timestamp_exp2 is greater than timestamp_exp1 . Valid values of interval are the following keywords: SQL_TSI_FRAC_SECOND, SQL_TSI_HOUR, SQL_TSI_SECOND, SQL_TSI_DAY, SQL_TSI_MINUTE, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR where fractional seconds are expressed in billionths of a second. Note: If either timestamp expression is a time value and interval specifies days, weeks, months, quarters, or years, the date portion of that timestamp is set to the current date before calculating the difference between the timestamps. If either timestamp expression is a date value and interval specifies fractional seconds, seconds, minutes, or hours, the time portion of that timestamp is set to 0 before calculating the difference between the timestamps. Example: SELECT Name, {fn TIMESTAMPDIFF(SQL_TSI_YEAR, {fn CURDATE()}, HiredDate)} AS "YearsWorked” FROM Employee Returns:
WEEK ( date_exp ) Instruction: Returns the week of the year in date_exp as an integer value in the range of 1-53. Example: SELECT {fn WEEK({fn CURDATE()})} AS "CurWeek" FROM Company Returns:
YEAR ( date_exp|string_exp|timestamp_exp ) Instruction: Returns the year in date_exp as an integer value. Example: SELECT {fn YEAR({fn CURDATE()})} AS "CurYear" FROM Company Returns:
QODBC System FunctionsDATABASE () Instruction: Returns the name of the database in use at the time this function is called. Example: SELECT {fn DATABASE()} AS "OpenDatabase" FROM Company Returns:
IFNULL ( exp, value ) Instruction: If exp is null, the value is returned. If exp is not null, exp is replaced. The possible data type(s) of value must be compatible with the data type of exp. Example: Select Name, {fn IFNULL(Fax, 'Missing Fax')} as "FixedFax" from Employee Returns:
PARENT( string_exp ) Instruction: Returns the characters before the colon it finds in string_exp Example : SELECT {fn PARENT(FullName)} AS “ParentValue”,FullName FROM Account CALLDIRECT Returns:
CHILD ( string_exp ) Instruction: Returns the characters after the colon it finds in string_exp Example: SELECT {fn CHILD(FullName)} AS “ChildValue”,FullName FROM Account CALLDIRECT Returns:
CONVERT (integer_exp, sqltype_exp) Instruction: Returns the slqtype value, which is converted from an integer value and can be used in numeric functions. Example: SELECT {fn CONVERT(21,SQL_FLOAT)} AS "ConverValue", CompanyName AS "CompanyName" FROM Company Returns:
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 (1 vote(s)) Helpful Not helpful |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||