SelectLast

Structure

SelectLast(sSQL): String:

Type

Function

Description

This function modifies sql text so that it will only return the last record in the result set. This sql syntax varies between MSSQL, FIREBIRD, and ORACLE - so it is helpful to use this function rather than hardcoding for one database type. This way, if the configuration is synced to a different database type the sql will still function properly. As an example, if you had this function:

SelectLast("SELECT GAGE_SN FROM GAGES WHERE COMPANY = 'ABC COMPANY' ORDER BY COMPANY, GAGE_SN")

Then a FIREBIRD database client would return this text:

SELECT FIRST 1 GAGE_SN FROM GAGES WHERE COMPANY = 'ABC COMPANY' ORDER BY COMPANY, GAGE_SN DESC

A MSSQL database client would return this text:

SELECT TOP 1 GAGE_SN FROM GAGES WHERE COMPANY = 'ABC COMPANY' ORDER BY COMPANY, GAGE_SN DESC

An ORACLE database client would return this text:

SELECT GAGE_SN FROM (SELECT GAGE_SN FROM GAGES WHERE COMPANY = 'ABC COMPANY' ORDER BY COMPANY, GAGE_SN DESC) WHERE ROWNUM = 1

IMPORTANT: For this function to work the SQL provided MUST begin with the text 'SELECT ' and include an 'ORDER BY' clause - otherwise the return text will simply be a copy of the original sql text.

Parameters: 1

sSQL = the original sql

Return Value

String

Example

if tdDoSQLRecords(1, SelectLast("SELECT GAGE_SN FROM GAGES WHERE COMPANY = 'ABC COMPANY' ORDER BY COMPANY, GAGE_SN")) then

ShowMessage("Last Record = " & tdFieldByNameAsString(1, "GAGE_SN"))

End If |