Knowledgebase
[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 List

Applicable to [QODBC-Desktop], [QODBC-Online], [QODBC-POS], [QXL- Desktop] & [QXL- Online] 

 

Stored Procedures

SP_COLUMNS table name

Instruction: Returns a recordset of the columns in the specified table.

Example: sp_columns Customer

Returns:

QUALIFIERNAME OWNER NAME TABLE NAME COLUMNNAME TYPE TYPE NAME PRECISION LENGTH SCALE RADIX NULLABLE REMARKS DEFAULT ...
QODBC   Customer ListID 12 VARCHAR 36 36 0   0   NULL ...
QODBC   Customer TimeCreated 11 TIMESTAMP 23 10 0   1   NULL ...
... ... ... ... ... ... ... ...

 

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:

LastInsertId ErrorMessage
F3B0-1355557956  

 

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:

QUALIFIERNAME OWNER NAME TABLE NAME COLUMNNAME KEYSEQ PKNAME
QODBC   Customer ListID 1 Customer_PrimaryKey

 

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:

QBFileName
C:\Documents and Settings\All Users\Documents\Intuit\QuickBooks\Test Company Files\Medal Crest Homes, LLC.QBW

 

*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:

Current_Title Amount_1_Title Amount_2_Title Amount_3_Title Amount_4_Title Amount_5_Title Label Current Amount_Count Amount_1 Amount_2 Amount_3 Amount_4 Amount_5
Current 1 – 30 31 – 60 61 – 90 >90 TOTAL 1456 Red Cloud Peak - Barbour 0.00 5 0.00 0.00 0.00 25671.35 25671.35
Current 1 – 30 31 – 60 61 – 90 >90 TOTAL 1463 Red Cloud Peak - MCH 0.00 5 0.00 0.00 0.00 29353.00 29353.00
... ... ... ... ... ... ... ... ... ... ... ... ... ...

 

SP_REPORTS

Instruction: Returns a recordset of the information of all reports.

Example: sp_reports  

Returns:

QUALIFIERNAME OWNER NAME TABLE NAME TYPE NAME REMARKS
QODBC   APAgingDetail SP_REPORT Vendors & Payables->A/P Aging Detail
QODBC   APAgingSummary SP_REPORT Vendors & Payables->A/P Aging Summary
QODBC   ARAgingDetail SP_REPORT Customers & Receivables->A/R Aging Detail

 

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:

SCOPE COLUMNNAME TYPE TYPE NAME PRECISION LENGTH SCALE PSEUDO
2 ListID 12 VARCHAR 36 36 0 0

Example2: sp_specialcolumns Customer RowVer  

Returns:  

SCOPE COLUMNNAME TYPE TYPE NAME PRECISION LENGTH SCALE PSEUDO
2 EditSequence 12 VARCHAR 16 16 0 0

 

SP_TABLES  table name

Instruction: Returns a recordset with the list of tables available from the ODBC Driver.

Example: sp_tables

Returns:

QUALIFIERNAME OWNER NAME TABLE NAME TYPE NAME REMARKS DELETEABLE VOIDABLE INSERT_ONLY
QODBC   Account TABLE Chart of 1 0 0
QODBC   AccountTaxLineInfo TABLE Account Tax 0 0 0
   

 

SP_CATEGORIES

Instruction: Returns a recordset with the list of the categories for all the tables.

Example: sp_categories

Returns:

CATEGORY NAME
Accounting & Taxes
Banking
Budgets
Company & Financial
Customers & Receivables
Employees & Payroll
Inventory
….

 

SP_CATEGORYTABLES

Instruction: Returns a recordset with the list of all the tables and the categories they belong to.

Example: sp_categorytables

Returns:

CATEGORY NAME TABLE NAME
Accounting & Taxes Account
Accounting & Taxes AccountTaxLineInfo
Accounting & Taxes Class
Accounting & Taxes journal entry
Accounting & Taxes JournalEntryCreditLine
Accounting & Taxes JournalEntryDebitLine
Accounting & Taxes JournalEntryLine
Accounting & Taxes SpecialAccount
Banking ReceivePaymentToDeposit
Banking SalesTaxPaymentCheck

 

SP_CATEGORYREPORTS

Instruction: Returns a recordset with the list of all the reports and the categories they belong to.

Example: sp_categoryreports

Returns:

CATEGORY NAME REPORT NAME
Accounting & Taxes AuditTrail
Accounting & Taxes GeneralLedger
Accounting & Taxes IncomeTaxDetail
Accounting & Taxes Journal
Accounting & Taxes TxnDetailByAccount
Accounting & Taxes TxnListByDate
Accounting & Taxes IncomeTaxSummary
Accounting & Taxes trial balance
Banking check details
Banking DepositDetail

 

SP_FOREIGNKEYS  table name table name

Instruction: Returns a recordset of the detailed relationship information of two tables.

Example: sp_foreignkeys Customer Invoice

Returns:

PKQUALIFIERNAME PKOWNERNAME PKTABLENAME PKCOLUMNNAME FKQUALIFIERNAME FKOWNERNAME FKTABLENAME FKCOLUMNNAME KEYSEQ UPDATE RULE DELETE RULE FKNAME PKNAME SEVERABILITY
QODBC   customer ListID QODBC   Invoice CustomerRefListID 1 3 3 Invoice_Customer_Link Customer_PrimaryKey 7

 

SP_PARAMETERS

Instruction: Returns a recordset with the list of the detailed information of parameters of each report.

Example: sp_parameters

Returns:

QUALIFIERNAME OWNER NAME TABLE NAME PARAMETER NAME TYPE TYPE NAME PRECISION LENGTH DEFAULT DATATYPE DATETIME_SUBTYPE VALUES VALUE_TYPE LOOKUP_VALUE LOOKUP_DISPAY_FILED DEFAULT_VALUE REMARKS ADVANCED
QODBC   APAgingDetail DateFrom 9 DATE 10 6   9 1 {d'yyyy-mm-dd'} Date       DateFrom 0
QODBC   APAgingDetail DateTo 9 DATE 10 6   9 1 {d'yyyy-mm-dd'} Date       DateTo 0
QODBC   APAgingDetail DateMacro 12 VARCHAR 4096 4096   12   |All|Today|ThisWeek|This…. FixedList       DateMacro 0
….

 

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:

QUALIFIERNAME OWNER NAME TABLE NAME NONUNIQUE INDEXQUALIFIERNAME INDEXNAME TYPE SEQININDEX COLUMNNAME COLLATION CARDINALITY PAGES FILTER
QODBC   Customer 0   Customer_PrimaryKey 3 1 ListID A      
QODBC   Customer 1   Customer_TimeModified 3 1 TimeModified A      

 

Keywords: last insert id

Function List

Applicable to [QODBC-Desktop], [QODBC-Online], [QODBC-POS], [QXL- Desktop] & [QXL- Online] 

 

Functions

     This is a list of all of the SQL functions supported by the QODBC Driver and their associated syntax.

QODBC String Functions

ASCII ( 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:

ASCII Name
65 Adam's Candy Shop
65 Andres, Cristina
66 Balak, Mike
51 330 Main St
82 Residential
66 Blackwell, Edward
67 Chapman, Natalie
67 Cheknis, Benjamin
67 Corcoran, Carol

 

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:

APlusB Name
AB Adam's Candy Shop
AB Andres, Cristina
AB Balak, Mike
AB 330 Main St
AB Residential

 

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:

STZip Name
CA94555 Adam's Candy Shop
CA94326 Andres, Cristina
CA94326 Balak, Mike
CA94326 330 Main St
CA94326 Residential

 

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:

Difference Name
199949 Adam's Candy Shop
1102999 Andres, Cristina
9910829 Balak, Mike
1099949 330 Main St
169901831 Residential

 

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:

Inserted Name
Ad*Inserted*'s Candy Shop Adam's Candy Shop
An*Inserted*es, Cristina Andres, Cristina
Ba*Inserted*k, Mike Balak, Mike
33*Inserted* Main St 330 Main St
Re*Inserted*dential Residential

 

LCASE ( string_exp )

Instruction: Converts all uppercase characters in string_exp to lowercase.

Example: SELECT {fn LCASE("Name")} AS "LCase", "Name" FROM Customer

Returns:

LCase Name
adam's candy shop Adam's Candy Shop
Andres, Cristina Andres, Cristina
Balak, mike Balak, Mike
330 main st 330 Main St
residential Residential

 

UCASE ( string_exp )

Instruction: Converts all lowercase characters in string_exp to uppercase.

Example: SELECT {fn UCASE("Name")} AS "UCase", "Name" FROM Customer

Returns:

UCase Name
ADAM'S CANDY SHOP Adam's Candy
ANDRES, CRISTINA Andres, Cristina
BALAK, MIKE Balak, Mike
330 MAIN ST 330 Main St
RESIDENTIAL Residential

 

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:

Left5 Name
Adam' Adam's Candy Shop
Andre Andres, Cristina
Balak Balak, Mike
330 M 330 Main St
Resid Residential

 

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:

Right5 Name
Shop Adam's Candy Shop
Stina Andres, Cristina
Mike Balak, Mike
In St 330 Main St
ntial Residential

 

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:

Length Name
17 Adam's Candy Shop
16 Andres, Cristina
11 Balak, Mike
11 330 Main St
11 Residential

 

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:

BitLength Name
136 Adam's Candy Shop
128 Andres, Cristina
88 Balak, Mike
88 330 Main St
88 Residential

 

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:

CharLength Name
17 Adam's Candy Shop
16 Andres, Cristina
11 Balak, Mike
11 330 Main St
11 Residential

 

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:

CharacterLength Name
17 Adam's Candy Shop
16 Andres, Cristina
11 Balak, Mike
11 330 Main St
11 Residential

 

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:

OctetLength Name
17 Adam's Candy Shop
16 Andres, Cristina
11 Balak, Mike
11 330 Main St
11 Residential

 

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:

location of Name
3 Adam's Candy Shop
16 Andres, Cristina
2 Balak, Mike
6 330 Main St
10 Residential

 

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:

LTrim Name
Adam's Candy Adam's Candy Shop
Andres, Cristina Andres, Cristina
Balak, Mike Balak, Mike
330 Main St 330 Main St
Residential Residential

 

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:

trim Name
Adam's Candy Shop Adam's Candy Shop
Andres, Cristina Andres, Cristina
Balak, Mike Balak, Mike
330 Main St 330 Main St
Residential Residential

 

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:

Repeat2 Name
Adam's Candy Shop Adam's Candy Shop Adam's Candy Shop
Andres, Cristina Andres, Cristina Andres, Cristina
Balak, Mike Balak, Mike Balak, Mike
330 Main St330 Main St 330 Main St
Residential Residential Residential

 

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:

PositionOfA Name
1 Adam's Candy Shop
1 Andres, Cristina
2 Balak, Mike
0 330 Main St
0 Residential

 

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:

Replace Name
Adam's Candy Shop Adam's Candy Shop
Andres, Cristina Andres, Cristina
Balak, Mike Balak, Mike
abc 330 Main St
Residential Residential

 

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:

Soundex Name
ADAMACACAMDACAB Adam's Candy Shop
AMDRACACRACDAMA Andres, Cristina
BALACAMACA Balak, Mike
AMAMACD 330 Main St
RACADAMDAL Residential

 

SPACE ( count )

Instruction: Returns a character string consisting of count spaces.

Example: SELECT '[' + {fn SPACE(10)} + ']' AS "TenSpaces", "Name" FROM Customer

Returns:

TenSpaces Name
[ ] Adam's Candy Shop
[ ] Andres, Cristina
[ ] Balak, Mike
[ ] 330 Main St
[ ] Residential

 

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:

Middle5Characters Name
dam's Adam's Candy Shop
ndres Andres, Cristina
alak, Balak, Mike
30 Ma 330 Main St
eside Residential

 

 

QODBC Numeric Functions

ABS ( 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:

Name ABSBalance Balance
Adam's Candy 40.00 40.00
Andres, Cristina 0.00 0.00
Balak, Mike 0.00 0.00
330 Main St 180.00 180.00
Residential 0.00 0.00

 

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:

ACOSValue CompanyName
1.570796 Larry's

 

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:

ASINValue CompanyName
1.570796 Larry's

 

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:

ATANValue CompanyName
0.785398 Larry's

 

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:

ATAN2Value CompanyName
0.463648 Larry's

 

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:

COSValue CompanyName
0.540302 Larry's

 

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:

COTValue CompanyName
0.642093 Larry's

 

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:

Name CeilingBalance Balance
Adam's Candy 40.00 40.00
Andres, Cristina 0.00 0.00
Balak, Mike 0.00 0.00
330 Main St 180.00 180.00
Residential 0.00 0.00

 

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:

DegreesReturned CompanyName
57.29578 Larry's

 

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:

ExpReturned ExpReturned
2.718282 Larry's

 

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:

Name FloorBalance Balance
Adam's Candy 40.00 40.00
Andres, Cristina 0.00 0.00
Balak, Mike 0.00 0.00
330 Main St 180.00 180.00
1456 Red Cloud 25671.00 25671.35

 

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:

LogReturned CompanyName
3.218876 Larry's

 

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:

Log10Returned CompanyName
1.39794 Larry's

 

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:

Mod7Returned CompanyName
6 Larry's

 

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:

PI CompanyName
3.141593 Larry's

 

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:

PowerValue CompanyName
64 Larry's

 

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:

RadiansValue CompanyName
1 Larry's

 

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:

RandValue
4035516600699198138

 

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:

Name RoundBalance Balance
Adam's Candy Shop 40.00 40.00
Andres, Cristina .00 0.00
Balak, Mike .00 0.00
330 Main St 180.00 180.00
Residential .00 0.00

 

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:

Name SignOfBalance Balance
Adam's Candy 1 40.00
Andres, Cristina 1 0.00
Balak, Mike 1 0.00
330 Main St 1 180.00
Residential 1 0.00

 

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:

SINValue CompanyName
0.841471 Larry's

 

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:

SQRTValue CompanyName
6.855655 Larry's

 

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:

TANValue CompanyName
1.557408 Larry's

 

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:

Name TruncateBalance Balance
Adam's Candy 40.00 40.00
Andres, Cristina .00 0.00
Balak, Mike .00 0.00
330 Main St 180.00 180.00
Residential .00 0.00

 

 

QODBC Time and Date Functions

CURDATE ()

Instruction: Returns the current date as a date value.

Example: SELECT {fn CURDATE()} AS "CurDate" FROM Company

Returns:

CurDate
2009-07-08

 

CURRENT_DATE ()

Instruction: Returns the current date as a date value.

Example: SELECT {fn CURDATE_DATE()} AS "CurrentDate" FROM Company

Returns:

CurrentDate
2009-07-08

 

CURTIME()

Instruction: Returns the local time as a time value.

Example: SELECT {fn CURTIME()} AS "CurTime" FROM Company

Returns:

CurTime
20:30:31

 

CURRENT_ TIME()

Instruction: Returns the current time as a time value.

Example : SELECT {fn CURRENT_TIME()} AS "CurrentTime" FROM Company

Returns:

CurrentTime
20:31:03

 

CURRENT_ TIMESTAMP()

Instruction: Returns the current date and time as a timestamp value.

Example: SELECT {fn CURRENT_TIMESTAMP()} AS "CurrentTimeStamp" FROM Company

Returns:

CurrentTimeStamp
2009-07-08 23:10:10.316

 

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:

CurDayName CompanyName
Thursday Larry's

 

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:

CurDayOfMonth
08

 

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:

CurDayOfWeek
3

 

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:

CurDayOfYear
189

 

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:

Year Month Day CompanyName
2009 7 9 Larry's

 

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:

CurHour
22

 

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:

CurMinute
59

 

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:

CurMonth
07

 

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:

CurMonthName CompanyName
July Larry's

 

NOW ()

Instruction: Returns the current date and time as a timestamp value.

Example: SELECT {fn NOW()} AS "Now" FROM Company

Returns:

Now
2009-07-08 23:05:45.144

 

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:

CurQuarter CompanyName
3 Larry's

 

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:

CurSecond
09

 

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:

Name Anniversary
Duncan Fisher 2012-06-15 00:00:00.000
Jenny Miller 2012-11-01 00:00:00.000
Shane B. Hamby 2012-06-18 00:00:00.000

 

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:

Name YearsWorked
Duncan Fisher 2
Jenny Miller 2
Shane B. Hamby 2

  

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:

CurWeek
27

 

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:

CurYear
2009

 

 

QODBC System Functions

DATABASE ()

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:

OpenDatabase CompanyName
C:\Documents and Settings\All Users\Documents\Intuit\QuickBooks\Sample Company Files\QuickBooks Enterprise Solutions 9.0\sample_service-based business.qbw Larry's

  

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:

Name FixedFax
Duncan Missing Fax
Jenny Miller Missing Fax
Shane B. Hamby Missing Fax

  

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:

ParentValue FullName
Truck Truck:Accumulated Depreciation
QuickBooks Credit Card QuickBooks Credit Card
QuickBooks Credit Card QuickBooks Credit Card:QBCC Field Office

 

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:

ChildValue FullName
Accumulated Depreciation Truck:Accumulated Depreciation
QuickBooks Credit Card QuickBooks Credit Card
QBCC Field Office QuickBooks Credit Card:QBCC Field Office

 

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:

ConverValue CompanyName
21 Larry's

 

 

(1 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).