SQLFIRST returns a value of the SQL Query result into a Spread Report. If the result of the SQL Query is a table of more than 1 column by 1 row, SQLRESULT must be used to retrieve other values.
SYNTAX:
SQLFIRST(Col, Row, "Facility","SQL Query", MaxColumns, MaxRows, Parameter1,…,Parameter20)
Col: Column number to retrieve result from
Row: Row number to retrieve result from
"Facility": Facility to query against, specified by its unique identifier. If left blank(i.e. ""), current facility is used.
"SQL Query": SQL select statement to execute
MaxColumns: Specifies the number of columns in the resulting table
MaxRows: Specifies the number of rows in the resulting table
Parameter1..20: OPTIONAL. External values to be used substituted into the query, should the query refer to them. In SQL Query, use @Px@ to refer to the parameter, or #Px# to refer to the parameter as a date, $Px$ as a number.
NOTES:
Use Locate, SQL Results to build and locate SQLFIRST and SQLRESULT functions.
Be aware that SQLFIRST stores each query result table within memory. Use caution when specifying large MaxColumn and MaxRow sizes. Also, restrain from specifying queries that can potentially overwhelm your PC memory.
If you specify 0 for Col and Row parameters, SQLFIRST will perform the query, but it will not return anything. The result table of the query will be accessible by SQLRESULT.
See Use Long SQL Statements in SQLFIRST for building long SQL Statements.
EXAMPLES:
SQLFIRST(1,1,””,"SELECT VarNum,Name, Units,Location from VarDesc where Location ='@P1@',4,30,A2) returns the result located at col 1 and row 1 of the 4 by 30 table of variables for the location entered in Cell A2.
SQLFIRST(1,1,”OPSSANDY”,"SELECT AVG(CurValue) from DataTbl where VarId = @P1@ and DateStamp >= #P2# and DateStamp <= #P3# ",1,1,A1,"1/1/2004",A2) returns the average for the Varid in A1 for facility OPSSANDY for the dates 1/1/2004 through the date in cell A2.
SQLFIRST(1,1,"OPSROCKY", SELECT name from LC_People where ID=$P1$,1,1,A2) Returns the Name from the LC_People Table for the ID number in cell A2. The query is set to return one result (1 column, 1 Row)