pts script syntax loopsql
full_sql_select_statement should be a complete SQL SELECT statement to gather the records that you require; it will be passed almost verbatim to the database drivers. Like the data for the TEXT command it can contain embedded variables, which will be translated before passing through.
Unlike standard WHERE clauses it cannot contain CURRENT."field_name". If you need this then you should use VARIABLE_FIELD to assign the field to a variable, and embed the variable instead.
Since SQL requires single quotes around strings, if you are embedding variables in the string, they must still be surrounded in the single quotes – this is not done automatically. This allows a numeric variable to be entered and still form legal SQL.
Since a SELECT statement can contain table joins, this can be used to simplify and speed up scripts by combining what would originally have been nested loops into a single action. However, unlike in most implementations of SQL, if two tables have a field of the same name, these cannot be accessed separately within your script. This is because fields are only accessed by the field name, with no table to qualify it. To overcome this you should us SQL’s AS keyword to create an alias for the field name. This will then provide a unique name that can be used to extract the field.
NODATERANGE and SORTBY / REVERSE_SORTBY are not supported when using raw SQL syntax. Doing so will confuse the parser and will most likely result in failure. ROW_COUNT is still supported as normal.
Examples:
(Statement split over multiple physical lines for clarity only)
LOOP SQL “SELECT * FROM [Away Talks]
WHERE [Brother]
LIKE ‘$strThisBrother’
ORDER BY [Talk Date];” ROW_COUNT $iNumRows
...
END_LOOP
LOOP SQL "SELECT [Congregation Speakers].Speaker,
[Congregation Speakers].Notes,
Congregations.Notes AS [CongNotes]
FROM [Congregation Speakers]
INNER JOIN Congregations
ON [Congregation Speakers].Congregation = Congregations.Congregations"
ORDER BY [Congregation Speakers].Speaker"
ROW_COUNT $iNumRows
FIELD "Notes" (the speaker notes)
EOL
FIELD "CongNotes" (was the congregation Notes field, now aliased)
EOL
END_LOOP