Valentina SQL FAQ
Q: How can I sort on column with aggregate function?
You should specify in the ORDER BY statement the numeric index of that column in the SELECT statement.
SELECT a, COUNT(a) FROM T GROUP BY a ORDER BY 2
Q: How can I get the RecID value of a newly added row if I use VCursor.AddRecord() command?
You should explicitly SELECT RecID field and after AddRecord() you can use its value. It contains RecID of just added record.
Q: What is the difference between Table Methods and Stored Procedures/Functions?
Well, Table Methods, Stored Procedures and User Defined Functions (UDFs) are quite different things:
Methods was in Valentina from the first year of its life, Procedures we made only on 9th year in Feb 2007, UDFs in June 2007.
Table Methods are not common feature for RDBMS, while Procedures and UDFs are.
- Table Method is a SIMPLE EXPRESSION (yes based on SQL rules) that calculates SCALAR value, using VALUES OF the CURRENT record of the Table.
- Procedures can contain practically any SQL statement: create new tables, drop them do SELECTs, INSERTs UPDATEs.
- Table Methods can be used by name, at any place where FIELD can be used:SELECT fld, Mtd FROM T1 WHERE fld = 1 AND Mtd = 2 ORDER BY fld, Mtd
- Procedures can be called using CALL method of SQL.
- UDFs can be used at any place where built-in functions can be used inside some expressions. UDFs always has syntax as FOO().
5) Name Scope
- Table Methods share the same namespace with Table Fields in the table scope.
- Stored Procedures have their own namespace in the database scope.
- User Defined Functions share namespace with built-in functions of Valentina engine (e.g. sin(), concat(), … )
- Table Methods can be indexed as well as regular fields.
- Stored Procedures cannot be indexed.
- UDFs can be part of Table Methods expression.